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
signature.asc (495 Bytes)
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@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
Only display privileged users in Owner drop-downs · bestpractical/rt@e48b942 · GitHub
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 (495 Bytes)
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:
RT Config - RT 4.2.17 Documentation - Best Practical 2/3/16 9:08 AM, Palle Girgensohn wrote:
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@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
Only display privileged users in Owner drop-downs · bestpractical/rt@e48b942 · GitHub
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
RT 4.4 and RTIR Training Sessions (http://bestpractical.com/services/training.html)
- Hamburg Germany � March 14 & 15, 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.
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.
if there’s interest i can fix my query, but only if it’ll help someone else.
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.
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.
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.
if there’s interest i can fix my query, but only if it’ll help someone else
I’m happy with the first advice, reverting the patch. It made RT usable from not being that! 
Btw, already had AutocompleteOwnersForSearch enabled.
Palle
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.
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.
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.
if there’s interest i can fix my query, but only if it’ll help someone else
I’m happy with the first advice, reverting the patch. It made RT usable from not being that!
Btw, already had AutocompleteOwnersForSearch enabled.
and you still got hit with the slow query?
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.
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.
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.
if there’s interest i can fix my query, but only if it’ll help someone else
I’m happy with the first advice, reverting the patch. It made RT usable from not being that!
Btw, already had AutocompleteOwnersForSearch enabled.
and you still got hit with the slow query?
yes.
signature.asc (495 Bytes)