"Heikki Linnakangas" <heikki@enterprisedb.com> 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