Re: Performance woes

Benjamin Smith <lists(at)benjamindsmith(dot)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