Re: Poor query plan across OR operator

On Tue, Jan 26, 2010 at 11:41 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Mark Hills <Mark(dot)Hills(at)framestore(dot)com> writes:
>> One of our most-used queries performs poorly (taking over 2 seconds) and a
>> tiny amount of refactoring shows it can be fast (less than 1ms) by
>> transforming the OR case (which spans two tables) into a UNION.
>
> I'd suggest going with the UNION.  We are unlikely to make the planner
> look for such cases, because usually such a transformation would be a
> net loss.  It seems like rather a corner case that it's a win even on
> your example.

This has come up for me, too. But even if we grant that it's
worthwhile, it seems like a tricky optimization to apply in practice,
because unless your row estimates are very accurate, you might easily
apply it when you would have been better off leaving it alone. And it
seems like getting accurate estimates would be hard, since the
conditions might be highly correlated, or not, and they're on
different tables.

...Robert