[rt-users] Slow Query on RT 4.2.4 after upgrade from 4.0

[Patrick Muldoon]

Recently upgraded from 4.0 to 4.2 and have noticed that we are now getting slow queries on pretty much all ticket history queries.   I ’ve been reading the list and attempting to google but I cannot seem to figure out what the cause is. 

This RT database started life 11 years ago and has been dragged along from upgrade to upgrade and this is the first time we’ve run into any real issue.   I’ve compared our Schema with a freshly installed version of 4.2 and don’t see any indexes missing, or anything along those lines.  

I think that it has something to do with the way either have groups setup, or permissions, since if I make my account an admin/super user all the queries are super fast.. 

We are running on postgresql 9.3.4 

here is the query that is causing us issues..

SQL(20.280673s):

SELECT COUNT(DISTINCT main.id) 
  FROM Tickets main 
  JOIN Groups Groups_1  ON ( LOWER(Groups_1.Domain) = 'rt::ticket-role' ) AND ( LOWER(Groups_1.Name) = 'requestor' ) AND ( Groups_1.Instance = main.id ) 
  JOIN Groups Groups_3  ON ( LOWER(Groups_3.Domain) = 'rt::ticket-role' ) AND ( Groups_3.Instance = main.id ) 
  JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id ) 
  LEFT JOIN CachedGroupMembers CachedGroupMembers_4  ON ( CachedGroupMembers_4.Disabled = '0' ) AND ( CachedGroupMembers_4.MemberId = '22' ) AND ( CachedGroupMembers_4.GroupId = Groups_3.id )  
  WHERE (
          (  
           ( main.Queue = '1' OR main.Queue = '4' OR main.Queue = '5' )  OR  
           ( main.Owner = '22' AND main.Queue = '2' )  OR  
           ( CachedGroupMembers_4.MemberId IS NOT NULL AND LOWER(Groups_3.Name) = 'admincc' AND main.Queue = '2' )  
          ) 
        )
        AND (main.IsMerged IS NULL) 
        AND (main.Status != 'deleted') 
        AND (main.Type = 'ticket') 
        AND ( 
              ( CachedGroupMembers_2.MemberId = '364' )  AND  
              ( LOWER(main.Status) = 'new' OR LOWER(main.Status) = 'open' OR LOWER(main.Status) = 'stalled' ) 
            )


And then the next query which returns the data takes about the same time to run.  so a small ticket takes around 40 seconds  to render. 

and the query plan 

 Aggregate  (cost=37.50..37.51 rows=1 width=4)
   ->  Nested Loop Left Join  (cost=1.95..37.50 rows=1 width=4)
         Filter: ((main.queue = 1) OR (main.queue = 4) OR (main.queue = 5) OR ((main.owner = 22) AND (main.queue = 2)) OR ((cachedgroupmembers_4.memberid IS NOT NULL) AND (lower((groups_3.name)::text) = 'admincc'::text) AND (main.queue = 2)))
         ->  Nested Loop  (cost=1.53..30.61 rows=1 width=22)
               ->  Nested Loop  (cost=1.11..22.16 rows=1 width=26)
                     Join Filter: (groups_1.instance = main.id)
                     ->  Nested Loop  (cost=0.83..18.32 rows=1 width=22)
                           ->  Index Scan using groups2 on groups groups_1  (cost=0.42..8.44 rows=1 width=8)
                                 Index Cond: ((lower((domain)::text) = 'rt::ticket-role'::text) AND (lower((name)::text) = 'requestor'::text))
                           ->  Index Scan using groups2 on groups groups_3  (cost=0.41..9.87 rows=1 width=14)
                                 Index Cond: ((lower((domain)::text) = 'rt::ticket-role'::text) AND (instance = groups_1.instance))
                     ->  Index Scan using tickets_pkey on tickets main  (cost=0.28..3.83 rows=1 width=12)
                           Index Cond: (id = groups_3.instance)
                           Filter: ((ismerged IS NULL) AND ((status)::text <> 'deleted'::text) AND ((type)::text = 'ticket'::text) AND ((queue = 1) OR (queue = 4) OR (queue = 5) OR (queue = 2) OR (queue = 2)) AND ((lower((status)::text) = 'new'::text) OR (lower((status)::text) = 'open'::text) OR (lower((status)::text) = 'stalled'::text)))
               ->  Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_2  (cost=0.41..8.44 rows=1 width=4)
                     Index Cond: ((groupid = groups_1.id) AND (memberid = 364) AND (disabled = 0::smallint))
         ->  Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_4  (cost=0.41..6.85 rows=1 width=8)
               Index Cond: ((groupid = groups_3.id) AND (memberid = 22) AND (disabled = 0::smallint))
(18 rows)


So pointers on places to look or things to try would be very helpful.  I am still trying to wrap my head around  why if I am root it doesn’t slow down, which leads me to think it has something to do with the way we have permissions setup.


Thanks,
-Patrick

--
Patrick Muldoon
Network/Software Engineer
INOC (http://www.inoc.net)
PGPKEY (http://www.inoc.net/~doon)
Key ID: 0x2D808DE5

I do not fear computers.  I fear the lack of them.  - Isaac Asimov