Re: left outer join terrible slow compared to inner join

Clay Luther wrote:

> Heh...well, first let me say:
>
> 1) Our database is highly normalized.

Excellent. When faced with the choice of ensuring integrity myself in
the face of redundancy vs. Tom Lane's ability to improve the planner,
optimizer, and executor, I always vote for the latter!

> 2) All joins in the query are performed across indeces.
> 3) It IS a huge query.
>
> There is a reason to the madness. Namely, this query was driven by a client application requirement. In a particular operational case, the application needed to be able to say "give me all this information now!" without making round trips to the database. The query itself has grown over the years (last time I looked at it, it was only 24 joins, not 37). But, as I said before,
>
> 1) It works
> 2) It works VERY fast (in SQLServer)
> 3) It works in production and has for years now

I have faced these issues before in older versions of PostgreSQL:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=67a713f0107dc77a&seekm=01C0CF88.292AB320.mascarm%40mascari.com#link1

I'd suggest a few things:

1) How long does it take to execute just a plain EXPLAIN? I suspect it
might be spending more time planning than actually executing

2) You might be able to play around with explicit join syntax in part
of your queries:

http://www.postgresql.org/docs/7.3/static/explicit-joins.html

3) I've found the GEQO threshold to be way too low:

http://www.postgresql.org/docs/7.3/static/runtime-config.html#RUNTIME-CONFIG-OPTIMIZER

4) If you have any UDF's used in the WHERE clause, attempt to rewrite
the query without them or use #2 to defer their evaluation if they are
costly. I've found that PostgreSQL, when left to its own devices, can
often choose to evaluate a UDF before a join, where the join would
have been far less costly to evaluate first.

I haven't tried 7.4beta though. It may solve all your problems and
answer all your questions. For me, each release has reduced planning
time by an order of magnitude. Hopefully, that trend will continue ad
infinitum. :-)

Mike Mascari
mascarm(at)mascari(dot)com