[rt-users] Postgresql 4.4.1 slow queries?

[Nilesh]

On Tue, 2016-09-27 at 07:52 +0000, Joel Bergmark wrote:
> Hi,
>  
> After upgrading to version 4.4.1 i have noticed that the SQL-queries takes
> significant longer time to produce a result, on average on my system, running
> Ubuntu 14.04 LTS, Postgres 9.3, Apache with perlmod. 8 gigram and plenty of
> CPU in a vmware cluster on SSD:s (likely not a hardware issue). We did not
> experience RT to have been this slow on 4.4.0.
>  
> Each query takes about 350ms to give a result, occasionally up to 4000ms have
> been seen, not a big problem but somethings that needs to be fixed.
>  
> Postgres have access to shared buffers = 2048 and effective_cache_size =
> 4096MB that was modified yesterday but queries still are same speed.
>  
> I have read plenty on postgres optimizing but not much seem to make a
> difference, and the https://rt-wiki.bestpractical.com/wiki/PerformanceTuning
> seems a bit out of date.
>  
> Example of problem:
>  
> 192.168.2.65 - - [27/Sep/2016:09:06:49 +0200] "GET
> /Search/Build.html?NewQuery=1 HTTP/1.1" 200 12072 Mozilla/5.0 (Windows NT
> 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/53.0.2785.101
> Safari/537.36"
> 
> 09:06:52 CEST LOG:  duration: 3362.432 ms  execute dbdpg_p2242_1745: 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')  ORDER BY main.Name ASC
> 
> I’m looking for any suggestions about this, and I’m not an expert on
> postgresql, but guess that this could have something to do with RT:s
> “($UseSQLForACLChecks, 1);” or modperl via Apache or simply some index in
> postgres?
>  
> Appreciate any feedback :-)
> 
> Regards, Joel
>  
> ---------
> RT 4.4 and RTIR training sessions, and a new workshop day! https://bestpractic
> al.com/training
> * Boston - October 24-26
> * Los Angeles - Q1 2017

What do you get if you run EXPLAIN over that query directly in psql CLI?

-- 
Nilesh