Re: 2 left joins causes seqscan

Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
> Willy-Bas Loos <willybas(at)gmail(dot)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