Hi David, Thanks for this input.
it takes the query from 1 minute+ (== timeout in fcgid) to subseond. Big leap forward! Thanks! The two queries you posted are equally fast for me, ~ 8 ms, but render different result, 15 vs 16 rows. :-( Palle > 3 feb. 2016 kl. 13:39 skrev David Gwynne <da...@gwynne.id.au>: > > On Thu, Jan 07, 2016 at 01:57:46PM +0100, Palle Girgensohn wrote: >> 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? > > ola, > > we hit this today while working on updating our installation. another > guy figured out that reverting > https://github.com/bestpractical/rt/commit/e48b94252c0bb4ab55587515cf695c0300b72d03 > brings the performance back in line with what we experience with > our currently 4.0 install. > > it takes the query from ~5500ms down to ~110ms > > however, while he was figuring that out, i was tinkering with the > query in psql with the intention of making it fast and then tricking > RT into generating the query. the query i ended up with runs in > about 8ms. > > the current (slow) query looks like that for us: > > SELECT > DISTINCT main.* > FROM > Users main > CROSS JOIN ACL ACL_3 > JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) > JOIN CachedGroupMembers CachedGroupMembers_2 ON ( > CachedGroupMembers_2.MemberId = Principals_1.id ) > JOIN CachedGroupMembers CachedGroupMembers_4 ON ( > CachedGroupMembers_4.MemberId = Principals_1.id ) > WHERE > ( > (ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR > (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1) > ) AND > (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND > (ACL_3.PrincipalType = 'Group') AND > (ACL_3.RightName = 'OwnTicket') AND > (CachedGroupMembers_2.Disabled = '0') AND > (CachedGroupMembers_2.GroupId = '4') AND > (CachedGroupMembers_4.Disabled = '0') AND > (Principals_1.Disabled = '0') AND > (Principals_1.PrincipalType = 'User') AND > (Principals_1.id != '1') > ORDER BY > main.Name ASC > ; > > after reverting the LimitToPrivileged out it generates: > > SELECT > DISTINCT main.* > FROM > Users main > CROSS JOIN ACL ACL_2 > JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) > JOIN CachedGroupMembers CachedGroupMembers_3 ON ( > CachedGroupMembers_3.MemberId = Principals_1.id ) > WHERE > ( > (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 3) OR > (ACL_2.ObjectType = 'RT::System' AND ACL_2.ObjectId = 1) > ) AND > (ACL_2.PrincipalId = CachedGroupMembers_3.GroupId) AND > (ACL_2.PrincipalType = 'Group') AND > (ACL_2.RightName = 'OwnTicket') AND > (CachedGroupMembers_3.Disabled = '0') AND > (Principals_1.Disabled = '0') AND > (Principals_1.PrincipalType = 'User') AND > (Principals_1.id != '1') > ORDER BY > main.Name ASC > ; > > this is the query i came up with: > > SELECT > DISTINCT main.* > FROM > ACL ACL_3 > LEFT JOIN Principals ON (ACL_3.principalid = Principals.id) > LEFT JOIN cachedgroupmembers ON (Principals.id = > cachedgroupmembers.groupid) > LEFT JOIN users main ON (cachedgroupmembers.memberid = main.id) > JOIN cachedgroupmembers cachedgroupmembers_2 ON > (cachedgroupmembers_2.memberid=main.id) > WHERE > ( > (ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR > (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1) > ) AND > (ACL_3.PrincipalType = 'Group') AND > (ACL_3.RightName = 'OwnTicket') AND > (Principals.disabled = '0') AND > (cachedgroupmembers.disabled = '0') AND > (cachedgroupmembers_2.groupid = 4) AND > (cachedgroupmembers_2.disabled = '0') AND > (main.id != 1) > ; > > cheers, > dlg
signature.asc
Description: Message signed with OpenPGP using GPGMail
--------- RT 4.4 and RTIR Training Sessions (http://bestpractical.com/services/training.html) * Hamburg Germany March 14 & 15, 2016