<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
I'm not sure if your Owner dropdown is large, but this option might
also help in a similar way by turning the Owner filed into a
autocomplete so RT doesn't have to generate the full Owner dropdown
on each page load where that field is offered:<br>
<br>
<a class="moz-txt-link-freetext" href="https://bestpractical.com/docs/rt/4.2/RT_Config.html#AutocompleteOwners">https://bestpractical.com/docs/rt/4.2/RT_Config.html#AutocompleteOwners</a><br>
<br>
<div class="moz-cite-prefix">On 2/3/16 9:08 AM, Palle Girgensohn
wrote:<br>
</div>
<blockquote
cite="mid:D7203DE3-9BC0-4443-9D20-211AD476ED72@pingpong.net"
type="cite">
<pre wrap="">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
</pre>
<blockquote type="cite">
<pre wrap="">3 feb. 2016 kl. 13:39 skrev David Gwynne <a class="moz-txt-link-rfc2396E" href="mailto:david@gwynne.id.au"><david@gwynne.id.au></a>:
On Thu, Jan 07, 2016 at 01:57:46PM +0100, Palle Girgensohn wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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?
</pre>
</blockquote>
<pre wrap="">
ola,
we hit this today while working on updating our installation. another
guy figured out that reverting
<a class="moz-txt-link-freetext" href="https://github.com/bestpractical/rt/commit/e48b94252c0bb4ab55587515cf695c0300b72d03">https://github.com/bestpractical/rt/commit/e48b94252c0bb4ab55587515cf695c0300b72d03</a>
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
</pre>
</blockquote>
<pre wrap="">
</pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">
---------
RT 4.4 and RTIR Training Sessions (<a class="moz-txt-link-freetext" href="http://bestpractical.com/services/training.html">http://bestpractical.com/services/training.html</a>)
* Hamburg Germany — March 14 & 15, 2016</pre>
</blockquote>
<br>
</body>
</html>