Re: bug or lacking doc hint

On Sun, Jun 25, 2023 at 9:35 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Mon, 26 Jun 2023 at 03:02, Marc Millas <marc(dot)millas(at)mokadb(dot)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