Indexes and EXPLAIN

Description

The order history page was instant at launch and takes three seconds now. The table grew from ten thousand orders to ten million, and the query hasn't changed: it reads every order to find one customer's twenty. An index turns that into a handful of page reads - but only the right index, and only if the query is written so Postgres can use it.

This is the skill behind most "the app got slow" tickets, and one of the most-asked topics in backend interviews. You'll learn to pick an index and then check your choice with EXPLAIN, Postgres's own description of how it will run a query. From simple to more technical:

1. What an index is, and how to read a plan.
2. Composite indexes, and why column order matters.
3. Writing filters an index can actually use.
4. Why Postgres sometimes ignores an index on purpose.
5. Partial and covering indexes; clustered versus non-clustered.
6. Fast pagination with a keyset instead of OFFSET.

The examples use 2,000 customers and 20,000 orders (10 orders per customer; 1% pending, 9% canceled, the rest delivered), analyzed, so every plan below is the one PostgreSQL 18 picked for real.

What an index does

A B-tree index keeps a column's values sorted, each with a pointer to its row. Postgres can jump to a value (=), walk a range (<, BETWEEN, LIKE 'abc%' with a suitable collation), and read rows in index order - which can replace an ORDER BY. Inside, it's a shallow tree of pages: a root page points to internal pages, which point to leaf pages holding the sorted keys - and each leaf entry points to where its row lives in the table:

Without one, finding a customer's orders means reading the whole table:

EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders
  Filter: (customer_id = 42)

After CREATE INDEX orders_customer_idx ON orders (customer_id);:

Bitmap Heap Scan on orders
  Recheck Cond: (customer_id = 42)
  ->  Bitmap Index Scan on orders_customer_idx
        Index Cond: (customer_id = 42)

The plan's scan nodes tell you how each table is read:

Here the customer's 10 orders sit on 10 different pages, so Postgres gathers them in a bitmap and reads each page once.

Reading EXPLAIN

EXPLAIN shows the plan Postgres would use, with estimated costs and row counts (COSTS OFF hides them, as here). EXPLAIN ANALYZE runs the statement and adds the actual rows, loops and time per node - and since Postgres 18 it also reports buffers (pages read from cache or disk) by default. Because it runs the statement, EXPLAIN ANALYZE on an UPDATE or DELETE really changes data: wrap it in BEGIN ... ROLLBACK.

What to look for:

• The scan on each big table - a Seq Scan with a selective Filter usually means a missing or unusable index.
• Index Cond vs Filter - an Index Cond narrows what the index reads; a Filter throws rows away after reading them.
• Sort nodes above a Limit - the index doesn't deliver the order you asked for.
• Estimated vs actual rows (with ANALYZE) far apart - stale or missing statistics; run ANALYZE.

Composite indexes: equality first, then order

The history page wants one customer's orders, newest first:

SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 5;

With only the customer_id index, Postgres finds the customer's orders, then sorts them:

Limit
  ->  Sort
        Sort Key: created_at DESC
        ->  Bitmap Heap Scan on orders
              Recheck Cond: (customer_id = 42)
              ->  Bitmap Index Scan on orders_customer_idx
                    Index Cond: (customer_id = 42)

With CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);, a customer's orders sit together already sorted by date, so Postgres reads the index backward and stops after five - no sort, however many orders the customer has:

Limit
  ->  Index Scan Backward using orders_customer_created_idx on orders
        Index Cond: (customer_id = 42)

The rule for column order: columns compared with = first, then the one you range over or sort by. An index on (created_at, customer_id) would scatter one customer's orders across the whole index. Traditionally a B-tree was only useful when the query constrained its leading column. Postgres 18 adds skip scan: when the leading column has few distinct values, it can jump through each of them. Here an index on (status, created_at) - three statuses - serves a query that only filters on created_at:

CREATE INDEX orders_status_created_idx ON orders (status, created_at);
EXPLAIN (COSTS OFF)
SELECT id FROM orders
WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02';

