Reading a query plan is a senior skill nobody teaches you
Adding an index is guessing. Reading the plan is knowing. The gap between those two is most of my job.
Most performance advice about databases is a list of things to add. Add an index. Add caching. Add a read replica. All of that can help and all of it is guessing until you've looked at what the database is actually doing, which it will tell you if you ask.
Start with the shape, not the numbers
Before reading any cost estimate, look at the shape of the plan. You're looking for three things, roughly in this order:
- A scan where you expected a seek. Usually means no usable index, or a function wrapped around the column so the index can't be used.
- A row-count estimate that's wildly off the actual. If the planner thinks it's getting 12 rows and gets 1.2 million, every decision after that is wrong. Stale statistics, or a correlation the planner can't see.
- A sort or hash that spills to disk. Shows up as a memory warning. Often it's cheaper to let the index provide the ordering.
-- Always ANALYZE. A plan without actual rows is a hypothesis.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.id, p.reference, c.name
FROM policies p
JOIN claims c ON c.policy_id = p.id
WHERE p.renewed_at >= now() - interval '30 days'
ORDER BY p.renewed_at DESC
LIMIT 50;
-- rows=12 ... actual rows=1203401 <- this line is the bugThe index that did the most work
On Columbus the single biggest win was not a clever index. It was a covering index on the two columns in the WHERE plus the three the query selected, so the database never had to go back to the heap. One index, about a third of the total improvement.
The second biggest win was deleting four indexes. They were never chosen by the planner and every write had to maintain them.
The habit
Measure, change one thing, measure again, write the number down. It's slower than it sounds like it should be and it's the only method I've found that doesn't eventually make things worse.