Adding indexes, and what they cost on every write

Now the practical part: creating indexes, and the handful of rules that decide whether yours gets used. Every rule here is a consequence of the B-tree being sorted — so if one looks arbitrary, go back and picture the tree.

Creating one

CREATE INDEX idx_loans_member ON loans (member_id);

In production, always:

CREATE INDEX CONCURRENTLY idx_loans_member ON loans (member_id);

A plain CREATE INDEX takes a lock that blocks all writes to the table for as long as the build takes — minutes on a large table, which is an outage. CONCURRENTLY builds it without that lock.

Its three costs, all worth knowing before you need it at 2am:

• It is slower, because it scans the table twice.
• It cannot run inside a transaction block, so it cannot be bundled with other DDL in one migration step.
• If it fails, it leaves an INVALID index behind, which is dead weight the planner ignores. Find them and drop them:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

DROP INDEX CONCURRENTLY exists too, and is the same idea.

What you already have for free

CREATE TABLE loans (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- indexed
  book_id bigint NOT NULL REFERENCES books(id),         -- NOT indexed
  member_id bigint NOT NULL REFERENCES members(id),     -- NOT indexed
  isbn text UNIQUE                                      -- indexed
);

PRIMARY KEY and UNIQUE create indexes. REFERENCES does not.

That asymmetry causes real problems. A foreign key without an index on the referencing side means:

• SELECT * FROM loans WHERE member_id = 12 scans the whole table.
• Deleting a member scans loans to check the constraint — and holds a lock while doing it. On a big child table this is how a routine delete becomes a timeout.

Index your foreign keys. Here is a query to find the ones you have missed:

SELECT c.conrelid::regclass AS table_name, a.attname AS column_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]
  );

The exception: a foreign key you never query by and whose parent you never delete from.

Composite indexes, and the one rule that matters

CREATE INDEX idx_loans_member_date ON loans (member_id, borrowed_on);

Leftmost prefix rule: a composite index can serve any query using a prefix of its columns, starting from the left.

For (member_id, borrowed_on):

Note the last two together: the order you write conditions in the WHERE clause means nothing. The order of columns in the index definition is everything.

Proved on the 500,000-row table. With only (member_id, tag) available, a member_id-only query uses it:

 Index Only Scan using idx_big_mt on big
   Index Cond: (member_id = 42)

while a tag-only query cannot:

 Gather
   Workers Planned: 2
   ->  Parallel Seq Scan on big
         Filter: (tag = 'tag17'::text)

Why? The index is sorted by member_id first. All the rows for member_id = 42 are together, so that is a range. But tag = 'tag17' appears once inside every single member_id group — scattered through the whole index. There is no range to scan. Picture a phone book sorted by surname then first name: finding every "Priya" means reading all of it.

Which column goes first

1. Columns used with = before columns used with ranges. An index can use only one range column, and it must be last. (status, created_at) for WHERE status = 'active' AND created_at > X — the other order wastes the second column.
2. Then the column with more distinct values, so you cut the search down faster.
3. Then match your ORDER BY, to avoid a sort.

And the corollary: (a, b) makes a separate index on (a) redundant. Drop it. It only costs writes. But (b) alone is a genuinely different index.

Sort direction

CREATE INDEX idx_loans_recent ON loans (member_id, borrowed_on DESC);

A B-tree reads backwards perfectly well — a fully reversed ORDER BY gives you an Index Scan Backward — so a single-column DESC index buys nothing.

It matters when directions are mixed. With only an all-ASC index available, ORDER BY member_id ASC, made_on DESC cannot be satisfied by reading either way:

 Limit
   ->  Incremental Sort
         Sort Key: member_id, made_on DESC
         Presorted Key: member_id
         ->  Index Scan using idx_big_member on big

Note what PostgreSQL does instead — an Incremental Sort. It uses the index for the member_id part (Presorted Key: member_id) and sorts only within each group. Far better than a full sort of 500,000 rows, and a good thing to recognise. But it is still a sort, and with the matching index it disappears entirely:

 Limit
   ->  Index Scan using idx_mm_mix on big

NULLS FIRST / NULLS LAST is the same story.

Four kinds of index worth knowing

Partial: index only some rows

CREATE INDEX idx_loans_open ON loans (member_id) WHERE returned_on IS NULL;

Measured on the 500,000-row table, a partial index on recent rows against the full index:

Eleven times smaller, and it stays in cache, and it costs nothing for writes to rows outside the condition.

The catch: the planner must be able to prove your query implies the index's condition. WHERE returned_on IS NULL AND member_id = 5 works. WHERE member_id = 5 alone does not, even if every matching row happens to qualify.

This is the highest-value index type most people never use. Reach for it whenever you query a minority of rows — unreturned loans, unprocessed jobs, non-deleted records.

Expression: index a computation

SELECT * FROM members WHERE lower(email) = 'anjali@example.com';   -- no index can help

An index on email is useless here, because the index stores the original values and the query asks about a transformed one. This is the same lesson as member_id + 0 = 42, which also forces a sequential scan:

CREATE INDEX idx_members_email_lower ON members (lower(email));

Now it works — and the query must use exactly the same expression, character for character in meaning.

For case-insensitive email, citext or a CHECK that stores it lowercased is usually tidier than remembering lower() at every call site.

Covering: answer from the index alone

CREATE INDEX idx_loans_cover ON loans (member_id) INCLUDE (borrowed_on, returned_on);

INCLUDE columns are stored in the leaves but are not part of the sort order, so they cannot be searched — only returned. When every column a query needs is in the index, you get an Index Only Scan and the table is never touched:

 Index Only Scan using idx_big_mt on big
   Heap Fetches: 0

