To: =?utf-8?q?Igor_Neyman?= <ineyman@perceptron.com>,
 =?utf-8?q?Pavel_Stehule?= <pavel.stehule@gmail.com>
Subject: 
 =?utf-8?q?RE=3A_=5BPERFORM=5D_Re=3A_=5BPERFORM=5D_Sub=2Doptimal_plan_for_a_paginated_query_on_a_view_with_another_view_inside_of_it=2E?=
Date: Wed, 07 Aug 2013 16:42:50 +0200
From: <slapo@centrum.sk>
Cc: =?utf-8?q?pgsql=2Dperformance=40postgresql=2Eorg?=
 <pgsql-performance@postgresql.org>
References: <20130802154316.59167BB5@centrum.sk>,
 <CAFj8pRAc-sk8QigQJJn+9MgLJmxU7pjH90Eqd47tAF_1HsGOXQ@mail.gmail.com>,
 <20130807144239.654353D3@centrum.sk>
 <A76B25F2823E954C9E45E32FA49D70EC1BBF38D9@mail.corp.perceptron.com>
In-Reply-To: 
 <A76B25F2823E954C9E45E32FA49D70EC1BBF38D9@mail.corp.perceptron.com>
MIME-Version: 1.0
Message-Id: <20130807164250.26C21834@centrum.sk>
Content-Type: multipart/alternative;
 boundary="_d32c_-------3e41c39d2dd253fceb7e38aac2b283ad"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.

--_ca29_-------7c296d4c426ea5811d0f225a319db5d2
Content-Type: multipart/alternative;
	boundary="_d32c_-------3e41c39d2dd253fceb7e38aac2b283ad"

--_d32c_-------3e41c39d2dd253fceb7e38aac2b283ad
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

You're right, it does... but it's quite odd, because I re-ran the explain-analyze statement and got the same results.
Still, the query now runs for about a second as mentioned before, so it's almost like something's missing from the explain, but I'm certain I copied it all.
 
I did this via pgadmin, but that shouldn't matter, should it?
 
Thank you,
 
Peter Slapansky
______________________________________________________________
> Od: Igor Neyman <ineyman@perceptron.com>
> Komu: "slapo@centrum.sk" <slapo@centrum.sk>, Pavel Stehule <pavel.stehule@gmail.com>
> Dátum: 07.08.2013 15:47
> Predmet: RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.
>
> CC: "pgsql-performance@postgresql.org"
Your last explain analyze (with 3 settings set to 32)  shows query duration 10ms, not 1sec.
Am I wrong? 

Regards,
Igor Neyman
 
______________________________________________________________
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of slapo@centrum.sk
Sent: Wednesday, August 07, 2013 8:43 AM
To: Pavel Stehule
Cc: pgsql-performance@postgresql.org
Subject: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

Good day,
 
I have included a link to the result of EXPLAIN ANALYZE. It's this one:
https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h <https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h>
 
Here's a link to Depesz's explain (if links to the site are okay):
http://explain.depesz.com/s/gCk <http://explain.depesz.com/s/gCk>
 
I have just tried setting geqo_threshold, join_collapse_limit and from_collapse_limit to 16, but it yielded no improvement.
Changing those three parameters to 32 did speed up the query from about 3.3 seconds to about a second (give or take 50 ms), which is a pretty good improvement, but not quite there, as I'm looking to bring it down to about 300 ms if possible. Changing those three settings to 48 yielded no improvements over 32.
Is there possibly something something else to tweak there?
 
Here's EXPLAIN ANALYZE output when the three settings have been set to 32:
http://explain.depesz.com/s/cj2 <http://explain.depesz.com/s/cj2>
 
Thank you.
 
Peter Slapansky


--_d32c_-------3e41c39d2dd253fceb7e38aac2b283ad
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<p style="padding:0 0 0 0; margin:0 0 0 0;">You're right, it does... but it's quite odd, because I re-ran the explain-analyze statement and got the same results.</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">Still, the query now runs for about a second as mentioned before, so it's almost like something's missing from the explain, but I'm certain I copied it all.</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">&nbsp;</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">I did this via pgadmin, but that shouldn't matter, should it?</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">&nbsp;</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">Thank you,</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">&nbsp;</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">Peter Slapansky</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">______________________________________________________________<br />
&gt; Od: Igor Neyman &lt;ineyman@perceptron.com&gt;<br />
&gt; Komu: "slapo@centrum.sk" &lt;slapo@centrum.sk&gt;, Pavel Stehule &lt;pavel.stehule@gmail.com&gt;<br />
&gt; D&aacute;tum: 07.08.2013 15:47<br />
&gt; Predmet: RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.<br />
&gt;</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">&gt; CC: "pgsql-performance@postgresql.org"</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">Your last explain analyze (with 3 settings set to 32) &nbsp;shows query duration 10ms, not 1sec.<br />
Am I wrong?&nbsp;<br />
<br />
Regards,<br />
Igor Neyman</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">&nbsp;</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">______________________________________________________________<br />
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of slapo@centrum.sk<br />
Sent: Wednesday, August 07, 2013 8:43 AM<br />
To: Pavel Stehule<br />
Cc: pgsql-performance@postgresql.org<br />
Subject: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.<br />
<br />
Good day,<br />
&nbsp;<br />
I have included a link to the result of EXPLAIN ANALYZE. It's this one:<br />
<a href="https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h">https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h</a><br />
&nbsp;<br />
Here's a link to Depesz's explain (if links to the site are okay):<br />
<a href="http://explain.depesz.com/s/gCk">http://explain.depesz.com/s/gCk</a><br />
&nbsp;<br />
I have just tried setting&nbsp;geqo_threshold,&nbsp;join_collapse_limit and from_collapse_limit to 16, but it yielded no improvement.<br />
Changing those three parameters to 32 did speed up the query from about 3.3 seconds to about a second (give or take 50 ms), which is a pretty good improvement, but not quite there, as I'm looking to bring it down to about 300 ms if possible. Changing those three settings to 48 yielded no improvements over 32.<br />
Is there possibly something something else to tweak there?<br />
&nbsp;<br />
Here's EXPLAIN ANALYZE output when the three settings have been set to 32:<br />
<a href="http://explain.depesz.com/s/cj2">http://explain.depesz.com/s/cj2</a><br />
&nbsp;<br />
Thank you.<br />
&nbsp;<br />
Peter Slapansky<br />
<br />
</p>


--_d32c_-------3e41c39d2dd253fceb7e38aac2b283ad--

--_ca29_-------7c296d4c426ea5811d0f225a319db5d2--