Re: Poor query plan across OR operator

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Jan 26, 2010 at 11:41 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

Actually, in the type of case Mark is showing, the estimates might be
*more* accurate since the condition gets decomposed into separate
per-table conditions. I'm still dubious about how often it's a win
though.

There's another problem, which is that transforming to UNION isn't
necessarily a safe transformation: it only works correctly if the
query output columns are guaranteed unique. Otherwise it might fold
duplicates together that would have remained distinct in the original
query. If your query output columns include a primary key then the
planner could be confident this was safe, but that reduces the scope
of the transformation even further ...

regards, tom lane