Why I stopped writing clever SQL
A few years ago I rewrote a reporting query as a single statement with three CTEs, a window function and a lateral join. It went from about four seconds to under two. I was pleased with it. It sat untouched for two years because nobody, including me, could work out what it did any more.
The cost is not where you think
The query was not wrong and it was not slow. The cost showed up somewhere else entirely: every request to change the report turned into a half-day archaeology exercise, and eventually into "can we just add a second report instead". The clever query bought two seconds and spent them at a rate of several hours a quarter.
What I do instead
Write the obvious version first and measure it against realistic data volumes, not the dev database with four thousand rows in it. Most of the time the obvious version is fine. When it is not, make one change at a time and leave a comment explaining what the plan looked like before and after:
-- Without the partial index below this does a seq scan
-- over ~9M rows (see plan in PR #482). Do not drop it.The comment is the important half. An optimisation with no record of what it was avoiding is indistinguishable from superstition, and the next person will either remove it or, worse, copy it somewhere it does nothing.
The exception
Genuinely hot paths earn genuine complexity. The distinction is whether anyone is waiting on the result. A nightly report is not a hot path, no matter how satisfying it is to optimise.