Data Engineer · #049 · September 10, 2026 · 2 min read
How do you debug a slow SQL query?
Read EXPLAIN ANALYZE, spot the three query shapes that silently kill indexes, fix join order, and know what to do when indexes are not enough: the slow query playbook on one page.
Get the free PDF
One page, print-ready, free to share. No signup needed.
Your query is not slow. Your plan is. The database will tell you exactly what it did, in one command, and most 41-second queries are one rewrite away from 0.2s. One page. The print-ready A4 PDF is at the bottom.
First move
EXPLAIN ANALYZE: the real plan, with actual times.Seq Scanon a big table: red flag.rowsestimate far from actual: stale stats.
Usual suspects
-- 41s: function on the column, index ignored
WHERE DATE(created_at) = '2026-09-10'
-- 0.2s: same rows, index range scan
WHERE created_at >= '2026-09-10'
AND created_at < '2026-09-11'
SELECT *: drags every column through the plan.WHERE f(col) = x: kills the index. Rewrite around the column.LIKE '%foo': leading wildcard, no index for you.
Indexes
- Index the WHERE columns: filters first.
- Composite order matters: the left prefix rule.
- A covering index skips the table visit.
Joins
- Join on keys, indexed on both sides.
EXISTSoverINfor big subqueries.- Filter early: shrink, then join.
Quick wins
ANALYZE table: refresh statistics.LIMITwhile debugging: iterate fast.- Drop dead indexes: every write pays for them.
Still slow?
- Partition big fact tables: prune whole chunks.
- Pre-aggregate: a nightly rollup table.
- Cache the answer. The fastest query is no query.
Reading the plan
| In the plan | It means | The move |
|---|---|---|
| Seq Scan on 40M rows | no usable index for the filter | index the WHERE columns |
| rows=100, actual=2M | stats are stale, plan is blind | ANALYZE the table |
| Nested Loop on huge sets | join order went wrong | check keys and filters |
Fix the biggest node first. A 41s Seq Scan makes every other optimization a rounding error.
Frequently asked questions
How do you find out why a SQL query is slow?
Run it under EXPLAIN ANALYZE and read the actual plan, not the query text. A Seq Scan on a large table means no usable index for the filter. A huge gap between estimated rows and actual rows means the planner is working from stale statistics: run ANALYZE on the table. Fix the most expensive node first; everything else is a rounding error next to a 41-second scan.
Why does my query not use the index?
Usually because the query wraps the indexed column in a function: WHERE DATE(created_at) = '2026-09-10' cannot use an index on created_at, while a range predicate on the bare column can. Same story for LOWER(email), CAST(id AS TEXT), leading-wildcard LIKE '%foo', and OR chains. Rewrite around the column, not on it.
What order do columns go in a composite index?
The left prefix rule decides: an index on (a, b) serves filters on a, and on a AND b, but not on b alone. Put the most selective equality filters first, then range columns. A covering index that includes the selected columns lets the database skip the table visit entirely.
What if the query is still slow after indexing?
At some point the answer is architectural: partition big fact tables so queries prune whole chunks, pre-aggregate with a nightly rollup table, or cache the answer. The fastest query is the one you do not run.
Get the free PDF
One page, print-ready, free to share. No signup needed.