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

[Alex Vandiver]

On 05/23/2014 12:11 PM, Patrick Muldoon wrote:
> here is the query that is causing us issues..
> 
> SQL(20.280673s): [snip]

This is because you (user #22) have rights to see queue 2, but only see
tickets in it which you are the AdminCc or Owner of.  RT applies these
limits in SQL in RT 4.2 by default (the configuration defaulted to off
in 4.0 and lower), and you're running into a pessimal case.

If you turn $UseSQLForACLChecks off, this query will likely speed up --
but you will run into the situation where ticket result pages may be
"short" due to applying ACLs right before they are displayed.  That is,
a page which nominally contains 50 tickets may only display 40, 20, or 0
responses and still contain a "next page" link.

Ther other fix is to adjust your rights on the queue to not rely on
ticket roles to control ShowTicket.

> and the query plan
> [snip]

For future reference, EXPLAIN ANALYZE will give you the realtime
amounts, as well.

> 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.

SuperUser ignores all permissions, yes.
 - Alex