Heap Fetches: 0 is the proof. On the measured table this was 4 buffers instead of 492 — a further 100× reduction on top of what the index already bought.

Two caveats. It only works if PostgreSQL knows the page is fully visible, which depends on the visibility map, which VACUUM maintains — so a recently-updated table shows non-zero Heap Fetches until vacuumed. And a wide INCLUDE list makes the index big, which erodes the benefit.

Unique: a constraint that happens to be an index

CREATE UNIQUE INDEX idx_members_email ON members (lower(email));

Note that this enforces uniqueness on an expression, which UNIQUE on a column cannot do. Combine with a partial index for "one active subscription per member":

CREATE UNIQUE INDEX one_active ON subscriptions (member_id) WHERE status = 'active';

That is a business rule enforced by the database, in one line, and it is the trick worth taking from this lesson.

When an index cannot be used

Commit these, because each one is a query you will write by accident:

A function or arithmetic on the column. WHERE lower(email) = ..., WHERE member_id + 0 = 42, WHERE date_part('year', borrowed_on) = 2026. Rewrite as a range — WHERE borrowed_on >= '2026-01-01' AND borrowed_on < '2027-01-01' — or index the expression.

A leading wildcard. LIKE '%monsoon'. Use full-text search or a trigram index (pg_trgm with GIN), which genuinely can do this.

A type mismatch that blocks the comparison. Comparing a bigint column to a numeric, or a text column to an integer, can prevent index use. PostgreSQL is better at this than most databases, but check the plan.

NOT, != and NOT IN. These match most of the table, so a scan is correct.

OR across different columns. Sometimes handled with a BitmapOr of two indexes, often not. UNION ALL of two indexed queries is the reliable rewrite.

Low selectivity — the important one. Even a perfect index is ignored when too many rows match, and this is the planner being right. Measured:

Same index, same table, same query shape — only the constant differs. The rough threshold where an index stops paying is somewhere around 5–10% of the table, and it depends on random_page_cost and on how wide the rows are.

The lesson: an index on a status column with three values, where 90% are 'done', will never be used for status = 'done'. It may be used for status = 'failed'. A partial index on the rare values is the right answer.

Check your work

Why CONCURRENTLY in production. A plain CREATE INDEX blocks writes for the whole build.

Its three costs. Slower, cannot run in a transaction, and leaves an INVALID index if it fails.

Which constraints create indexes. PRIMARY KEY and UNIQUE — not REFERENCES.

Two problems from an unindexed foreign key. Slow lookups, and a parent delete scanning the child table while holding a lock.

The leftmost prefix rule. A composite index serves queries using a prefix of its columns from the left.

Why a tag-only query cannot use (member_id, tag). The tag values are scattered through every member_id group, so there is no contiguous range.

Whether WHERE clause order matters. No. Index column order does.

Column ordering rules. Equality before range; higher cardinality next; then match ORDER BY.

Which index (a, b) makes redundant. (a). Not (b).

When DESC in an index matters. Only for mixed directions in one ORDER BY — a fully reversed order gives an Index Scan Backward.

What an Incremental Sort is. Using an index for the leading sort columns and sorting only within each group.

What a partial index buys. Measured 11× smaller, and no write cost for excluded rows.

Its requirement. The query must imply the index's WHERE condition.

What INCLUDE columns can and cannot do. Be returned, not searched.

What proves an index-only scan. Heap Fetches: 0.

Why an index-only scan can stop working. The visibility map is stale until VACUUM runs.

Six situations where an index cannot be used. Functions on the column, leading wildcard, type mismatch, negation, OR across columns, low selectivity.

The rough selectivity threshold. Around 5–10% of the table.

Why an index on a 90%-common status value is useless. Too many rows match; use a partial index on the rare values.

Practice

1. Create an index with and without CONCURRENTLY while another session writes to the table. Observe the blocking.
2. Run the NOT indisvalid query on your database.
3. Run the missing-foreign-key-index query against a schema of yours and index what it finds.
4. Time a parent-row delete before and after indexing the child's foreign key.
5. Build (member_id, tag) and test all four query shapes from the table above.
6. Swap the column order and test the same four. Explain the difference.
7. Write the conditions in the opposite order in the WHERE clause and confirm the plan is identical.
8. Create (a) and (a, b), then check pg_stat_user_indexes to see whether (a) is ever used.
9. Build an index for ORDER BY member_id ASC, borrowed_on DESC and confirm the sort node disappears.
10. Build a partial index on unreturned loans. Compare its size with the full index.
11. Query it without the IS NULL condition and confirm it is not used.
12. Query WHERE lower(email) = ... with an index on email, then with an expression index.
13. Write WHERE date_part('year', borrowed_on) = 2026, check the plan, rewrite as a range, check again.
14. Add INCLUDE columns until a query becomes an Index Only Scan with Heap Fetches: 0.
15. Update some of those rows and re-run it. Watch Heap Fetches rise, then VACUUM and re-check.
16. Create a unique partial index enforcing one active subscription per member, and try to violate it.
17. Find the constant that flips a query between index scan and sequential scan.
18. Take a status column and measure the plan for the common value and a rare one.

Official documentation

• PostgreSQL — Indexes — The whole chapter; multicolumn, unique, expression, partial and index-only scans each have a section.
• PostgreSQL — Multicolumn indexes — The leftmost prefix rule in the official wording.
• PostgreSQL — Partial indexes — Including the "planner must prove implication" requirement.
• PostgreSQL — Index-only scans and covering indexes — INCLUDE, and the visibility-map dependency.
• PostgreSQL — CREATE INDEX — CONCURRENTLY, and what happens when it fails.
• PostgreSQL — pg_trgm — Trigram indexes, which can serve a leading wildcard.

Next: deciding which indexes to actually create.