[rt-users] slow join with cachedgroupmembers for a simple "comment" click
Palle Girgensohn
girgen at pingpong.net
Thu Jan 7 07:57:46 EST 2016
Hi,
For our RT database, just clicking "comment" takes five seconds. In general, RT is very slow for us, and I believe that after 10+ years of use, we have bloat in the database. 500k+ entries in CachedGroupMembers, for example. All of them but a handful are enabled (disabled = 0).
So when I click comment in a ticket, I wait for this query five seconds. Seems to me it produces a list of users allowed to comment on this.
The results can be very different for different queus.
We'd like to keep the history, so shredding old tickets is not the first choice for us.
rt=# explain ANALYZE
rt-# SELECT DISTINCT main.id,
rt-# main.name
rt-# FROM Users main
rt-# CROSS JOIN ACL ACL_3
rt-# JOIN Principals Principals_1 ON (Principals_1.id = main.id)
rt-# JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.MemberId = Principals_1.id)
rt-# JOIN CachedGroupMembers CachedGroupMembers_4 ON (CachedGroupMembers_4.MemberId = Principals_1.id)
rt-# WHERE ((ACL_3.ObjectType = 'RT::Ticket'
rt(# AND ACL_3.ObjectId = 75164)
rt(# OR (ACL_3.ObjectType = 'RT::Queue'
rt(# AND ACL_3.ObjectId = 21)
rt(# OR (ACL_3.ObjectType = 'RT::System'
rt(# AND ACL_3.ObjectId = 1))
rt-# AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId)
rt-# AND (ACL_3.PrincipalType = 'Group')
rt-# AND (ACL_3.RightName = 'OwnTicket')
rt-# AND (CachedGroupMembers_2.Disabled = '0')
rt-# AND (CachedGroupMembers_2.GroupId = '4')
rt-# AND (CachedGroupMembers_4.Disabled = '0')
rt-# AND (Principals_1.Disabled = '0')
rt-# AND (Principals_1.PrincipalType = 'User')
rt-# AND (Principals_1.id != '1')
rt-# ORDER BY main.Name ASC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=554.36..554.37 rows=1 width=29) (actual time=5927.879..5927.937 rows=72 loops=1)
-> Sort (cost=554.36..554.37 rows=1 width=29) (actual time=5927.877..5927.893 rows=149 loops=1)
Sort Key: main.name, main.id
Sort Method: quicksort Memory: 32kB
-> Nested Loop (cost=1.84..554.35 rows=1 width=29) (actual time=5.926..5927.400 rows=149 loops=1)
-> Nested Loop (cost=1.56..550.64 rows=2 width=33) (actual time=0.152..78.279 rows=129788 loops=1)
-> Nested Loop (cost=1.13..548.76 rows=1 width=37) (actual time=0.131..7.133 rows=134 loops=1)
-> Nested Loop (cost=0.71..493.88 rows=36 width=33) (actual time=0.115..4.984 rows=136 loops=1)
-> Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_2 (cost=0.42..5.94 rows=76 width=4) (actual time=0.079..0.152 rows=137 loops=1)
Index Cond: ((groupid = 4) AND (disabled = 0::smallint))
Heap Fetches: 0
-> Index Scan using users_pkey on users main (cost=0.29..6.41 rows=1 width=29) (actual time=0.033..0.034 rows=1 loops=137)
Index Cond: (id = cachedgroupmembers_2.memberid)
-> Index Scan using principals_pkey on principals principals_1 (cost=0.42..1.51 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=136)
Index Cond: (id = main.id)
Filter: ((id <> 1) AND (disabled = 0::smallint) AND (principaltype = 'User'::text))
Rows Removed by Filter: 0
-> Index Only Scan using cachedgroupmembers2 on cachedgroupmembers cachedgroupmembers_4 (cost=0.42..1.67 rows=21 width=8) (actual time=0.011..0.290 rows=969 loops=134)
Index Cond: ((memberid = principals_1.id) AND (disabled = 0::smallint))
Heap Fetches: 0
-> Index Only Scan using acl1 on acl acl_3 (cost=0.28..1.85 rows=1 width=4) (actual time=0.045..0.045 rows=0 loops=129788)
Index Cond: ((rightname = 'OwnTicket'::text) AND (principaltype = 'Group'::text) AND (principalid = cachedgroupmembers_4.groupid))
Filter: (((objecttype = 'RT::Ticket'::text) AND (objectid = 75164)) OR ((objecttype = 'RT::Queue'::text) AND (objectid = 21)) OR ((objecttype = 'RT::System'::text) AND (objectid = 1)))
Rows Removed by Filter: 0
Heap Fetches: 0
Planning time: 6.461 ms
Execution time: 5928.204 ms
(27 rows)
If I remove the join on CachedGroupMembers_2 (the one that joins on memberid = principals.id where groupid = 4), it is lightning fast.
rt=# explain ANALYZE
rt-# SELECT DISTINCT main.id,
rt-# main.name
rt-# FROM Users main
rt-# CROSS JOIN ACL ACL_3
rt-# JOIN Principals Principals_1 ON (Principals_1.id = main.id)
rt-# --JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.MemberId = Principals_1.id)
rt-# JOIN CachedGroupMembers CachedGroupMembers_4 ON (CachedGroupMembers_4.MemberId = Principals_1.id)
rt-# WHERE ((ACL_3.ObjectType = 'RT::Ticket'
rt(# AND ACL_3.ObjectId = 75164)
rt(# OR (ACL_3.ObjectType = 'RT::Queue'
rt(# AND ACL_3.ObjectId = 21)
rt(# OR (ACL_3.ObjectType = 'RT::System'
rt(# AND ACL_3.ObjectId = 1))
rt-# AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId)
rt-# AND (ACL_3.PrincipalType = 'Group')
rt-# AND (ACL_3.RightName = 'OwnTicket')
rt-# -- AND (CachedGroupMembers_2.Disabled = '0')
rt-# -- AND (CachedGroupMembers_2.GroupId = '4')
rt-# AND (CachedGroupMembers_4.Disabled = '0')
rt-# AND (Principals_1.Disabled = '0')
rt-# AND (Principals_1.PrincipalType = 'User')
rt-# AND (Principals_1.id != '1')
rt-# ORDER BY main.Name ASC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=1323.30..1323.33 rows=4 width=29) (actual time=20.321..20.395 rows=74 loops=1)
-> Sort (cost=1323.30..1323.31 rows=4 width=29) (actual time=20.320..20.340 rows=108 loops=1)
Sort Key: main.name, main.id
Sort Method: quicksort Memory: 30kB
-> Nested Loop (cost=614.87..1323.26 rows=4 width=29) (actual time=18.323..19.919 rows=108 loops=1)
Join Filter: (main.id = principals_1.id)
-> Hash Join (cost=614.44..724.20 rows=1232 width=33) (actual time=18.305..18.755 rows=124 loops=1)
Hash Cond: (cachedgroupmembers_4.memberid = main.id)
-> Nested Loop (cost=0.71..71.95 rows=2620 width=4) (actual time=0.168..0.456 rows=136 loops=1)
-> Index Only Scan using acl1 on acl acl_3 (cost=0.28..12.31 rows=13 width=4) (actual time=0.149..0.238 rows=12 loops=1)
Index Cond: ((rightname = 'OwnTicket'::text) AND (principaltype = 'Group'::text))
Filter: (((objecttype = 'RT::Ticket'::text) AND (objectid = 75164)) OR ((objecttype = 'RT::Queue'::text) AND (objectid = 21)) OR ((objecttype = 'RT::System'::text) AND (objectid = 1)))
Rows Removed by Filter: 108
Heap Fetches: 0
-> Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_4 (cost=0.42..4.54 rows=5 width=8) (actual time=0.009..0.013 rows=11 loops=12)
Index Cond: ((groupid = acl_3.principalid) AND (disabled = 0::smallint))
Heap Fetches: 0
-> Hash (cost=454.44..454.44 rows=12744 width=29) (actual time=18.118..18.118 rows=12819 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 771kB
-> Seq Scan on users main (cost=0.00..454.44 rows=12744 width=29) (actual time=0.009..9.680 rows=12819 loops=1)
-> Index Scan using principals_pkey on principals principals_1 (cost=0.42..0.47 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=124)
Index Cond: (id = cachedgroupmembers_4.memberid)
Filter: ((id <> 1) AND (disabled = 0::smallint) AND (principaltype = 'User'::text))
Rows Removed by Filter: 0
Planning time: 2.446 ms
Execution time: 20.726 ms
(26 rows)
Any ideas how to make RT quicker here? What is the purpose of this query anyway? I'm just getting the comments view?
Palle
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 495 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20160107/8daefbaa/attachment.sig>
More information about the rt-users
mailing list