On Jul 6, 2012, at 9:24 PM, Gurjeet Singh wrote: > On Thu, Jul 5, 2012 at 7:16 PM, Steven Schlansker <ste...@likeness.com> wrote: > > On Jul 5, 2012, at 3:51 PM, Tom Lane wrote: > > > Steven Schlansker <ste...@likeness.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 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
