[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] hashed subplan 5000x slower than two sequential operations
From:       <tivv00 () gmail ! com>
Date:       2011-01-18 21:29:40
Message-ID: AANLkTik4apwA1Dwbp+PYoz7OOiYY6TDniC+ECqaCwBHt () mail ! gmail ! com
[Download RAW message or body]

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

[Attachment #3 (text/html)]

<br><br><div class="gmail_quote">2011/1/18 masterchief <span dir="ltr">&lt;<a \
href="mailto:esimon@theiqgroup.com">esimon@theiqgroup.com</a>&gt;</span><br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex;"> <div class="im"><br>
&gt; Tom Lane wrote:<br>
&gt;<br>
&gt; The only really effective way the planner knows to optimize an<br>
&gt; &quot;IN (sub-SELECT)&quot; is to turn it into a semi-join, which is not \
possible<br> &gt; here because of the unrelated OR clause.  You might consider \
replacing<br> &gt; this with a UNION of two scans of &quot;contexts&quot;.  (And yes, \
I know it&#39;d be<br> &gt; nicer if the planner did that for you.)<br>
<br>
</div>In moving our application from Oracle to Postgres, we&#39;ve discovered that \
a<br> large number of our reports fall into this category.  If we rewrite them as<br>
a UNION of two scans, it would be quite a big undertaking.  Is there a way<br>
to tell the planner explicitly to use a semi-join (I may not grasp the<br>
concepts here)?  If not, would your advice be to hunker down and rewrite the<br>
queries?<br><br></blockquote><div> </div><div> You can try &quot;exists&quot; instead \
of &quot;in&quot;. Postgresql likes exists better. </div><div>Alternatively, you can \
do something like &quot;set enable_seqscan=false&quot;. 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.</div> \
<div>--</div><div><br></div></div>Best regards,<br> Vitalii Tymchyshyn<br>



[prev in list] [next in list] [prev in thread] [next in thread]