From: Markus Schulz <msc@antzsystem.de>
Organization: AntzSystem GmbH
To: pgsql-performance@postgresql.org
Subject: Re: problem with from_collapse_limit and joined views
Date: Sat, 4 Dec 2010 19:46:21 +0100
User-Agent: KMail/1.13.5 (Linux/2.6.36-trunk-686; KDE/4.4.5; i686; ; )
Cc: Tom Lane <tgl@sss.pgh.pa.us>
References: <4CFA09E2020000250003829C@gw.wicourts.gov>
	<24137.1291481998@sss.pgh.pa.us>
In-Reply-To: <24137.1291481998@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Message-Id: <201012041946.21722@Mail-Followup-To>

Am Samstag 04 Dezember 2010 schrieb Tom Lane:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
...
> > One option would be to create a different user for running queries
> > which read from complex views such as this.
> 
> If you don't want to change the collapse limits, the only other
> option is to restructure this specific query so that its syntactic
> structure is closer to the ideal join order.  Look at the plan you
> get in the good-performing case and re-order the join syntax to look
> like that.

no that's not working in this case.
view1 and view2 are written with explicit joins and no better join was 
possible. Each view works perfect standalone. 
In my above example i have rewritten view1 without explicit joins only 
for testing purpose. Without explicit joins i can gather the optimal 
query plan from a slightly higher from_collapse_limit (see workaround 2 
from my initial posting). 
If both views using explicit joins the from_collapse_limit is useless 
(only join_collapse_limit usable).

The problem exists only for "view1 JOIN view2" and that pgsql don't 
"see" that an element of view2 contains an index-access for reducing the 
data from view1. Only if he can break the complete join of both views 
into one query-plan he can "see" this. But for this i must raise the 
limits.

Looks like some improvement to the geco optimizer was needed here ;)


regards
msc