On Mon, 26 Jun 2023 at 03:02, Marc Millas <marc.millas@xxxxxxxxxx> 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 Postgres does 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
