A query that got 40× faster, and the index had nothing to do with it
What a visual query plan showed me that four hours of reading EXPLAIN did not.
A dashboard query took 3.1 seconds on about forty thousand rows. That is absurd and I knew it was absurd.
I read the EXPLAIN ANALYZE output for most of an evening. It is a tree printed as indented text, and past about fifteen nodes I lose the ability to hold it in my head.
So I spent a weekend rendering it as boxes, sized by actual time.
What was immediately obvious
One node was 94% of the runtime. A nested loop, running a subquery once per row, because the subquery referenced the outer query and the planner could not pull it out.
In the text output that node is one line among forty-one, and its cost is a number I had read four times without registering.
The fix
Rewriting the correlated subquery as a lateral join. 3.1 seconds to 76 milliseconds.
No index was added. I had spent the previous day adding two, which did nothing, because the problem was never the lookup.
I could have found this by reading more carefully. I did not, four times. A tool that makes it impossible to miss is worth more than the intention to read carefully.