From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: kaloyan@digsys.bg
To: pgsql-performance@postgresql.org
Subject: Re: Question about LEFT JOIN and query plan
Date: Tue, 07 Sep 2010 11:54:52 -0500
Message-ID: <4C86280C02000025000352AF@gw.wicourts.gov> (raw)
In-Reply-To: <4C85E774.7090204@digsys.bg>
References: <4C811F68.9060601@digsys.bg>
<4C80E73402000025000351AF@gw.wicourts.gov>
<4C85E774.7090204@digsys.bg>
>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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, kaloyan@digsys.bg
Subject: Re: Question about LEFT JOIN and query plan
In-Reply-To: <4C86280C02000025000352AF@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox