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@benjamindsmith.com> 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