Debugging slow SQL queries: an index primer

A slow query is almost always doing more work than it needs to, and EXPLAIN is the tool that tells you exactly where. Learning to read it is worth more than memorizing indexing rules, because the same rule ("add an index") is wrong about a third of the time.

Start with EXPLAIN ANALYZE, not EXPLAIN

EXPLAIN shows the planned query plan; EXPLAIN ANALYZE actually runs the query and shows real timings and row counts alongside the plan. The difference between planned and actual row counts is the single most useful number on the page:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';

Seq Scan on orders (cost=0.00..18334.00 rows=12 width=96)
                    (actual time=0.02..142.31 rows=8 loops=1)
  Filter: (customer_id = 42 AND status = 'pending')
  Rows Removed by Filter: 499992

That plan says: a sequential scan read 500,000 rows to find 8 matching ones. That's the signature of a missing index — the database has no faster way to find matching rows than reading the whole table.

The fix: a composite index matching the filter

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

Re-running EXPLAIN ANALYZE should now show an Index Scan or Bitmap Index Scan instead of Seq Scan, with actual time dropping from milliseconds-times-hundreds-of-thousands to sub-millisecond.

Column order in a composite index matters. An index on (customer_id, status) serves queries filtering on customer_id alone, or on customer_id and status together, but not on status alone — the index is a sorted structure keyed first by customer_id, so without that column pinned down, the database can't jump directly to the relevant status values. Put the column with the highest selectivity (most distinct values relative to row count), or the one always present in your queries, first.

Why the planner sometimes ignores your index anyway

An index existing doesn't guarantee it gets used. Common reasons a query still does a sequential scan despite a matching index:

• Low selectivity: if 60% of rows have status = 'pending', a sequential scan can be genuinely cheaper than jumping around via an index, since the index scan would still touch most of the table's pages. The planner is often right here.
• Function wrapping the column: WHERE LOWER(email) = 'x@y.com' can't use a plain index on email because the stored values and the search value are transformed differently. Either index the expression directly (CREATE INDEX ON users (LOWER(email))) or store a normalized column.
• Implicit type mismatch: comparing a text column to an integer literal, or a varchar column to a differently-collated literal, can silently disable index usage in some databases.
• Leading wildcard LIKE: WHERE name LIKE '%smith' can't use a standard B-tree index because the index is sorted by prefix; WHERE name LIKE 'smith%' can. Full-text search or trigram indexes (pg_trgm in Postgres) solve the leading-wildcard case.
• Stale statistics: after a large bulk load, the planner's row estimates can be badly wrong until ANALYZE runs. This shows up as a big gap between "rows=X" (estimated) and "actual rows=Y" in the plan.

Reading the shape of the plan, not just the leaf nodes

Plans are trees, read from the innermost/bottom operation outward. A few nodes worth recognizing:

• Nested Loop: fine for small row counts on one side, catastrophic when both sides are large — this is often the top of an accidentally cartesian-ish join.
• Hash Join: good for joining two moderately large sets; needs enough work_mem to build the hash table in memory, or it spills to disk and gets much slower.
• Sort: an explicit sort step for an ORDER BY that isn't satisfied by an index — if the same sort happens on every request, an index matching the ORDER BY columns can eliminate it entirely.

Covering indexes: avoiding the table lookup entirely

If a query only ever needs a handful of columns, an index that includes them all lets the database answer straight from the index without touching the table at all (an "index-only scan"):

CREATE INDEX idx_orders_lookup ON orders (customer_id, status) INCLUDE (total_amount, created_at);

This is a meaningful win for hot read paths — dashboards, list endpoints — where the same narrow set of columns is fetched repeatedly.

The indexing patterns that fix most real queries

• Index every foreign key column used in a JOIN — many ORMs don't do this automatically.
• Index columns that appear in WHERE, in that order of restrictiveness, for composite indexes.
• Index columns used in ORDER BY when the same sort runs frequently.
• Don't index columns with very low cardinality alone (a boolean is_deleted column indexed by itself rarely helps) — combine them with a more selective column instead, or use a partial index.
• Use a partial index for common filtered queries: CREATE INDEX ON orders (customer_id) WHERE status = 'pending' is smaller and faster than indexing the whole table when "pending" is a small slice.

The cost of over-indexing

Every index speeds up reads and slows down writes, since each INSERT/UPDATE/DELETE has to maintain every index on the table. A table with fifteen indexes "just in case" is often the actual cause of slow writes that gets misdiagnosed as "the database is slow" generally. Check pg_stat_user_indexes (or your database's equivalent) periodically for indexes with near-zero scans — they're pure write overhead and safe to drop.

A repeatable process

1. Run EXPLAIN ANALYZE on the slow query.
2. Find the node with the biggest gap between rows expected and rows actually processed, or the single largest actual-time contributor.
3. Check whether an existing index should have applied and didn't, or whether no index exists.
4. Add the narrowest index that would change that specific node's plan.
5. Re-run EXPLAIN ANALYZE and confirm the plan changed, not just that the query "feels faster."

Skipping step 5 is how databases end up with indexes nobody remembers adding, that never actually fixed anything.