On Mon, 26 Jun 2023 at 03:02, Marc Millas <marc.millas@mokadb.com> wrote:
> When I ask this list, David Rowley suggest to rewrite the SQL, replacing the OR by a union.
>
> Fine, this do work, even if a bit complex as the original SQL was a set of intricate joins.
>
>
> So, either this behaviour ( postgres unable to find an appropriate plan for join with OR clauses)
> is a true bug which is waiting to be corrected, either the doc is lacking a chapter on which one can read that
Postgresdoes NOT support that syntax as soon as there is some data volumes.
The problem is that out of the 3 methods PostgreSQL uses to join
tables, only 1 of them supports join conditions with an OR clause.
Merge Join cannot do this because results can only be ordered one way
at a time. Hash Join technically could do this, but it would require
that it built multiple hash tables. Currently, it only builds one
table. That leaves Nested Loop as the join method to implement joins
with OR clauses. Unfortunately, nested loops are quadratic and the
join condition must be evaluated once per each cartesian product row.
That does not scale well.
Tom Lane did start some work [1] to allow the planner to convert some
queries to use UNION instead of evaluating OR clauses, but, if I
remember correctly, it didn't handle ORs in join conditions, though
perhaps having it do that would be a natural phase 2. I don't recall
why the work stopped.
> So, now I am writing some kind of recipe book for the users of that DB.
>
> What should I write ?
You're asking a public mailing list to write private documentation for
the company you work for? hmm.
David
[1]
https://www.postgresql.org/message-id/flat/20180904015910.GA1797012%40rfd.leadboat.com#0ddccc114c74d1ddaedcb07b64530967
