On Sun, Jun 25, 2023 at 9:35 PM David Rowley <dgrowle...@gmail.com> wrote:
> On Mon, 26 Jun 2023 at 03:02, Marc Millas <marc.mil...@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 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. > looks like some kind of misunderstanding: what I am asking is: should I , or am I allowed to write that, according to the community, Postgres is unable to do something ? > David > > [1] > https://www.postgresql.org/message-id/flat/20180904015910.GA1797012%40rfd.leadboat.com#0ddccc114c74d1ddaedcb07b64530967 Marc MILLAS Senior Architect +33607850334 www.mokadb.com
