Обсуждение: 2 left joins causes seqscan

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

> As you can see, the second query is far more efficient, even
> though it scans both tables twice to combine the results.

But the two queries don't return the same results.  Of course the
second one will be faster.  The simple equivalent of your second
query is:

explain analyze select a.field1, b.title
  from a
  join b on b.id = a.id
  where lower(b.title) like 'abcd%'
    and lang in (1, 2);

The equivalent of your first query is to take the result sets from
these two queries:

select a1.field1, b1.title, b2.title
  from a a1
  join b b1 on b1.id = a1.id and b1.lang = 1
  left join b b2 on (b2.id = a1.id and b2.lang = 2)
  where lower(b1.title) like'abcd%'
union
select a2.field1, b4.title, b3.title
  from a a2
  join b b3 on b3.id = a2.id and b3.lang = 2
  left join b b4 on (b4.id = a2.id and b4.lang = 1)
  where lower(b3.title) like'abcd%';

The above form does optimize better than the original, but it's not
too surprising that the planner can't come up with the optimal
plan; you've posed quite a challenge for it.

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

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

Kevin Grittner <kgrittn@ymail.com> writes:
> 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.

Yeah.  The short reason why the index was not used in the original
query is that the supposedly indexable condition was inside an OR,
which made it useless as an index qualification: rows not satisfying
that condition at all might yet satisfy the query as a whole.  The
planner does have some ability to use indexes when every arm of the
OR includes an indexable condition on the same table, but that was
not the case here.

Another point about the proposed transformation is that an OR in
WHERE is far from equivalent to a UNION: WHERE ... OR does not result
in full de-duplication.  You could possibly conclude they were
equivalent if the query output columns included a primary key,
but that was not the case here.  In any case, the planner includes
no logic that could transform OR into UNION, and I'd be pretty
hesitant to add any even if the transformation were formally correct,
because the planner has no ability to optimize UNION meaningfully.
You'd often get a worse plan than you get now.  (Perhaps that will
change someday, but it's not very high on the priority list.)

            regards, tom lane