The first time you run EXPLAIN ANALYZE on a slow query, the output looks like a wall of parentheses. It is not. It is a tree, and there are only a handful of things you need to read.
Read it inside out
The plan is a tree of nodes, printed with the outermost operation at the top. Execution happens the other way around: the most indented lines run first, and results flow upward. Start at the deepest indentation and read up.
The four numbers that matter
Each node reports cost, rows, actual time, and loops. The one to watch is the gap between estimated rows and actual rows returned. When the planner estimates 10 rows and gets 400,000, every decision it made above that node was based on a bad assumption — and that is usually your real problem, not the node that looks slow.
Sequential scan is not always wrong
A sequential scan on a small table is correct and fast. Postgres chooses it deliberately when the table is small enough or when it expects to return most of the rows anyway. Adding an index there will not help and will slow your writes down.
The sequential scan you care about is the one on a large table inside a nested loop, running thousands of times. Look at loops.
The usual culprits
- A function on the indexed column.
WHERE lower(email) = $1cannot use a plain index onemail. Index the expression, or store the normalised value. - A type mismatch. Comparing a
bigintcolumn to an integer parameter can prevent index use depending on how the driver sends it. - Missing statistics. After a bulk load, run
ANALYZE. The planner is only as good as its idea of your data distribution. - OR conditions across columns. These often defeat a single composite index. Sometimes two queries and a UNION are genuinely faster.
The habit worth building
Do not optimise from intuition. Run the query with EXPLAIN (ANALYZE, BUFFERS), change one thing, and run it again. Keep the before and after. Most of the time the fix is one index or one rewritten predicate, and you will only find which one by measuring.
Want this kind of thinking on your project?
We scope, price, and build software for Nigerian businesses — and train the engineers who maintain it.