Indexes That Get Used: PostgreSQL EXPLAIN Basics

An index that never gets used is extra write cost on every INSERT. An index that gets used is a filter, a join key, or an ORDER BY that no longer sorts. The only way to know which one you built is EXPLAIN. This post is the minimum planner literacy for PostgreSQL 16 on a single VPS: how to read the node types, why the planner ignores an index you just created, and how to write a btree that matches the query you actually run.

Work on a copy of production data when you can. Row counts change plans. A 200-row table will seq-scan even with a perfect index, and that is correct.

Read EXPLAIN ANALYZE, not EXPLAIN alone

EXPLAIN shows the plan. EXPLAIN ANALYZE runs the query and shows actual rows and time. Use ANALYZE on a replica or during a quiet window. Add BUFFERS when you care about cache hits versus High-Speed SSD reads.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, total_cents, created_at
FROM app.orders
WHERE customer_id = 4421
ORDER BY created_at DESC
LIMIT 20;

Look at the first node that reads a table. Seq Scan means the heap was read in order. Index Scan means the index found tuples, then the heap was fetched. Index Only Scan means the index and the visibility map answered without a heap fetch. Bitmap Heap Scan plus Bitmap Index Scan is the usual plan for a filter that matches many rows.

actual rows versus plan rows is the first lie detector. If the planner expected 12 rows and saw 180000, statistics are stale or the predicate is not selective the way ANALYZE thinks. Run ANALYZE app.orders and try again before you add another index.

Build a btree that matches the WHERE and ORDER BY

PostgreSQL's default index is btree. It serves equality, range, and ORDER BY in the index order. For the query above, a btree on (customer_id, created_at DESC) can filter and return the 20 rows without a sort.

CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON app.orders (customer_id, created_at DESC);
ANALYZE app.orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM app.orders
WHERE customer_id = 4421
ORDER BY created_at DESC
LIMIT 20;

CONCURRENTLY avoids a write lock on a live table. It cannot run inside a transaction block. After it finishes, the plan should show Index Scan or Index Only Scan using orders_customer_created_idx, with no Sort node. If a Sort remains, the index column order does not match ORDER BY, or a function wraps created_at.

Leftmost prefix matters. (customer_id, created_at) can serve WHERE customer_id = 4421. It cannot serve WHERE created_at > now() - interval '1 day' by itself. Put the equality columns first, then the range or sort columns.

Why the planner ignores the index

Low selectivity. If customer_id has three values, a seq scan is cheaper. Check that with n_distinct.

SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE schemaname = 'app' AND tablename = 'orders';
SELECT indexrelid::regclass, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relid = 'app.orders'::regclass;

idx_scan of zero after a week means the index is dead weight. Drop it. correlation near 1.0 means the heap is already ordered on that column, which is why a seq scan plus limit can beat an index on a time-ordered table.

Functions on the column hide the index. WHERE lower(email) = 'a@b.com' will not use a btree on email. Use a btree on (lower(email)) or store a normalized column. CAST, AT TIME ZONE, and wrapping a column in COALESCE have the same effect.

Random page cost still matters. On High-Speed SSD, random_page_cost of 1.1 is closer to reality than 4.0. If EXPLAIN shows a seq scan that ANALYZE then proves slower than a hinted index scan, lower random_page_cost slightly. Do not set it to 0.1. The planner will start preferring nested loops that explode.

Partial indexes are the right tool for a hot subset. WHERE status = 'open' on a table that is 95 percent closed is a small index that gets used. A partial unique index also enforces uniqueness only where it matters.

Covering indexes with INCLUDE let Index Only Scan return extra columns without putting them in the sort key. INCLUDE (total_cents) on (customer_id, created_at DESC) is how you make the LIMIT 20 query an index-only read once the visibility map is current. VACUUM has to visit the table for that map to stay set.

One query, one index, one EXPLAIN before and after. Do not create five indexes because a dashboard was slow. The writes still go through every btree on that table, on the same High-Speed SSD as the heap.

Takeaway

EXPLAIN ANALYZE, then a btree whose leftmost columns match the filter and whose trailing columns match ORDER BY. If the plan ignores it, the stats, the wrapping function, or the selectivity is the reason. Spin up a Lucknow DC01 Ubuntu VPS on Netbay in under 60 seconds and practice these plans on a restored copy — netbayhosts.in.