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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
