Tom Lane wrote:
> David Link <dlink@soundscan.com> writes:
>
>> The following large EXPLAIN SELECT Statement fails to return, but
>> continues to take up processing time until it is killed.
>> [ 52-way join... ]
>>
>
> Am I right in guessing that all the sales_xxx tables are the same size
> and have similar statistics? I think the problem is that the planner is
> faced with a large set of essentially equivalent plans and isn't pruning
> the list aggressively enough. That's something we fixed in 8.0.
>
Correct.
>
>> Postgresql 7.4.8
>>
>
> You really oughta try something newer. On my machine, 7.4.12 plans a
> 52-way join in about a minute, and 8.0 and 8.1 in under a second.
>
We just completed our upgrade to 8.1.3. And we are happy campers! Our
Explain plan problem has gone away and everything runs faster. I
especially notice improved caching of repeated queries. Hats off to you
postgres folks. Thank you very much. Postgres rocks!
> I wonder also if there's not a better way to design the query...
> maybe a UNION ALL would work better than nested joins.
>
We need the info in separate columns. I don't think we can do it with
UNION. That's why the many joins.
I understand though with the new tablespace and inheritence features in
8/8.1 I could put all those sales tables back into one table and keep
the data in separate files.
> regards, tom lane
>
>
Thanks again for all your help.
David Link
Nielsen Entertainment, White Plains, NY