On Tue, Nov 7, 2017 at 7:41 PM, Nicolas MARTIN <[email protected]> wrote:
> Here you are
>
> $ psql -c "EXPLAIN DELETE FROM session WHERE authenticated=0 AND
> last_visit<1504216800;"
> QUERY PLAN
> ---------------------------------------------------------------------
> Delete on session (cost=0.00..931.79 rows=1506 width=6)
> -> Seq Scan on session (cost=0.00..931.79 rows=1506 width=6)
> Filter: ((last_visit < 1504216800) AND (authenticated = 0))
>
> $ psql -c "EXPLAIN DELETE FROM session_attribute WHERE authenticated=0 AND
> sid NOT IN (SELECT sid FROM session WHERE authenticated=0);"
> QUERY PLAN
>
-------------------------------------------------------------------------------
> Delete on session_attribute (cost=926.96..3379.66 rows=7889 width=6)
> -> Seq Scan on session_attribute (cost=926.96..3379.66 rows=7889
> width=6)
> Filter: ((NOT (hashed SubPlan 1)) AND (authenticated = 0))
> SubPlan 1
> -> Seq Scan on session (cost=0.00..901.83 rows=10052 width=22)
> Filter: (authenticated = 0)
>
> It's probably not as informative as it could be after my last purge of
> anonymous sessions.
Thanks for the reply.
It seems that "NOT IN (...)" in delete query for session_attribute leads the
issue.
I consider it could be solved by using "NOT EXISTS (...)", instead.
====
diff --git a/trac/web/session.py b/trac/web/session.py
index a8b68fae6..103c22c4a 100644
--- a/trac/web/session.py
+++ b/trac/web/session.py
@@ -500,6 +500,7 @@ class SessionAdmin(Component):
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)
""")
====
I tested with 100,000 sessions on PostgreSQL.
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
trac=> EXPLAIN
trac-> DELETE FROM session_attribute
trac-> WHERE authenticated=0
trac-> AND sid NOT IN (SELECT sid FROM session
trac(> WHERE authenticated=0)
trac-> ;
QUERY PLAN
-----------------------------------------------------------------------------------
Delete on session_attribute (cost=0.00..242228398.61 rows=75356 width=6)
-> Seq Scan on session_attribute (cost=0.00..242228398.61 rows=75356
width=6)
Filter: ((authenticated = 0) AND (NOT (SubPlan 1)))
SubPlan 1
-> Materialize (cost=0.00..3088.90 rows=50048 width=32)
-> Seq Scan on session (cost=0.00..2495.66 rows=50048
width=32)
Filter: (authenticated = 0)
(7 rows)
trac=> EXPLAIN
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-> ;
QUERY PLAN
--------------------------------------------------------------------------------------
Delete on session_attribute (cost=3512.26..14370.02 rows=53393 width=12)
-> Hash Anti Join (cost=3512.26..14370.02 rows=53393 width=12)
Hash Cond: (session_attribute.sid = s.sid)
-> Seq Scan on session_attribute (cost=0.00..6430.10 rows=150713
width=38)
Filter: (authenticated = 0)
-> Hash (cost=2495.66..2495.66 rows=50048 width=38)
-> Seq Scan on session s (cost=0.00..2495.66 rows=50048
width=38)
Filter: (authenticated = 0)
(8 rows)
--
Jun Omae <[email protected]> (大前 潤)
--
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/trac-users.
For more options, visit https://groups.google.com/d/optout.
