EXPLAIN: how to read a query plan and find slow queries

When a query is slow, guessing wastes time. EXPLAIN shows the query plan: the steps the database chooses to run your SQL. Reading it tells you where the time goes.

Run it

EXPLAIN SELECT * FROM articles WHERE author_id = 5 ORDER BY created_at DESC LIMIT 10;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;   -- PostgreSQL: really runs it and shows actual time

Other databases: MySQL uses EXPLAIN (the type column, where ALL means a full scan), SQLite uses EXPLAIN QUERY PLAN.

Careful: EXPLAIN ANALYZE actually executes the query. For UPDATE or DELETE, wrap it in a transaction and roll back.

The main scan types

• Sequential (full) scan: reads the whole table. Fine for small tables or when most rows are needed, bad for a selective filter on a big table.
• Index scan: uses an index to find rows, then reads the table.
• Index-only scan: answers from the index alone (a covering index), the fastest.
• Bitmap scan: combines several index results, common when many rows match.

Join methods

• Nested loop: for each row on one side, look up matches on the other. Great with an index and few rows.
• Hash join: builds a hash table of one side. Good for large, unsorted inputs.
• Merge join: joins two sorted inputs.

A nested loop over a huge table without an index is a classic cause of slowness.

What to look at

1. Cost (estimated, in arbitrary units). Compare the plans, not the absolute number.
2. Estimated rows vs actual rows. A big difference means the planner is working from wrong statistics. Run ANALYZE, and consider extended statistics.
3. Actual time of each node: find the slowest one.
4. Rows removed by filter: if a node reads a million rows and keeps ten, you need a better index.
5. Sort and temp files: "external merge" on disk means the sort did not fit in memory.

A typical fix

Plan shows a sequential scan on articles with Filter: author_id = 5, removing 999,000 rows. Add an index, run the query again and confirm an index scan:

CREATE INDEX idx_articles_author_created ON articles (author_id, created_at DESC);

Now the filter and the sort both use the index.

Habits

• Check plans on realistic data volumes. A small test database hides problems.
• Turn on the slow query log (or pg_stat_statements) to find the worst queries by total time.
• Change one thing at a time and measure again.
• Remember that the best plan can change as data grows.

Takeaway: measure with EXPLAIN, compare estimated and actual rows, look for full scans and big sorts, then fix with indexes or by rewriting the query.

---

Backend & Database series, Part 2 (performance and operations): 1 of 6.