We’re trying to move RT 4.4.4 from Oracle to Postgres 11 on CentOS7 and in the process we noticed an unusual problem. Visiting the search builder under ‘rt4/Search/Build.html?NewQuery=1’ takes about 8 seconds, while it was instantaneous on Oracle.
The problem seems to be specific to the query populating ‘Owner’ box. Looking at archives identical issue was experienced by people using both postgres and mysql. ( Postgresql 4.4.1 slow queries? ). We have only ~1800 users, so I’m not sure why we’d experience this. We’ve tested it with PostgreSQL 9.6, 10, & 11, all of them showed the same poor performance. PostgreSQL 12 was very fast, but RT itself is then broken (perl DBD issues).
I was wondering if anybody experienced that, and whether you were able to solve it.
The query itself is:
EXPLAIN ANALYZE 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') 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' OR ACL_3.RightName = 'SuperUser') 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')
I’m not too familiar with PostgreSQL to debug the results of EXPLAIN ANALYZE of said query, but but here’s the full output:
Unique (cost=60.28..60.36 rows=1 width=375) (actual time=7929.281..7929.392 rows=26 loops=1) -> Sort (cost=60.28..60.28 rows=1 width=375) (actual time=7929.280..7929.287 rows=147 loops=1) Sort Key: main.id, main.name, main.password, main.authtoken, main.comments, main.signature, main.emailaddress, main.freeformcontacti nfo, main.organization, main.realname, main.nickname, main.lang, main.gecos, main.homephone, main.workphone, main.mobilephone, main.pagerphon e, main.address1, main.address2, main.city, main.state, main.zip, main.country, main.timezone, main.smimecertificate, main.creator, main.crea ted, main.lastupdatedby, main.lastupdated Sort Method: quicksort Memory: 63kB -> Nested Loop (cost=1.83..60.27 rows=1 width=375) (actual time=37.341..7928.353 rows=147 loops=1) -> Nested Loop (cost=1.55..41.68 rows=1 width=379) (actual time=0.038..42.106 rows=50088 loops=1) -> Nested Loop (cost=1.12..28.90 rows=1 width=383) (actual time=0.029..1.436 rows=151 loops=1) -> Nested Loop (cost=0.85..28.43 rows=1 width=8) (actual time=0.023..0.935 rows=151 loops=1) -> Index Scan using grou on cachedgroupmembers cachedgroupmembers_2 (cost=0.42..11.53 rows=2 width=4) (act ual time=0.012..0.158 rows=153 loops=1) Index Cond: (groupid = 4) Filter: (disabled = 0) -> Index Scan using principals_pkey on principals principals_1 (cost=0.42..8.45 rows=1 width=4) (actual ti me=0.004..0.004 rows=1 loops=153) Index Cond: (id = cachedgroupmembers_2.memberid) Filter: ((id <> 1) AND (disabled = 0) AND ((principaltype)::text = 'User'::text)) Rows Removed by Filter: 0 -> Index Scan using users_pkey on users main (cost=0.28..0.47 rows=1 width=375) (actual time=0.002..0.002 rows=1 loops=151) Index Cond: (id = principals_1.id) -> Index Scan using cachedgroupmembers3 on cachedgroupmembers cachedgroupmembers_4 (cost=0.42..12.69 rows=10 width=8) (actual time=0.004..0.196 rows=332 loops=151) Index Cond: (memberid = principals_1.id) Filter: (disabled = 0) Rows Removed by Filter: 0 -> Index Only Scan using acl1 on acl acl_3 (cost=0.28..18.58 rows=1 width=4) (actual time=0.157..0.157 rows=0 loops=50088) Index Cond: ((principaltype = 'Group'::text) AND (principalid = cachedgroupmembers_4.groupid)) Filter: ((((rightname)::text = 'OwnTicket'::text) OR ((rightname)::text = 'SuperUser'::text)) AND (((objecttype)::text = 'RT::Queue'::text) OR (((objecttype)::text = 'RT::System'::text) AND (objectid = 1)))) Rows Removed by Filter: 1 Heap Fetches: 34961 Planning Time: 2.210 ms