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

>> 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.

Ah, that's cool!

I was thinking about it like a rewrite happening post-parsing /
pre-planning.

--
David Geier