To: "Gauri Kanekar" <meetgaurikanekar@gmail.com>
cc: pgsql-performance@postgreSQL.org
Subject: Re: Cross Join Problem 
In-reply-to: <7e4ba9550808180706l4f595729h102cbc2bb3e1bfa@mail.gmail.com> 
References: <7e4ba9550808180637v382ce149sf0577849b03d955@mail.gmail.com>
	<18370.1219068149@sss.pgh.pa.us>
	<7e4ba9550808180706l4f595729h102cbc2bb3e1bfa@mail.gmail.com>
Comments: In-reply-to "Gauri Kanekar" <meetgaurikanekar@gmail.com>
	message dated "Mon, 18 Aug 2008 19:36:21 +0530"
Date: Mon, 18 Aug 2008 11:20:00 -0400
Message-ID: <28902.1219072800@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

[ 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