27 / 32 · 5 min

Asking the database what it is doing

A query is a description of the answer, not instructions. The query planner decides how to get it, and when a query is slow the useful question is what it decided.

EXPLAIN

sql

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN shows the plan. ANALYZE runs it and shows what actually happened, which is the one you want, because estimates and reality diverging is itself the diagnosis.

code

Seq Scan on orders  (cost=0.00..18334.00 rows=1 width=44)
                    (actual time=0.02..142.8 rows=3 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 999997

Read it from the inside out. This one read a million rows and kept three. An index on customer_id turns it into:

code

Index Scan using orders_customer_id_idx on orders
                    (actual time=0.03..0.05 rows=3 loops=1)

What to look for

In the plan	Means
Seq Scan on a large table	no usable index, or the planner chose not to
Rows Removed by Filter, large	reading far more than it returns
estimated rows far from actual	statistics are stale, run ANALYZE
Nested Loop with a big inner side	often a join without an index
loops=N with N large	this runs once per outer row

A sequential scan is not automatically wrong. Reading a small table entirely is faster than an index lookup, and the planner knows this. It becomes wrong when the table is large and the result is small.

Why an index is ignored

Common and worth recognising:

sql

WHERE YEAR(order_date) = 2026       -- function on the column, index unusable
WHERE order_date >= '2026-01-01'    -- usable
    AND order_date <  '2027-01-01'

WHERE customer_id = '42'            -- type mismatch forces a cast

An index is on the column's values. Wrap the column in a function and the index no longer describes what is being compared.

The order of a composite index

An index on (customer_id, order_date) helps a query filtering on customer_id, or on both. It does not help one filtering on order_date alone, for the same reason a phone book sorted by surname does not help you find everyone called Priya.