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

List:       pgsql-performance
Subject:    Re: [PERFORM] Cross Join Problem
From:       "Gauri Kanekar" <meetgaurikanekar () gmail ! com>
Date:       2008-08-19 13:46:17
Message-ID: 7e4ba9550808190634q123274ffgca0f7a26df822ee9 () mail ! gmail ! com
[Download RAW message or body]

Thanx alot... its solved my problem

On Mon, Aug 18, 2008 at 8:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> [ please keep the list cc'd for the archives' sake ]
>
> "Gauri Kanekar" <meetgaurikanekar@gmail.com> writes:
> > On Mon, Aug 18, 2008 at 7:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> No PG release since 7.3 would have voluntarily planned that query that
> >> way.  Maybe you were using join_collapse_limit = 1 to force the join
> >> order?
>
> > Yes, We have set join_collapse_limit set to 1.
>
> Ah, so really your question is why join_collapse_limit isn't working as
> you expect.  That code changed quite a bit in 8.2, and the way it works
> now is that the critical decision occurs while deciding whether to fold
> the cross-join (a sub-problem of size 2) into the top-level join
> problem.  Which is a decision that's going to be driven by
> from_collapse_limit not join_collapse_limit.
>
> So one way you could make it work is to reduce from_collapse_limit to
> less than 3, but I suspect you'd find that that has too many bad
> consequences for other queries.  What's probably best is to write the
> problem query like this:
>
>        FROM table1 a cross join ( table2 b cross join table3 c )
>
> which will cause join_collapse_limit to be the relevant number at both
> steps.
>
>                        regards, tom lane
>



-- 
Regards
Gauri

[Attachment #3 (text/html)]

<div dir="ltr">Thanx alot... its solved my problem<br><br><div class="gmail_quote">On \
Mon, Aug 18, 2008 at 8:50 PM, Tom Lane <span dir="ltr">&lt;<a \
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt;</span> wrote:<br> \
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); \
margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">[ please keep the list cc&#39;d for \
the archives&#39; sake ]<br> <div class="Ih2E3d"><br>
&quot;Gauri Kanekar&quot; &lt;<a \
href="mailto:meetgaurikanekar@gmail.com">meetgaurikanekar@gmail.com</a>&gt; \
writes:<br> &gt; On Mon, Aug 18, 2008 at 7:32 PM, Tom Lane &lt;<a \
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br> </div><div \
class="Ih2E3d">&gt;&gt; No PG release since 7.3 would have voluntarily planned that \
query that<br> &gt;&gt; way. &nbsp;Maybe you were using join_collapse_limit = 1 to \
force the join<br> &gt;&gt; order?<br>
<br>
</div><div class="Ih2E3d">&gt; Yes, We have set join_collapse_limit set to 1.<br>
<br>
</div>Ah, so really your question is why join_collapse_limit isn&#39;t working as<br>
you expect. &nbsp;That code changed quite a bit in 8.2, and the way it works<br>
now is that the critical decision occurs while deciding whether to fold<br>
the cross-join (a sub-problem of size 2) into the top-level join<br>
problem. &nbsp;Which is a decision that&#39;s going to be driven by<br>
from_collapse_limit not join_collapse_limit.<br>
<br>
So one way you could make it work is to reduce from_collapse_limit to<br>
less than 3, but I suspect you&#39;d find that that has too many bad<br>
consequences for other queries. &nbsp;What&#39;s probably best is to write the<br>
problem query like this:<br>
<br>
 &nbsp; &nbsp; &nbsp; &nbsp;FROM table1 a cross join ( table2 b cross join table3 c \
)<br> <br>
which will cause join_collapse_limit to be the relevant number at both<br>
steps.<br>
<br>
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; \
&nbsp;regards, tom lane<br> </blockquote></div><br><br clear="all"><br>-- \
<br>Regards<br>Gauri<br> </div>



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