[sgarland]

Point taken re: gc.

For query plans, in nearly every plan flip I’ve encountered, the root cause came down to either poor table / column statistics (because they had let the table grown too large without appropriate tuning), having an enormous amount of joins beyond the deterministic level the planner could provide, or sub-optimal queries. The latter is growing less common as planners get better and better at rewriting on the fly, but it’s always good to know how to do things optimally.

A good example is semijoins / antijoins. With modern versions of both MySQL and Postgres, you’ll probably have “WHERE foo IN (…)” turned into “WHERE EXISTS (SELECT 1…)”, but it’s better to write it the optimal way in the first place.

I will grant you that indexing can be bewildering, with its many rules, caveats, and gotchas.