Re: hashed subplan 5000x slower than two sequential operations

2011/1/18 masterchief <esimon(at)theiqgroup(dot)com>

>
> > Tom Lane wrote:
> >
> > The only really effective way the planner knows to optimize an
> > "IN (sub-SELECT)" is to turn it into a semi-join, which is not possible
> > here because of the unrelated OR clause. You might consider replacing
> > this with a UNION of two scans of "contexts". (And yes, I know it'd be
> > nicer if the planner did that for you.)
>
> In moving our application from Oracle to Postgres, we've discovered that a
> large number of our reports fall into this category. If we rewrite them as
> a UNION of two scans, it would be quite a big undertaking. Is there a way
> to tell the planner explicitly to use a semi-join (I may not grasp the
> concepts here)? If not, would your advice be to hunker down and rewrite
> the
> queries?
>
>
You can try "exists" instead of "in". Postgresql likes exists better.
Alternatively, you can do something like "set enable_seqscan=false". Note
that such set is more like a hammer, so should be avoided. If it is the only
thing that helps, it can be set right before calling query and reset to
default afterwards.
--

Best regards,
Vitalii Tymchyshyn