Re: left outer join terrible slow compared to inner join

"Clay Luther" <claycle(at)cisco(dot)com> writes:

> Interstingly enough, the EXPLAIN ANALYZE itself took 90+ seconds:

"explain" would produce just the plan, normally in a few ms. This query might
take a while though.

"explain analyze" says to produce the plan and then actually run the query and
annotate the plan with the actual timing results at each node. Note the
"actual time" labels on each row. So it's not suprising that it took 90s.

Now, uh, there are 37 tables involved in this query. That's kind of a lot.
Like, really, a lot. It's possible this is a sane, if extremely normalized
design, but well, still. 37 is a big number.

Postgres has to consider 37 factorial different ways of combining these
tables. or about 13,763,750,000,000,000,000,000,000,000,000,000,000,000,000
different combinations. That makes it harder for it to come up with the best
combination. You might consider rewriting it to use the ANSI join syntax "LEFT
JOIN" and "RIGHT JOIN" if you haven't already. That might help it out.

That said. My first guess as to the real problem. Of the 37 tables 36 of them
aren't being accessed using indexes. Do you have indexes on the join columns?
Perhaps you should? Postgres performs better when it has indexes.

I'm a bit puzzled how one could get up to 37 tables in a single query other
than just having taken normalization a bit too far. But if that was the
thinking then I would expect the joins to be on the primary keys of all the
tables, which would presumably have indexes. So, well, I guess I'll just stay
puzzled.

--
greg