[rt-users] UseSQLForACLChecks / CurrentUserCanSee

[ktm at rice.edu]

On Fri, Jan 25, 2013 at 02:15:02AM +0400, Ruslan Zakirov wrote:
> On Fri, Jan 25, 2013 at 1:52 AM, ktm at rice.edu <ktm at rice.edu> wrote:
> > We are running version 3.8.13 with a PostgreSQL 9.2 backend. I will
> > try to look into it further and see if I can locate the cause of the
> > pathological performance issues.
> 
> Then you don't have one performance improvement that is only in RT
> 4.0+, grab patch from b5b357766929465521281c2886456c6b1fe24824 commit.
> 
> Pg's log of slow queries would be next stop.
> 
> 
> -- 
> Best regards, Ruslan.
> 

Hi Ruslan,

I could not figure out how to pull this patch from the git commit, but
here are the two top queries from the DB logs:

SELECT count ( DISTINCT main.id ) FROM tickets main JOIN groups groups_1 ON ( groups_1.domain = '' ) AND ( groups_1.instance = main.id ) LEFT JOIN cachedgroupmembers cachedgroupmembers_2 ON ( cachedgroupmembers_2.memberid = '' ) AND ( cachedgroupmembers_2.groupid = groups_1.id ) WHERE ( main.STATUS != '' ) AND ( main.queue = '' AND main.STATUS = '' AND ( ( main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' ) OR ( main.OWNER = '' ) OR ( cachedgroupmembers_2.memberid IS NOT NULL AND groups_1.type = '' ) OR ( cachedgroupmembers_2.memberid IS NOT NULL AND groups_1.type = '' ) ) ) AND ( main.type = '' ) AND ( main.effectiveid = main.id ) ;

It was executed 260 time for a total DB time of 2.65 seconds.


And the second query:

SELECT DISTINCT main.* FROM groups main JOIN principals principals_2 ON ( principals_2.id = main.id ) JOIN cachedgroupmembers cachedgroupmembers_3 ON ( cachedgroupmembers_3.groupid = main.id ) JOIN principals principals_1 ON ( principals_1.id = main.id ) WHERE ( principals_1.disabled = '' ) AND ( principals_2.disabled = '' ) AND ( cachedgroupmembers_3.memberid = '' ) AND ( main.domain = '' ) AND ( principals_1.principaltype = '' ) AND ( cachedgroupmembers_3.disabled = '' ) AND ( main.type = '' OR main.type = '' ) ORDER BY main.name ASC;

It was executed 259 time for a total DB time of 1.43 seconds. The
execution was spread over the 12-14 seconds needed to load the page. The individual
queries seem quick. The total DB time is 3 seconds, but the total display time is
almost 5 times as long.

Regards,
Ken