When a query is slow, the worst response is to optimize by instinct.
PostgreSQL already tells you how it executed the query. You just need to ask.
That is what EXPLAIN ANALYZE is for.
Start With the Plan, Not the Theory
EXPLAIN ANALYZE
SELECT *
FROM users
WHERE status = 'archived';
This gives you the actual execution plan, not just the SQL text you hoped would be efficient.
The First Things to Look For
You do not need to master every plan node on day one. Start with:
- scan type: sequential scan vs index-backed access
- estimated rows vs actual rows
- total execution time
- whether expensive steps are repeated many times
These are often enough to explain most "why is this slow?" incidents.
A Sequential Scan Is Not Automatically Bad
One of the most common beginner mistakes is assuming every Seq Scan is a bug.
It is not.
If the table is small or the query needs a large percentage of the rows anyway, a sequential scan can be the right plan.
The real question is whether the chosen plan matches the workload.
What an Index Often Changes
If a selective filter lacks a useful index, PostgreSQL may have no cheap way to narrow the search.
Adding an index can change that:
CREATE INDEX idx_users_status ON users(status);
But even then, do not stop at "index added". Re-run EXPLAIN ANALYZE and confirm the new plan is actually better.
The Real Skill
Reading execution plans is less about memorizing node names and more about asking:
- how many rows did PostgreSQL think it would touch?
- how many did it actually touch?
- where is time being spent?
- is the plan doing repeated work?
That is the path from folklore to engineering.
Further Reading