Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4A57864FCE6 for ; Tue, 19 Aug 2008 10:34:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 24797-09 for ; Tue, 19 Aug 2008 10:34:22 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.233]) by postgresql.org (Postfix) with ESMTP id 1DA7764FCE1 for ; Tue, 19 Aug 2008 10:34:24 -0300 (ADT) Received: by rv-out-0506.google.com with SMTP id b25so4015074rvf.43 for ; Tue, 19 Aug 2008 06:34:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=Me0D3/JPZrZMqRieXUeQLVaxheDYA9Ot5LwPAmzAJGE=; b=YlBAqC9dMFU0xeLeEd4wJYS15C3a9CBmVimU0rlNvpy5NplM6YALHiqOTHQhCAxiJA K1H0quyeT6/a/b5kJMjWmOl6EXKsRh1RRL5RSpgqZg8MBgll42Xw0sGLV4oOliv5Bq2O F7aTO4ZEljiYPUX0bX0BGikdEWUaxHPdM9LGk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=f71MMediD1F4UcKzHN+EN3dwdgCDfflSqk2D+cViDgDHcSTQLgvrHNRjGqPEeaRlFM bD3RJWshfRr/Ztt7wR6n9+KS9/GE9I0h8FNffGHfQUcuV6d17xdgwfqaAJsrRm8Ao5Lk yfcufffHjMCVMk3xG3jCkAvEHjEESusRr9PJk= Received: by 10.141.123.4 with SMTP id a4mr4050454rvn.172.1219152857310; Tue, 19 Aug 2008 06:34:17 -0700 (PDT) Received: by 10.140.127.10 with HTTP; Tue, 19 Aug 2008 06:34:17 -0700 (PDT) Message-ID: <7e4ba9550808190634q123274ffgca0f7a26df822ee9@mail.gmail.com> Date: Tue, 19 Aug 2008 19:04:17 +0530 From: "Gauri Kanekar" To: "Tom Lane" Subject: Re: Cross Join Problem Cc: pgsql-performance@postgresql.org In-Reply-To: <28902.1219072800@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6982_26608731.1219152857310" References: <7e4ba9550808180637v382ce149sf0577849b03d955@mail.gmail.com> <18370.1219068149@sss.pgh.pa.us> <7e4ba9550808180706l4f595729h102cbc2bb3e1bfa@mail.gmail.com> <28902.1219072800@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/204 X-Sequence-Number: 31085 ------=_Part_6982_26608731.1219152857310 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Thanx alot... its solved my problem On Mon, Aug 18, 2008 at 8:50 PM, Tom Lane wrote: > [ please keep the list cc'd for the archives' sake ] > > "Gauri Kanekar" writes: > > On Mon, Aug 18, 2008 at 7:32 PM, Tom Lane 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 ------=_Part_6982_26608731.1219152857310 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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
------=_Part_6982_26608731.1219152857310--