Re: left outer join terrible slow compared to inner join

Greg Stark <gsstark@mit.edu> writes:
> Now, uh, there are 37 tables involved in this query. That's kind of a lot.

> 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.

Of course, we don't do that ... long before you get to 37 tables, the
optimizer gives up on exhaustive search and goes for approximate
answers.  So part of the problem might be that the partial GEQO plan
search isn't finding an especially good plan.  But it looked to me like
this is basically a star schema and the exact order in which we join the
detail tables isn't much of an issue.

I thought the problem is more likely to be that 7.3 isn't doing enough
analysis of when it can simplify outer joins --- there are conditions
in the join steps of Clay's example that really should have been pushed
down to the scan steps.  We'll have to await his report of whether 7.4
does better.

            regards, tom lane