Tags: web-dev concept

Query Planning

Date: 2026-08-17

---

The database decides how to execute your query, and reading that decision is the only reliable way to know why one is slow. Guessing at the cause of a slow query is the most common waste of time in backend work.

---

The query planner takes a declarative query, enumerates ways of executing it, estimates the cost of each using statistics about the data, and picks one. The execution plan is that choice, and every database can show it to you.

EXPLAIN SELECT ...          -- the plan
EXPLAIN ANALYZE SELECT ...  -- the plan, plus
                            -- actual timings

Use ANALYZE — it runs the query and reports what really happened, which is the only way to see where estimates were wrong.

What a plan is made of

Read it as a tree, innermost first:

Hash Join  (cost=… rows=200)
  ├─ Seq Scan on orders  (rows=100000)
  │    Filter: total > 50
  └─ Hash
       └─ Index Scan using customers_pkey
            (rows=200)

Seq Scan        read every row
Index Scan      walk the index, fetch rows
Index Only Scan the index had everything
Bitmap Scan     many index hits, batched
Nested Loop     for each outer row, probe inner
Hash Join       build a hash of one side
Merge Join      both sides sorted, zip them
Sort            explicit sort — memory or disk
Aggregate       GROUP BY, COUNT

The three numbers that matter

1  ESTIMATED vs ACTUAL rows
     est 200, actual 500,000
     → statistics are stale or the
       predicate is unusual
     → the planner chose badly BECAUSE
       it was misinformed

2  Seq Scan on a large table
     with a selective filter
     → a missing or unusable index

3  Sort or Hash spilling to disk
     "external merge Disk: 45MB"
     → raise work memory, or index to
       avoid the sort

The first is the highest-value diagnostic. A planner picking a bad strategy is almost always a planner with wrong estimates, and the fix is to refresh statistics rather than to rewrite the query.

Why a sequential scan is sometimes right

SELECT * FROM orders WHERE status = 'paid';
-- 95% of rows are 'paid'

Using an index here would be slower. The database would walk the index and then fetch nearly every row individually, in index order, which is worse than reading the table in physical order. The planner is correct to ignore the index.

This is why “it’s not using my index” is often not a bug. Check selectivity before assuming — Indexing.

Statistics, and why they go stale

The planner estimates from sampled statistics — row counts, distinct values, distributions. After a bulk load, a large delete, or a big data shift, those become wrong and the plans follow.

ANALYZE table_name;  -- refresh statistics

A query that was fast for months and is suddenly slow, with no code change, is usually stale statistics or a plan flip caused by data growth crossing a threshold.

Common causes of slow queries, in order

1  no index on the filter/join column
2  a function on an indexed column
3  N+1 — many small queries, not one
     slow one
4  SELECT * pulling columns you discard
5  OFFSET pagination deep into a table
     (OFFSET 100000 reads and discards
      100,000 rows) — use keyset pagination
6  an unbounded query with no LIMIT
7  stale statistics

See: N+1 Queries · Query Performance for the rewrites

Number 3 is the one that never appears in a slow-query log, because each individual query is fast. It shows up as a slow page.

Reading it as a non-DBA

You don’t need to tune a planner. You need to answer three questions:

Is it scanning something large?     → index it
Are estimates wildly off?           → ANALYZE
Is it sorting or spilling to disk?  → index the
                                      ORDER BY

That covers most of what a web developer will ever encounter, and it’s the difference between a fix and a guess.

Where the abstraction leaks

• Planners differ. Postgres, MySQL and SQLite make different choices, and advice doesn’t transfer cleanly
• Prepared statements can produce generic plans that are worse than one tailored to the parameters
• ORMs generate queries you didn’t write. Log the actual SQL before concluding anything — the query you’re debugging may not be the query you think — N+1 Queries
• Warehouses plan differently, with columnar storage and partition pruning rather than row indexes. EXPLAIN is still the tool; the vocabulary changes