Re: EXPLAIN SELECT .. does not return

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