<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body dir="auto"><div></div><div><br></div><div><br>4 feb. 2016 kl. 00:48 skrev David Gwynne <<a href="mailto:david@gwynne.id.au">david@gwynne.id.au</a>>:<br><br></div><blockquote type="cite"><div><span></span><br><blockquote type="cite"><span>On 4 Feb 2016, at 00:08, Palle Girgensohn <<a href="mailto:girgen@pingpong.net">girgen@pingpong.net</a>> wrote:</span><br></blockquote><blockquote type="cite"><span></span><br></blockquote><blockquote type="cite"><span>Hi David,</span><br></blockquote><blockquote type="cite"><span></span><br></blockquote><blockquote type="cite"><span>Thanks for this input.</span><br></blockquote><blockquote type="cite"><span></span><br></blockquote><blockquote type="cite"><span>it takes the query from 1 minute+ (== timeout in fcgid) to subseond.</span><br></blockquote><blockquote type="cite"><span></span><br></blockquote><blockquote type="cite"><span>Big leap forward!</span><br></blockquote><blockquote type="cite"><span></span><br></blockquote><blockquote type="cite"><span>Thanks!</span><br></blockquote><blockquote type="cite"><span></span><br></blockquote><blockquote type="cite"><span></span><br></blockquote><blockquote type="cite"><span>The two queries you posted are equally fast for me, ~ 8 ms, but render different result, 15 vs 16 rows. :-(</span><br></blockquote><span></span><br><span>yeah, after i sent it i recognised some issues with mine. i can fix it, but then id have to go back and make RT generate the query and that idea makes me sad.</span><br><span></span><br></div></blockquote><div><br></div>Mmm, the problem is really the ORM. It never really marries well with SQL. Sets vs objects. Sadly, I don't think it'll be worth the effort trying to force RT to generate the query. But my experience with this specific implementation is limited. <div><br></div><div><br><blockquote type="cite"><div><span>if there's interest i can fix my query, but only if it'll help someone else</span></div></blockquote><div><br></div>I'm happy with the first advice, reverting the patch. It made RT usable from not being that! :-)</div><div><br></div><div>Btw, already had <span style="font-weight: bold; background-color: rgba(255, 255, 255, 0);">AutocompleteOwnersForSearch</span><span style="font-weight: bold; background-color: rgba(255, 255, 255, 0);"> enabled. </span></div><div><span style="font-weight: bold; background-color: rgba(255, 255, 255, 0);"><br></span></div><div><span style="font-weight: bold; background-color: rgba(255, 255, 255, 0);">Palle</span></div><div><b><br></b></div><div><b><br></b><blockquote type="cite"><div><span></span><br><blockquote type="cite"><span></span><br></blockquote><blockquote type="cite"><span>Palle</span><br></blockquote><blockquote type="cite"><span></span><br></blockquote><blockquote type="cite"><span></span><br></blockquote><blockquote type="cite"><span></span><br></blockquote><blockquote type="cite"><blockquote type="cite"><span>3 feb. 2016 kl. 13:39 skrev David Gwynne <<a href="mailto:david@gwynne.id.au">david@gwynne.id.au</a>>:</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>On Thu, Jan 07, 2016 at 01:57:46PM +0100, Palle Girgensohn wrote:</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>Hi,</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>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).</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>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.</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>The results can be very different for different queus.</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>We'd like to keep the history, so shredding old tickets is not the first choice for us.</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt=# explain ANALYZE</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# SELECT DISTINCT main.id,</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# main.name</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# FROM Users main</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# CROSS JOIN ACL ACL_3</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# JOIN Principals Principals_1 ON (Principals_1.id = main.id)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.MemberId = Principals_1.id)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# JOIN CachedGroupMembers CachedGroupMembers_4 ON (CachedGroupMembers_4.MemberId = Principals_1.id)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# WHERE ((ACL_3.ObjectType = 'RT::Ticket'</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt(# AND ACL_3.ObjectId = 75164)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt(# OR (ACL_3.ObjectType = 'RT::Queue'</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt(# AND ACL_3.ObjectId = 21)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt(# OR (ACL_3.ObjectType = 'RT::System'</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt(# AND ACL_3.ObjectId = 1))</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (ACL_3.PrincipalType = 'Group')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (ACL_3.RightName = 'OwnTicket')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (CachedGroupMembers_2.Disabled = '0')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (CachedGroupMembers_2.GroupId = '4')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (CachedGroupMembers_4.Disabled = '0')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (Principals_1.Disabled = '0')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (Principals_1.PrincipalType = 'User')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (Principals_1.id != '1')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# ORDER BY main.Name ASC;</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> QUERY PLAN</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>Unique (cost=554.36..554.37 rows=1 width=29) (actual time=5927.879..5927.937 rows=72 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> Sort (cost=554.36..554.37 rows=1 width=29) (actual time=5927.877..5927.893 rows=149 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Sort Key: main.name, main.id</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Sort Method: quicksort Memory: 32kB</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> Nested Loop (cost=1.84..554.35 rows=1 width=29) (actual time=5.926..5927.400 rows=149 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> Nested Loop (cost=1.56..550.64 rows=2 width=33) (actual time=0.152..78.279 rows=129788 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> Nested Loop (cost=1.13..548.76 rows=1 width=37) (actual time=0.131..7.133 rows=134 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> Nested Loop (cost=0.71..493.88 rows=36 width=33) (actual time=0.115..4.984 rows=136 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> 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)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Index Cond: ((groupid = 4) AND (disabled = 0::smallint))</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Heap Fetches: 0</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> 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)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Index Cond: (id = cachedgroupmembers_2.memberid)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> 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)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Index Cond: (id = main.id)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Filter: ((id <> 1) AND (disabled = 0::smallint) AND (principaltype = 'User'::text))</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Rows Removed by Filter: 0</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> 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)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Index Cond: ((memberid = principals_1.id) AND (disabled = 0::smallint))</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Heap Fetches: 0</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> 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)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Index Cond: ((rightname = 'OwnTicket'::text) AND (principaltype = 'Group'::text) AND (principalid = cachedgroupmembers_4.groupid))</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Filter: (((objecttype = 'RT::Ticket'::text) AND (objectid = 75164)) OR ((objecttype = 'RT::Queue'::text) AND (objectid = 21)) OR ((objecttype = 'RT::System'::text) AND (objectid = 1)))</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Rows Removed by Filter: 0</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Heap Fetches: 0</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>Planning time: 6.461 ms</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>Execution time: 5928.204 ms</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>(27 rows)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>If I remove the join on CachedGroupMembers_2 (the one that joins on memberid = principals.id where groupid = 4), it is lightning fast.</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt=# explain ANALYZE</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# SELECT DISTINCT main.id,</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# main.name</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# FROM Users main</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# CROSS JOIN ACL ACL_3</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# JOIN Principals Principals_1 ON (Principals_1.id = main.id)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# --JOIN CachedGroupMembers CachedGroupMembers_2 ON (CachedGroupMembers_2.MemberId = Principals_1.id)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# JOIN CachedGroupMembers CachedGroupMembers_4 ON (CachedGroupMembers_4.MemberId = Principals_1.id)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# WHERE ((ACL_3.ObjectType = 'RT::Ticket'</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt(# AND ACL_3.ObjectId = 75164)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt(# OR (ACL_3.ObjectType = 'RT::Queue'</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt(# AND ACL_3.ObjectId = 21)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt(# OR (ACL_3.ObjectType = 'RT::System'</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt(# AND ACL_3.ObjectId = 1))</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (ACL_3.PrincipalType = 'Group')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (ACL_3.RightName = 'OwnTicket')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# -- AND (CachedGroupMembers_2.Disabled = '0')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# -- AND (CachedGroupMembers_2.GroupId = '4')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (CachedGroupMembers_4.Disabled = '0')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (Principals_1.Disabled = '0')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (Principals_1.PrincipalType = 'User')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# AND (Principals_1.id != '1')</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>rt-# ORDER BY main.Name ASC;</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> QUERY PLAN</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>Unique (cost=1323.30..1323.33 rows=4 width=29) (actual time=20.321..20.395 rows=74 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> Sort (cost=1323.30..1323.31 rows=4 width=29) (actual time=20.320..20.340 rows=108 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Sort Key: main.name, main.id</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Sort Method: quicksort Memory: 30kB</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> Nested Loop (cost=614.87..1323.26 rows=4 width=29) (actual time=18.323..19.919 rows=108 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Join Filter: (main.id = principals_1.id)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> Hash Join (cost=614.44..724.20 rows=1232 width=33) (actual time=18.305..18.755 rows=124 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Hash Cond: (cachedgroupmembers_4.memberid = main.id)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> Nested Loop (cost=0.71..71.95 rows=2620 width=4) (actual time=0.168..0.456 rows=136 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> 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)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Index Cond: ((rightname = 'OwnTicket'::text) AND (principaltype = 'Group'::text))</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Filter: (((objecttype = 'RT::Ticket'::text) AND (objectid = 75164)) OR ((objecttype = 'RT::Queue'::text) AND (objectid = 21)) OR ((objecttype = 'RT::System'::text) AND (objectid = 1)))</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Rows Removed by Filter: 108</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Heap Fetches: 0</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> 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)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Index Cond: ((groupid = acl_3.principalid) AND (disabled = 0::smallint))</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Heap Fetches: 0</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> Hash (cost=454.44..454.44 rows=12744 width=29) (actual time=18.118..18.118 rows=12819 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Buckets: 2048 Batches: 1 Memory Usage: 771kB</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> Seq Scan on users main (cost=0.00..454.44 rows=12744 width=29) (actual time=0.009..9.680 rows=12819 loops=1)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> -> 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)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Index Cond: (id = cachedgroupmembers_4.memberid)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Filter: ((id <> 1) AND (disabled = 0::smallint) AND (principaltype = 'User'::text))</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span> Rows Removed by Filter: 0</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>Planning time: 2.446 ms</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>Execution time: 20.726 ms</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>(26 rows)</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><blockquote type="cite"><span>Any ideas how to make RT quicker here? What is the purpose of this query anyway? I'm just getting the comments view?</span><br></blockquote></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>ola,</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>we hit this today while working on updating our installation. another</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>guy figured out that reverting</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span><a href="https://github.com/bestpractical/rt/commit/e48b94252c0bb4ab55587515cf695c0300b72d03">https://github.com/bestpractical/rt/commit/e48b94252c0bb4ab55587515cf695c0300b72d03</a></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>brings the performance back in line with what we experience with</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>our currently 4.0 install.</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>it takes the query from ~5500ms down to ~110ms</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>however, while he was figuring that out, i was tinkering with the</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>query in psql with the intention of making it fast and then tricking</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>RT into generating the query. the query i ended up with runs in</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>about 8ms.</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>the current (slow) query looks like that for us:</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>SELECT</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> DISTINCT main.*</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>FROM</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> Users main</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> CROSS JOIN ACL ACL_3</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> JOIN Principals Principals_1 ON ( Principals_1.id = main.id )</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id )</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.MemberId = Principals_1.id )</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>WHERE</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1)</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> ) AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_3.PrincipalType = 'Group') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_3.RightName = 'OwnTicket') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (CachedGroupMembers_2.Disabled = '0') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (CachedGroupMembers_2.GroupId = '4') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (CachedGroupMembers_4.Disabled = '0') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (Principals_1.Disabled = '0') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (Principals_1.PrincipalType = 'User') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (Principals_1.id != '1')</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>ORDER BY</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> main.Name ASC</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>;</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>after reverting the LimitToPrivileged out it generates:</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>SELECT</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> DISTINCT main.*</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>FROM</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> Users main</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> CROSS JOIN ACL ACL_2</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> JOIN Principals Principals_1 ON ( Principals_1.id = main.id )</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> JOIN CachedGroupMembers CachedGroupMembers_3 ON ( CachedGroupMembers_3.MemberId = Principals_1.id )</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>WHERE</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 3) OR</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_2.ObjectType = 'RT::System' AND ACL_2.ObjectId = 1)</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> ) AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_2.PrincipalId = CachedGroupMembers_3.GroupId) AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_2.PrincipalType = 'Group') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_2.RightName = 'OwnTicket') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (CachedGroupMembers_3.Disabled = '0') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (Principals_1.Disabled = '0') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (Principals_1.PrincipalType = 'User') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (Principals_1.id != '1')</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>ORDER BY</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> main.Name ASC</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>;</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>this is the query i came up with:</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>SELECT</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> DISTINCT main.*</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>FROM</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> ACL ACL_3</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> LEFT JOIN Principals ON (ACL_3.principalid = Principals.id)</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> LEFT JOIN cachedgroupmembers ON (Principals.id = cachedgroupmembers.groupid)</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> LEFT JOIN users main ON (cachedgroupmembers.memberid = main.id)</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> JOIN cachedgroupmembers cachedgroupmembers_2 ON (cachedgroupmembers_2.memberid=main.id)</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>WHERE</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1)</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> ) AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_3.PrincipalType = 'Group') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (ACL_3.RightName = 'OwnTicket') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (Principals.disabled = '0') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (cachedgroupmembers.disabled = '0') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (cachedgroupmembers_2.groupid = 4) AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (cachedgroupmembers_2.disabled = '0') AND</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span> (main.id != 1)</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>;</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span></span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>cheers,</span><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span>dlg</span><br></blockquote></blockquote><blockquote type="cite"><span></span><br></blockquote><span></span><br></div></blockquote></div></body></html>