2011/1/18 masterchief
<esimon@theiqgroup.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