[rt-users] slow join with cachedgroupmembers for a simple "comment" click

Palle Girgensohn girgen at pingpong.net
Wed Feb 3 09:08:11 EST 2016


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 <david at 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

-------------- 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/20160203/d34665fb/attachment.sig>


More information about the rt-users mailing list