Indexes & query plans: making the right reads cheap

A table's rows sit in no useful order. To answer WHERE customer_id = 42 without an index, PostgreSQL reads every row and checks each one — a sequential scan. Fine for 100 rows; slow for 10 million, and slower every month.

An index is a second structure that keeps a column's values sorted, each with a pointer to the row it came from:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

The default kind is a B-tree: a shallow tree whose leaves hold the sorted values. Finding 42 means walking down a few levels — a handful of page reads, and only a few more for a hundred million rows than for a thousand — then following the pointers to just the matching rows.

Because the values are sorted, a B-tree helps with more than equality:

What it costs. An index isn't free:

• Every write maintains it. Each INSERT adds an entry to every index on the table (every partial index whose condition the row meets), and most UPDATEs do too (the exception: an update that changes no indexed column and fits on the same page). Five indexes means five extra structures touched per write.
• Space — an index on a big table is itself big.
• Building it blocks writes. A plain CREATE INDEX lets reads continue but blocks INSERT, UPDATE and DELETE until it finishes (the migrations unit covers CONCURRENTLY).

So index for the queries you actually run, and drop indexes nothing uses.

Two indexes you get without asking: every PRIMARY KEY and every UNIQUE constraint builds a unique B-tree index. A foreign key does not — orders.customer_id above needed its own.

A multicolumn index on (a, b) is sorted by a, and by b only within each value of a — like a phone book sorted by surname, then first name. That one fact gives you the rules.

The PostgreSQL manual states it precisely: equality conditions on the leading columns, plus a range condition on the first column without an equality, limit how much of the index is read. Conditions on later columns are checked inside the index (saving trips to the table), but they don't necessarily shrink the slice — only PostgreSQL 18's skip scan, below, sometimes can.

Equality first, then the range

The query: one customer's orders in a three-month window, on a 200,000-row table (PostgreSQL 18, EXPLAIN ANALYZE, trimmed):

WHERE customer_id = 42 AND created_at >= '2025-01-01' AND created_at < '2025-04-01'

-- index on (created_at, customer_id)
 Index Scan using orders_created_customer_idx on orders (actual time=0.833..4.499 rows=13.00 loops=1)
   Index Cond: ((created_at >= …) AND (created_at < …) AND (customer_id = 42))
   Buffers: shared hit=28 read=495

-- index on (customer_id, created_at)
 Bitmap Heap Scan on orders (actual time=0.033..0.050 rows=13.00 loops=1)
   Buffers: shared hit=13 read=3
   ->  Bitmap Index Scan on orders_customer_created_idx
         Index Cond: ((customer_id = 42) AND (created_at >= …) AND (created_at < …))

Same 13 rows. With the range column first, the scan walks every order in those three months (about 129,000 index entries) and checks customer_id on each: 523 pages and 4.5 ms. With the equality column first, it jumps to customer 42 and reads only that customer's dates: 16 pages and 0.05 ms.

The index can supply the ORDER BY

"The newest 20 open orders":

CREATE INDEX orders_status_created_idx ON orders (status, created_at);

SELECT * FROM orders WHERE status = 'open' ORDER BY created_at DESC LIMIT 20;

 Limit  (actual time=0.196..0.217 rows=20.00 loops=1)
   ->  Index Scan Backward using orders_status_created_idx on orders
         Index Cond: (status = 'open'::text)

Within status = 'open' the entries are already in created_at order, so PostgreSQL reads the last 20 backwards and stops. No Sort node; without the index the plan was a sequential scan of all 200,000 rows feeding a sort.

Leftmost prefix

An index on (customer_id, created_at) serves customer_id = ?, and customer_id = ? AND created_at …. It is a poor fit for created_at alone: the dates are scattered across every customer's section of the index, so the planner will usually prefer another plan.

