Starship
Plan

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.

8 reactions

One project a week, in your inbox

What was built, the numbers it produced, and what did not work. No other email, and one click to leave.