"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Have you played with enable_seqscan=off or enable_hashjoin=off? That's > not a good long term solution, but it would be interesting to see what > happens.
I think this is a case where Postgres just doesn't know it can re-order near an outer join. Outer joins often can't be re-ordered and Postgres isn't a general theorem prover, it can't always figure out whether it's safe to re-order them. The structure of your query is a whole series of left outer joins, the result of which is then (inner) joined with one more table. The outer joins return a whole lot of records but the inner join is only going to match a few of them. The only hope you have of a reasonable plan here is if Postgres can figure out that it can do the inner join first so that it only has to perform the outer join on the resulting records. I think it could actually re-order the inner query to happen first in this case. But I'm not certain, it's tricky to tell. But the fact that Oracle finds a way to execute it quickly gives me some confidence that it ought to be possible since I think Oracle does get join orderings reasonably right. I'm not so sure about Informix. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
