Re: Suboptimal query plan fixed by replacing OR with UNION

On Jul 6, 2012, at 9:24 PM, Gurjeet Singh wrote:

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

That sounds like a great optimization for Postgres, but unfortunately it's far outside of my skill set / time to contribute, so I'd have to wait for a "real" PG dev to get to it :)

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