Re: Suboptimal query plan fixed by replacing OR with UNION

On Thu, Jul 5, 2012 at 7:16 PM, Steven Schlansker <steven(at)likeness(dot)com>wrote:

>
> On Jul 5, 2012, at 3:51 PM, Tom Lane wrote:
>
> > Steven Schlansker <steven(at)likeness(dot)com> writes:
> >> Why is using an OR so awful here?
> >
> > Because the OR stops it from being a join (it possibly needs to return
> > some rows that are not in the semijoin of the two tables).
> >
> >> Why does it pick a sequential scan? Is this an optimizer bug
> >
> > No. It can't transform OR into a UNION because the results might not
> > be the same. I assume you don't care about removal of duplicates, or
> > have some reason to know that there won't be any ... but the planner
> > doesn't know that.
> >
>
> Thanks for the insight here. It still seems unfortunate that it picks a
> sequential scan -- but if there really is no more efficient way to do this,
> I will just rewrite the query.
>

It might not be applicable to this case (because of the use of ANY in
second branch of OR clause), but some databases provide a feature called
OR-Optimization, where the optimizer breaks up the query at OR clause
boundaries and uses UNION ALL operator to join the resulting queries, just
like you did. Optimizer does need to add additional AND clauses to some of
the branches to make sure the result set is not affected.

Just a thought.
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company