Bitmap Heap Scan on orders
  Recheck Cond: ((created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-03-02 00:00:00+00'::timestamp with time zone))
  ->  Bitmap Index Scan on orders_status_created_idx
        Index Cond: ((created_at >= '2026-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-03-02 00:00:00+00'::timestamp with time zone))

Skip scan is a bonus for few-valued leading columns, not a reason to ignore column order - with thousands of distinct leading values it would mean thousands of jumps.

Write predicates the index can use

An index on a column serves conditions on the column itself. Wrap the column in a function or a cast and the sorted order no longer applies:

CREATE INDEX customers_email_idx ON customers (email);
EXPLAIN (COSTS OFF)
SELECT id FROM customers WHERE lower(email) = 'user42@example.com';

Seq Scan on customers
  Filter: (lower(email) = 'user42@example.com'::text)

Either compare the column directly (store emails lowercased), or index the expression - CREATE INDEX customers_email_lower_idx ON customers (lower(email));:

Bitmap Heap Scan on customers
  Recheck Cond: (lower(email) = 'user42@example.com'::text)
  ->  Bitmap Index Scan on customers_email_lower_idx
        Index Cond: (lower(email) = 'user42@example.com'::text)

The same trap hides in date filters. With an index on created_at:

SELECT id, total FROM orders WHERE created_at::date = '2026-01-15';

Seq Scan on orders
  Filter: ((created_at)::date = '2026-01-15'::date)

Rewrite it as a half-open range on the column, and the index serves it:

SELECT id, total FROM orders
WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16';

Index Scan using orders_created_idx on orders
  Index Cond: ((created_at >= '2026-01-15 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-01-16 00:00:00+00'::timestamp with time zone))

You can't fix this one with an expression index: casting a timestamptz to date depends on the session's time zone, and an index expression must be immutable - CREATE INDEX ON orders ((created_at::date)) fails with functions in index expression must be marked IMMUTABLE. Other predicates an index can't serve: LIKE '%term' (a leading wildcard), comparing a column to a different type (WHERE id::text = '42'), and arithmetic on the column (WHERE total * 1.2 > 100 - move it to the other side).

When Postgres ignores the index - on purpose

With an index on status, the planner reads the whole table for 'delivered' (90% of rows) but uses the index for 'pending' (1%):

Seq Scan on orders
  Filter: (status = 'delivered'::text)

Index Scan using orders_status_idx on orders
  Index Cond: (status = 'pending'::text)

Fetching most rows one by one through an index is slower than reading the table in order, and the planner knows the distribution from the statistics ANALYZE collects. An unused index isn't always a bug - check how many rows match before adding one. On a tiny table, a sequential scan is always cheapest.

Partial and covering indexes

A partial index indexes only the rows a hot query needs. A queue that always asks for pending orders:

CREATE INDEX orders_pending_idx ON orders (created_at)
    WHERE status = 'pending';
EXPLAIN (COSTS OFF)
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10;

Limit
  ->  Index Scan using orders_pending_idx on orders

It holds 1% of the rows - less than a tenth of the size of an index on created_at for every order - and stays small as delivered orders pile up. The query must repeat the index's condition (status = 'pending') for Postgres to use it.

A covering index adds columns with INCLUDE, so a query can be answered from the index without visiting the table:

CREATE INDEX orders_customer_total_idx ON orders (customer_id) INCLUDE (total);
EXPLAIN (COSTS OFF)
SELECT customer_id, total FROM orders WHERE customer_id = 42;

Index Only Scan using orders_customer_total_idx on orders
  Index Cond: (customer_id = 42)

An index-only scan still checks the table's visibility map; on a table with many recent changes, VACUUM is what keeps it from falling back to the table.

Clustered vs non-clustered

Interviews ask about this constantly, and the answer depends on the database:

• Non-clustered index: a separate sorted structure whose entries point to rows stored somewhere else. Following a pointer means one more page read.
• Clustered index: the table's rows are stored inside the index, in its order. There can be only one per table, since rows can only be stored in one order.

What it means in practice: in InnoDB, a range of primary keys is cheap (the rows are next to each other) and the primary key's size matters (every secondary index stores it). In PostgreSQL, every index is non-clustered, so a range scan that returns many rows can touch one table page per row - which is why the planner switches to a bitmap or a sequential scan. The deep dive measures exactly how much physical order matters.

Which index for which query?

Pagination: keyset, not OFFSET

LIMIT 20 OFFSET 100000 makes Postgres produce and throw away 100,000 rows to show 20, and pages shift when rows are added. Keyset pagination remembers the last row shown and asks for the rows after it. With a unique tiebreaker (id) and a row comparison, one index serves every page:

CREATE INDEX orders_customer_created_id_idx ON orders (customer_id, created_at, id);
EXPLAIN (COSTS OFF)
SELECT id, created_at
FROM orders
WHERE customer_id = 42
  AND (created_at, id) < ('2026-02-01', 9000)
ORDER BY created_at DESC, id DESC
LIMIT 5;

Limit
  ->  Index Only Scan Backward using orders_customer_created_id_idx on orders
        Index Cond: ((customer_id = 42) AND (ROW(created_at, id) < ROW('2026-02-01 00:00:00+00'::timestamp with time zone, 9000)))

(created_at, id) < (x, y) compares pairs in order - earlier created_at, or the same one with a smaller id - so orders that share a timestamp are neither skipped nor repeated, and the whole comparison is an Index Cond. The cost: no jumping to page 500, only next and previous.

Indexes aren't free

Every index is updated on every insert, and on every update of an indexed column; it takes disk and memory; and building one locks writes unless you use CREATE INDEX CONCURRENTLY (slower, can't run inside a transaction). Index what your queries filter, join and sort by - including foreign-key columns - and drop indexes that nothing uses: pg_stat_user_indexes.idx_scan counts how often each has been used since statistics were last reset.

Where it breaks

• Wrong column order in a composite index - equality columns first, then range or sort.
• A function or cast on the indexed column (lower(email), created_at::date) - rewrite the predicate or index the expression.
• Expecting an index for a condition most rows match - the planner is right to scan.
• OFFSET for deep pages - use keyset pagination with a unique tiebreaker.
• EXPLAIN ANALYZE on a write - it runs; wrap it in a rolled-back transaction.
• Indexing every column "just in case" - each slows writes; drop the unused ones.

Tradeoffs

• One composite index vs several single-column ones: a composite built for the query beats combining separate indexes; separate ones serve more varied queries less well.
• Partial vs full index: a partial index is small and fast but only serves queries that repeat its condition.
• Covering (INCLUDE) vs lean: index-only scans save table reads at the cost of a bigger index to maintain.
• Keyset vs OFFSET: keyset is fast at any depth and stable under inserts; OFFSET allows jumping to page N.

Going further. The deep dive (next tab) measures how physical row order changes the cost of the same index scan - 21 page reads versus 1,008 - shows what CLUSTER does and undoes, compares PostgreSQL with InnoDB and SQL Server clustering in depth, and covers the planner's cost model, index-only scans and the visibility map, B-tree deduplication, the other index types, and the real cost of every index.

Check yourself

1. The page query filters customer_id = ? and sorts by created_at DESC LIMIT 20. Which index avoids the sort, and why not (created_at, customer_id)?

2. There's an index on created_at, yet WHERE created_at::date = '2026-01-15' scans the whole table. Why, and what's the fix?

3. Why does keyset pagination compare (created_at, id) rather than just created_at?

Key takeaways

• A B-tree index serves =, ranges and ORDER BY on its columns; EXPLAIN shows whether a query uses it, and EXPLAIN ANALYZE runs it and shows what really happened.
• Composite indexes: equality columns first, then the range or sort column. Postgres 18's skip scan helps when the leading column has few values.
• Keep indexed columns bare in predicates - no functions or casts - or index the expression (it must be immutable).
• The planner skips indexes for conditions most rows match; that's usually right.
• Partial indexes for hot subsets, INCLUDE for index-only scans, keyset pagination instead of deep OFFSETs - and every index has a write cost.