Re: 2 left joins causes seqscan

Willy-Bas Loos <willybas@gmail.com> wrote:

> I can't understand what is confusing the planner.

Well, it doesn't do exhaustive proofs of whether two queries are
equivalent.  If it did, it would still not have come up with a plan
like your second one, because it is not equivalent.  The trick in
planning is to stop when the cost of further analysis is likely to
exceed the benefits derived from a better plan.  The fact that the
first query was complex enough that *you* weren't able to
accurately optimize it better before posting is pretty good
evidence that it's moving into the realm of "expensive to
optimize".

Now, if you want to propose some specific check the planner can
make to try to find a plan like the one generated for the query I
showed (which I believe actually *is* equivalent to your first
query), perhaps someone will find implementing that a better use of
their time than any of the other things they have in front of them
to work on, and benchmarks can establish what the planning cost of
that is for people running queries it *won't* benefit compared to
the benefits seen in queries that *do* benefit.  There is an
understandable reluctance to add planning costs to every query run
in order to cause better plan choice for a very small percentage of
queries, especially when there is a workaround -- of explicitly
writing a UNION for those cases.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company