Re: Question about LEFT JOIN and query plan - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Question about LEFT JOIN and query plan
Date
Msg-id 4C86280C02000025000352AF@gw.wicourts.gov
Whole thread Raw
In response to Re: Question about LEFT JOIN and query plan  (Kaloyan Iliev Iliev <kaloyan@digsys.bg>)
List pgsql-performance
>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

pgsql-performance by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: Question about LEFT JOIN and query plan
Next
From: David Kerr
Date:
Subject: pgbench could not send data to client: Broken pipe