Wow! I did exactly what you suggested, and played with the ordering for about 20-30 minutes. After 10 minutes or so, I'd gotten the response time down to 1700 ms from 2200 ms. Moving the join conditions up into the "FROM" clause, and dropping the "WHERE" clause altogether allowed me to reorder the statements easily without having to worry about rethinking all the logic. And, this dropped the query time from between 2.2-30 seconds all the way down to just 55-ish ms, without any new indexes! What's more, the improvement came from a move of a block I thought more or less unimportant! // tries to put jaw back into mouth // -Ben On Monday 12 December 2005 16:11, you wrote: > Benjamin Smith <lists@xxxxxxxxxxxxxxxxxx> writes: > > The example that I gave was a small one to illustrate my understanding of > > multiple foreign keys, indexes and how they work together. (or don't) The > > actual query is quite a bit bigger and nastier. I've considered breaking it > > up into smaller pieces, but this query has been extensively tested and > > debugged. It's on a complex schema carefully designed to meet some very > > demanding requirements. > > What you probably need to do is rethink the join order. As coded, the > planner has no freedom to change the join order, which means it's up to > you to get it right. In particular it seems a bad idea to be processing > the join to enrollments last when that table is the best-constrained > one. Instead of "enrollments, stmoduleobjass LEFT JOIN lots-o-stuff" > consider "enrollments JOIN stmoduleobjass ON relevant-join-conditions > LEFT JOIN lots-o-stuff". Likewise for lcregistrations vs lcclasses. > > regards, tom lane > -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
