From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Gauri Kanekar" <meetgaurikanekar(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgreSQL(dot)org |
Subject: | Re: Cross Join Problem |
Date: | 2008-08-18 15:20:00 |
Message-ID: | 28902.1219072800@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
[ please keep the list cc'd for the archives' sake ]
"Gauri Kanekar" <meetgaurikanekar(at)gmail(dot)com> writes:
> On Mon, Aug 18, 2008 at 7:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Wong | 2008-08-18 15:33:44 | Re: file system and raid performance |
Previous Message | Matthew Wakeling | 2008-08-18 14:57:22 | Re: Slow query with a lot of data |