<div dir="ltr">You made some typing mistake, you should get something like this:<br><br> QUERY PLAN <br>---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br> Unique (cost=33.54..33.62 rows=1 width=329)<br> -> Sort (cost=33.54..33.54 rows=1 width=329)<br> Sort Key: <a href="http://main.name">main.name</a>, <a href="http://main.id">main.id</a>, main.password, main.authtoken, main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, 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.smimecertificate, main.creator, main.created, main.lastupdatedby, main.lastupdated<br> -> Nested Loop (cost=1.12..33.53 rows=1 width=329)<br> Join Filter: (<a href="http://main.id">main.id</a> = <a href="http://principals_1.id">principals_1.id</a>)<br> -> Nested Loop (cost=0.84..33.18 rows=1 width=337)<br> Join Filter: (<a href="http://main.id">main.id</a> = cachedgroupmembers_2.memberid)<br> -> Nested Loop (cost=0.56..29.94 rows=9 width=333)<br> -> Nested Loop (cost=0.28..13.74 rows=51 width=4)<br> -> Seq Scan on acl acl_3 (cost=0.00..5.40 rows=1 width=4)<br> Filter: (((principaltype)::text = 'Group'::text) AND ((rightname)::text = 'OwnTicket'::text) AND ((((objecttype)::text = 'RT::Queue'::text) AND (objectid = 56)) OR (((objecttype)::text = 'RT::System'::text) AND (objectid = 1))))<br> -> Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_4 (cost=0.28..8.32 rows=2 width=8)<br> Index Cond: ((groupid = acl_3.principalid) AND (disabled = 0))<br> -> Index Scan using users_pkey on users main (cost=0.28..0.31 rows=1 width=329)<br> Index Cond: (id = cachedgroupmembers_4.memberid)<br> -> Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_2 (cost=0.28..0.35 rows=1 width=4)<br> Index Cond: ((groupid = 4) AND (memberid = cachedgroupmembers_4.memberid) AND (disabled = 0))<br> -> Index Scan using principals_pkey on principals principals_1 (cost=0.28..0.34 rows=1 width=4)<br> Index Cond: (id = cachedgroupmembers_4.memberid)<br> Filter: ((id <> 1) AND (disabled = 0) AND ((principaltype)::text = 'User'::text))<br><br></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Sep 28, 2016 at 2:42 PM, Joel Bergmark <span dir="ltr"><<a href="mailto:joel.bergmark@t3.se" target="_blank">joel.bergmark@t3.se</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi!<br>
<br>
This is the output from explain:<br>
<br>
postgres=# EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = <a href="http://main.id" rel="noreferrer" target="_blank">main.id</a> ) 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' AND ACL_3.ObjectId = 56) 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') 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;<br>
ERROR: relation "users" does not exist<br>
LINE 1: EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN AC...<br>
<br>
I have looked for more queries that takes time and its always this query that generates the slow behavior, but seemingly the query itself seems fine "users" exists in db, but then again obviously postgres has issues with something here.<br>
<br>
Any thoughts?<br>
<br>
Regards, Joel<br>
<br>
-----Ursprungligt meddelande-----<br>
Från: rt-users [mailto:<a href="mailto:rt-users-bounces@lists.bestpractical.com">rt-users-bounces@<wbr>lists.bestpractical.com</a>] För Nilesh<br>
Skickat: den 27 september 2016 17:36<br>
Till: <a href="mailto:rt-users@lists.bestpractical.com">rt-users@lists.bestpractical.<wbr>com</a><br>
Ämne: Re: [rt-users] Postgresql 4.4.1 slow queries?<br>
<div class="HOEnZb"><div class="h5"><br>
On Tue, 2016-09-27 at 07:52 +0000, Joel Bergmark wrote:<br>
> Hi,<br>
> <br>
> After upgrading to version 4.4.1 i have noticed that the SQL-queries<br>
> takes significant longer time to produce a result, on average on my<br>
> system, running Ubuntu 14.04 LTS, Postgres 9.3, Apache with perlmod. 8<br>
> gigram and plenty of CPU in a vmware cluster on SSD:s (likely not a<br>
> hardware issue). We did not experience RT to have been this slow on 4.4.0.<br>
> <br>
> Each query takes about 350ms to give a result, occasionally up to<br>
> 4000ms have been seen, not a big problem but somethings that needs to be fixed.<br>
> <br>
> Postgres have access to shared buffers = 2048 and effective_cache_size<br>
> = 4096MB that was modified yesterday but queries still are same speed.<br>
> <br>
> I have read plenty on postgres optimizing but not much seem to make a<br>
> difference, and the<br>
> <a href="https://rt-wiki.bestpractical.com/wiki/PerformanceTuning" rel="noreferrer" target="_blank">https://rt-wiki.bestpractical.<wbr>com/wiki/PerformanceTuning</a><br>
> seems a bit out of date.<br>
> <br>
> Example of problem:<br>
> <br>
> 192.168.2.65 - - [27/Sep/2016:09:06:49 +0200] "GET<br>
> /Search/Build.html?NewQuery=1 HTTP/1.1" 200 12072 Mozilla/5.0 (Windows<br>
> NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko)<br>
> Chrome/53.0.2785.101 Safari/537.36"<br>
><br>
> 09:06:52 CEST LOG: duration: 3362.432 ms execute dbdpg_p2242_1745:<br>
> SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN<br>
> Principals<br>
> Principals_1 ON ( Principals_1.id = <a href="http://main.id" rel="noreferrer" target="_blank">main.id</a> ) JOIN CachedGroupMembers<br>
> CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId =<br>
> Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON (<br>
> CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE<br>
> ((ACL_3.ObjectType =<br>
> 'RT::Queue') OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId <br>
> = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND<br>
> (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR<br>
> ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled =<br>
> '0') AND (CachedGroupMembers_2.GroupId = '4') AND<br>
> (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled =<br>
> '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id !=<br>
> '1') ORDER BY main.Name ASC<br>
><br>
> I’m looking for any suggestions about this, and I’m not an expert on<br>
> postgresql, but guess that this could have something to do with RT:s<br>
> “($UseSQLForACLChecks, 1);” or modperl via Apache or simply some index<br>
> in postgres?<br>
> <br>
> Appreciate any feedback :-)<br>
><br>
> Regards, Joel<br>
> <br>
> ---------<br>
> RT 4.4 and RTIR training sessions, and a new workshop day!<br>
> <a href="https://bestpractic" rel="noreferrer" target="_blank">https://bestpractic</a> <a href="http://al.com/training" rel="noreferrer" target="_blank">al.com/training</a><br>
> * Boston - October 24-26<br>
> * Los Angeles - Q1 2017<br>
<br>
What do you get if you run EXPLAIN over that query directly in psql CLI?<br>
<br>
--<br>
Nilesh<br>
<br>
---------<br>
RT 4.4 and RTIR training sessions, and a new workshop day! <a href="https://bestpractical.com/training" rel="noreferrer" target="_blank">https://bestpractical.com/<wbr>training</a><br>
* Boston - October 24-26<br>
* Los Angeles - Q1 2017<br>
</div></div></blockquote></div><br></div>