Tom Lane wrote:
David Link <dlink@xxxxxxxxxxxxx> 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
