Re: [PROPOSAL] Expand OR clauses in joins to UNION ALL paths

On 07.10.26 13:02, Ayoub Kazar wrote:
> Attached is a patch that lets the planner rewrite an OR clause that spans
> several relations into a UNION ALL with one branch per arm, and use that
> plan when it is estimated to be cheaper.
>
> Background
>
> OR clauses that mention more than one relation are awkward for the planner
> today. If the OR is the join condition itself (a.x = b.y OR a.x = b.z), it
> cannot be a hash or merge join clause, so the only choice left is a nested
> loop, usually with a BitmapOr of index scans on the inner side that is run
> again for every outer row. If it is a filter on two different tables
> (a.f = 1 OR b.g = 2), BitmapOr cannot help either, since it only combines
> indexes of one relation, and the OR ends up as a join filter. The
> textbook case is a star schema with an OR over conditions on two
> dimension tables.

This transformation is extremely useful and in my experience, queries
that can profit from it occur frequently. So +1 for the effort.

> Where it is not considered:
>
> - ORs that only reference one relation, the argument is that in this
> case, surely a BitmapOr or anything else do the job.
> - volatile functions in the jointree, security quals or function/subquery
>   RTEs, since the arms are evaluated more than once
> - FULL joins, TABLESAMPLE, FOR UPDATE/SHARE, CTEs, set operations,
>   recursive queries, well anything other than SELECT ?
> - partitioned tables, also inside subqueries. Child RTEs are created inside
>   query_planner(), so after arms prune differently their range table
>   indexes no longer match the original one, i didn't try to make it work
> yet the solution here is maybe to refix pointers back to match root
> PlannerInfo for each arm, but this felt too much, i wonder if its worth
> it for the benefit it can get for partitioned tables.
> - ORs in JOIN ... ON clauses. Only the top level WHERE quals are looked at
>   for now.

The transformation is not always beneficial. Here's an example:

SELECT o.*
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'returned' -- very selective: 1k rows
OR c.tier = 'standard'; -- very unselective: 900k of 1M customers

When one condition is very selective a NLJ on the table with the more
selective condition is actually pretty efficient.

Do you unconditionally apply the transformation, or do you have
heuristics to only do it if it likely pays off?

--
David Geier