session purge command is too slow with many sessions on PostgreSQL

[Jun Omae]

Timing of "session purge" command with ~63 000 sessions is 8 minutes over. See ​gdiscussion:trac-users:jeiA32fZVic.

Proposed patch:

trac/web/session.py

@@ -500 +500 @@
             db("""
                 DELETE FROM session_attribute
                 WHERE authenticated=0
-                      AND sid NOT IN (SELECT sid FROM session
-                                      WHERE authenticated=0)
+                      AND NOT EXISTS (SELECT * FROM session AS s
+                                      WHERE s.sid=session_attribute.sid
+                                      AND s.authenticated=0)
                 """)

Timing of deleting session_attribute records with 100,000 sessions on PostgreSQL:

Before	555742.893 ms over
After	161.881 ms

I've confirmed no changes with 100,000 sessions on SQLite and MySQL.

trac=> SELECT authenticated, COUNT(*) FROM session GROUP BY authenticated;
 authenticated | count
---------------+--------
             0 | 100000
             1 |      9
(2 rows)

trac=> SELECT authenticated, COUNT(*) FROM session_attribute GROUP BY
authenticated;
 authenticated | count
---------------+--------
             0 | 200000
             1 |     47
(2 rows)

trac=> \timing on
Timing is on.
trac=> BEGIN;
BEGIN
Time: 0.276 ms
trac=> DELETE FROM session_attribute
trac-> WHERE authenticated=0
trac->       AND sid NOT IN (SELECT sid FROM session
trac(>                       WHERE authenticated=0)
trac-> ;
^CCancel request sent
ERROR:  canceling statement due to user request
Time: 555742.893 ms
trac=>
trac=>
trac=> BEGIN;
BEGIN
Time: 0.288 ms
trac=> DELETE FROM session_attribute
trac-> WHERE authenticated=0
trac-> AND NOT EXISTS (SELECT * FROM session AS s
trac(>                 WHERE s.sid=session_attribute.sid
trac(>                 AND s.authenticated=0)
trac-> ;
DELETE 0
Time: 161.881 ms
trac=> ROLLBACK;
ROLLBACK
Time: 0.263 ms