PostgreSQL 18 softens this rule with skip scan. When the leading column has only a few distinct values, the planner can run one index search per value. With an index on (status, created_at) (two statuses), a query on created_at alone used it:

 Bitmap Index Scan on orders_status_created_idx (actual time=0.141..0.142 rows=1440.00 loops=1)
   Index Cond: ((created_at >= …) AND (created_at < …))
   Index Searches: 5

Index Searches (new in PostgreSQL 18's EXPLAIN ANALYZE output) shows the skipping: several small searches instead of one scan of the whole index.

With thousands of distinct leading values, skip scan doesn't pay and you're back to the rule. Don't design around skip scan; design so your hot queries have their equality columns leading.

The recipe

1. Columns compared with = first (all of them).
2. Then one range or sort column.
3. Stop. The manual's advice: multicolumn indexes should be used sparingly, and more than three columns is rarely helpful.

The index exists, EXPLAIN says Seq Scan. Work down this list — every case below except the collation one was run on PostgreSQL 18.

1. The query wraps the column in a function or cast

-- index on (email)
EXPLAIN SELECT * FROM users WHERE lower(email) = 'user42@example.com';
 Seq Scan on users
   Filter: (lower(email) = 'user42@example.com'::text)

-- index on (created_at)
EXPLAIN SELECT * FROM orders WHERE created_at::date = '2025-03-01';
 Seq Scan on orders
   Filter: ((created_at)::date = '2025-03-01'::date)

The index holds email, not lower(email), so it can't answer a question about lower(email). Two fixes: rewrite the condition so the bare column is compared — for dates, a half-open range created_at >= '2025-03-01' AND created_at < '2025-03-02' gave an Index Scan — or index the expression itself:

CREATE INDEX users_lower_email_idx ON users (lower(email));
-- now: Index Scan using users_lower_email_idx … Index Cond: (lower(email) = …)

An expression index matches only the same expression: lower(email) in the query, not upper(email) and not email ILIKE ….

2. LIKE with a leading wildcard — or the wrong collation

A B-tree can serve LIKE 'User42%' (it becomes a range: >= 'User42' AND < 'User43'), never LIKE '%@example.com' — the beginning of the string is unknown, so there's no place in the sorted order to start. For "contains" or "ends with" searches you need a different index type (the pg_trgm extension's trigram indexes), not a B-tree.

Even the anchored pattern has a catch. In a database whose collation isn't C (check SELECT datcollate FROM pg_database; en_US.UTF-8 is common), the default B-tree can't serve LIKE at all. On such a database (tested on PostgreSQL 14 with en_US.UTF-8; the rule is the same in 18), LIKE 'User42%' was a Seq Scan until:

CREATE INDEX users_email_pattern_idx ON users (email text_pattern_ops);

text_pattern_ops compares strictly character by character. Keep the default index too if you also run </> range queries on the column — the pattern index can't serve those.

3. The condition matches most of the table

-- 95% of orders are 'shipped'; index on (status)
EXPLAIN SELECT * FROM orders WHERE status = 'shipped';
 Seq Scan on orders  (cost=0.00..4167.00 rows=190000 width=37)

This isn't a failure. Fetching 190,000 rows through an index means jumping around the table page by page; reading the table straight through is cheaper. An index pays off when it's selective. For a skewed column, the useful index is often a partial one on the rare value (WHERE status = 'open').

4. The planner's statistics are wrong

The planner chooses by estimated row counts. After a bulk load, or when the data's shape has changed, the estimates can be far off. In EXPLAIN ANALYZE, compare rows= in the estimate with rows= in the actual part of the same node: a 100× miss is the planner working from bad information. Run ANALYZE table_name and look again.

Testing the theory

The manual's procedure: run ANALYZE first, then, for testing only, SET enable_seqscan = off and re-run EXPLAIN ANALYZE. If it still won't use the index, the condition doesn't match the index (cases 1 and 2). If it uses it and runs slower, the planner was right (case 3). If it uses it and runs faster, the cost estimates are wrong (case 4). RESET enable_seqscan afterwards — it is a diagnostic, not a fix.