Poor postgres performance in search builder

Hello,

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? - #8 by Ulf_Renman ). 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
 

Thanks!

Hi,

I had this problem with PostgreSQL 9.6 and fixed it by running ANALYZE on the tables involved. You may want to increase the statistics target as well. Note, I had to analyze multiple times to get a sampling that worked quickly. I think PostgreSQL 12 improves some of the statistics. I did see that there is a new releae of DBD::Pg that supports PostgreSQL 12. Did you try it?

Regards,
Ken

Thank you for the suggestions. Running ANALYZE on users and cachedgroupmembers tables seems to have no effect on the performance, wonder if I’m not doing it correctly.

With regards to PgSQL 12, the perl DBD::Pg module itself works fine, it’s RT that breaks. We’ve tested it both with upgrading existing database and new import, and both fail. Considering it’s still fairly new, I will wait to use it as an actual solution to this problem.

My users table has currently ~1,800 entries, while the cachedgroupmembers has ~650,000. Based on what I see in other posts, these numbers are fairly low, so I’m not sure what’s going on with that query.

Another data point is that I’m able to repeat this issue with the imported data set on three different hosts (dev/test/newprod). Yet on another older system running CentOS 6 with PgSQL 8.4 this query takes 1.6 seconds, with same data set. All four systems are almost identical vms, so it’s not the underlying OS/host that’s the problem, and full text searching is blazingly fast.

Thanks in advance!

Hi,
I did want to mention that I needed to ANALYZE all of the items in the query and not just the users and cachedgroupmembers tables. Only those tables did not address the problem.

Regards,
Ken

We have more than 4.5M entries in cachedgroupmembers, you can imagine performance. Tickets, even smallest, opens more than 30s.

AQO PostgreSQL extension saved me. After learning/training this nightmare query runs in a split seconds.