To: <pgsql-performance@postgresql.org>
Subject: 
 =?utf-8?q?Re=3A_Sub=2Doptimal_plan_for_a_paginated_query_on_a_view_with_another_view_inside_of_it=2E?=
Date: Mon, 05 Aug 2013 10:14:45 +0200
From: <slapo@centrum.sk>
References: <20130802154316.59167BB5@centrum.sk>
In-Reply-To: <20130802154316.59167BB5@centrum.sk>
MIME-Version: 1.0
Message-Id: <20130805101445.8190C260@centrum.sk>
Content-Type: multipart/alternative;
 boundary="_d32c_-------59afe6e5a358e0daf8b21ad827807b89"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.

--_ca29_-------82bc3f7aa1caacba80c8b5cf060015e7
Content-Type: multipart/alternative;
	boundary="_d32c_-------59afe6e5a358e0daf8b21ad827807b89"

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

I apologise, I have neglected to mention Postgres versions tested. It occurs with 9.0 and 9.2
I have typo in my previous message - the sentence about vacuum, reindex and analyze should be:
"I had also run vacuum, reindex and analyze on the whole database, but it seems to have had no effect."
 
Thanks for any thoughts on the issue.
 
Peter Slapansky


______________________________________________________________
> Od: <slapo@centrum.sk>
> Komu: <pgsql-performance@postgresql.org>
> Dátum: 02.08.2013 15:43
> Predmet: Sub-optimal plan for a paginated query on a view with another view inside of it.
>

Good day,
 
I have a performance issue when JOINing a view within another view more than once.
The query takes over three seconds to execute, which is too long in this case. It's not a problem if the tables are nearly empty, but that isn't the case on the production database.
 
I suspect the planner thinks it's better to first put together the v_address view and JOIN it to the parcel table later on, but the function "fx_get_user_tree_subordinates_by_id" should be JOINed to the parcel table first, as it reduces the number of rows to less than 200 and any following JOINs would be much faster.
 
I have also ran vacuum, reindex and analyze on the whole database, but it seems to have had to effect.
 
Is there any way to nudge the planner toward that way of execution?
 
This is the query:
https://app.box.com/s/jzxiuuxoyj28q4q8rzxr <https://app.box.com/s/jzxiuuxoyj28q4q8rzxr>
 
This is the query plan:
https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h <https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h> (plain text)
https://app.box.com/s/jzxiuuxoyj28q4q8rzxr <https://app.box.com/s/jzxiuuxoyj28q4q8rzxr> (graphical output)
 
These are the views:
https://app.box.com/s/uibzidsazwv3eeauovuk <https://app.box.com/s/uibzidsazwv3eeauovuk> (paginated view)
https://app.box.com/s/v71vyexmdyl97m4f3m6u <https://app.box.com/s/v71vyexmdyl97m4f3m6u> (used three times in the paginated view).
 
 
Thank you.
 
Peter Slapansky


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

<p style="padding:0 0 0 0; margin:0 0 0 0;">I apologise, I have neglected to mention Postgres versions tested. It occurs with 9.0 and 9.2</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">I have typo in my previous message - the sentence about vacuum, reindex and analyze should be:</p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">"<span style="font-size: 10pt;">I had also run vacuum, reindex and analyze on the whole database, but it seems to have had no effect.</span><span style="font-size: 10pt;">"</span></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;">Thanks for any thoughts on the issue.</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;"><span style="font-size: 10pt;">Peter Slapansky</span></p>

<p style="padding:0 0 0 0; margin:0 0 0 0;"><span style="font-size: 10pt;"><br />
</span></p>

<p style="padding:0 0 0 0; margin:0 0 0 0;">______________________________________________________________<br />
&gt; Od: &lt;slapo@centrum.sk&gt;<br />
&gt; Komu: &lt;pgsql-performance@postgresql.org&gt;<br />
&gt; D&aacute;tum: 02.08.2013 15:43<br />
&gt; Predmet: 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;"><br />
Good day,<br />
&nbsp;<br />
I have a performance issue when JOINing a view within another view more than once.<br />
The query takes over three seconds to execute, which is too long in this case. It's not a problem if the tables are nearly empty, but that isn't the case on the production database.<br />
&nbsp;<br />
I suspect the planner thinks it's better to first put together the v_address view and JOIN it to the parcel table later on, but the function "fx_get_user_tree_subordinates_by_id" should be JOINed to the parcel table first, as it reduces the number of rows to less than 200 and any following JOINs would be much faster.<br />
&nbsp;<br />
I have also ran vacuum, reindex and analyze on the whole database, but it seems to have had to effect.<br />
&nbsp;<br />
Is there any way to nudge the planner toward that way of execution?<br />
&nbsp;<br />
This is the query:<br />
<a href="https://app.box.com/s/jzxiuuxoyj28q4q8rzxr">https://app.box.com/s/jzxiuuxoyj28q4q8rzxr</a><br />
&nbsp;<br />
This is the query plan:<br />
<a href="https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h">https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h</a> (plain text)<br />
<a href="https://app.box.com/s/jzxiuuxoyj28q4q8rzxr">https://app.box.com/s/jzxiuuxoyj28q4q8rzxr</a> (graphical output)<br />
&nbsp;<br />
These are the views:<br />
<a href="https://app.box.com/s/uibzidsazwv3eeauovuk">https://app.box.com/s/uibzidsazwv3eeauovuk</a> (paginated view)<br />
<a href="https://app.box.com/s/v71vyexmdyl97m4f3m6u">https://app.box.com/s/v71vyexmdyl97m4f3m6u</a> (used three times in the paginated view).<br />
&nbsp;<br />
&nbsp;<br />
Thank you.<br />
&nbsp;<br />
Peter Slapansky</p>


--_d32c_-------59afe6e5a358e0daf8b21ad827807b89--

--_ca29_-------82bc3f7aa1caacba80c8b5cf060015e7--