On 07/10/2026 17:42, David Geier wrote:
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.
Glad to hear.
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?
No everything is planned like any other path, each arm in the OR gets
fully planned alone, then UNIONed ALL together, cost is recalculated on
this new Append path, so its cost based, not a rule.
Regards,
Ayoub Kazar
