Message-Id: <4C86280C02000025000352AF@gw.wicourts.gov>
Date: Tue, 07 Sep 2010 11:54:52 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <kaloyan@digsys.bg>,<pgsql-performance@postgresql.org>
Subject: Re: Question about LEFT JOIN and query plan
References: <4C811F68.9060601@digsys.bg>
	<4C80E73402000025000351AF@gw.wicourts.gov> <4C85E774.7090204@digsys.bg>
In-Reply-To: <4C85E774.7090204@digsys.bg>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

>Kaloyan Iliev Iliev <kaloyan@digsys.bg> wrote:
> Kevin Grittner wrote:
 
>> Out of curiosity, what happens if you consistently use JOIN
>> clauses, rather than mixing that with commas?:
 
> The plan improves. So can you explain why?
 
Commas in a FROM clause bind more loosely than JOIN clauses,
rearrangement from one side of an outer join to the other is a bit
tricky, and the *_collapse_limit settings (which you have not shown)
can affect how much JOIN rearrangement is done for a complex query. 
On a quick scan over your query it didn't appear that the
rearrangement would break anything, so I wondered whether the
planner might do better if you made its job a bit easier by putting
the inner joins all on the left to start with and putting the tables
closer to the order of efficient access.
 
If you still see this difference with very high collapse limits,
your example might be a good one to support further work on the
optimizer; but it would be more useful for that if you could create
a synthetic case to demonstrate the problem -- starting with
creation of tables, data, and indexes on which the different forms
of the query yielded different plans.
 
-Kevin