[rt-users] Slow ticket search screen draw with RT 4.2.10 and PostgreSQL-9.4

[ktm at rice.edu]

Hi RT community,

I am testing the performance. When I pull up

Search->Tickets->New Search

as the RT superuser, it takes 6 seconds, which while not
exactly speedy, is tolerable. Unfortunately, for normal
users the time is 32s. I have attached the EXPLAIN ANALYZE
results and the plan looks reasonable. It wouldn't be too
bad if the hit was taken just once, but everytime the page
is loaded it takes 32s. Does anyone have any ideas about
how to improve the performance? We are running RT-4.2.10
with a PostgreSQL-9.4 backend.

Regards,
Ken
-------------- next part --------------
                                                                                                                                                                                                                                                                                                          QUERY PLAN                                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=332.77..332.87 rows=1 width=879) (actual time=32072.684..32134.271 rows=290 loops=1)
   ->  Sort  (cost=332.77..332.78 rows=1 width=879) (actual time=32072.680..32076.145 rows=48176 loops=1)
         Sort Key: main.name, main.id, main.password, main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, main.emailencoding, main.webencoding, main.externalcontactinfoid, main.contactinfosystem, main.externalauthid, main.authsystem, main.gecos, main.homephone, main.workphone, main.mobilephone, main.pagerphone, main.address1, main.address2, main.city, main.state, main.zip, main.country, main.timezone, main.pgpkey, main.creator, main.created, main.lastupdatedby, main.lastupdated, main.authtoken, main.smimecertificate
         Sort Method: quicksort  Memory: 25252kB
         ->  Nested Loop  (cost=1.99..332.76 rows=1 width=879) (actual time=0.286..30180.225 rows=48176 loops=1)
               ->  Nested Loop  (cost=1.71..322.76 rows=2 width=883) (actual time=0.054..561.465 rows=496080 loops=1)
                     ->  Nested Loop  (cost=1.28..316.88 rows=1 width=887) (actual time=0.046..3.581 rows=310 loops=1)
                           ->  Nested Loop  (cost=0.86..310.49 rows=12 width=8) (actual time=0.041..2.022 rows=310 loops=1)
                                 ->  Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_2  (cost=0.43..43.53 rows=359 width=4) (actual time=0.024..0.331 rows=312 loops=1)
                                       Index Cond: ((groupid = 4) AND (disabled = 0::smallint))
                                       Heap Fetches: 312
                                 ->  Index Scan using principals_pkey on principals principals_1  (cost=0.43..0.69 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=312)
                                       Index Cond: (id = cachedgroupmembers_2.memberid)
                                       Filter: ((id <> 1) AND (disabled = 0::smallint) AND ((principaltype)::text = 'User'::text))
                                       Rows Removed by Filter: 0
                           ->  Index Scan using users_pkey on users main  (cost=0.42..0.48 rows=1 width=879) (actual time=0.003..0.004 rows=1 loops=310)
                                 Index Cond: (id = principals_1.id)
                     ->  Index Scan using cachedgroupmembers1 on cachedgroupmembers cachedgroupmembers_4  (cost=0.43..3.48 rows=48 width=8) (actual time=0.007..1.412 rows=1600 loops=310)
                           Index Cond: (memberid = principals_1.id)
                           Filter: (disabled = 0::smallint)
                           Rows Removed by Filter: 0
               ->  Index Only Scan using acl1 on acl acl_3  (cost=0.29..4.85 rows=3 width=4) (actual time=0.059..0.059 rows=0 loops=496080)
                     Index Cond: ((rightname = 'OwnTicket'::text) AND (principaltype = 'Group'::text) AND (principalid = cachedgroupmembers_4.groupid))
                     Filter: (((objecttype)::text = 'RT::Queue'::text) OR (((objecttype)::text = 'RT::System'::text) AND (objectid = 1)))
                     Heap Fetches: 48176
 Planning time: 2.256 ms
 Execution time: 32135.475 ms
(27 rows)