beginnerintermediateadvanced

PostgreSQL · Indexing

Index Fundamentals

The read/write trade-off every index makes, the three scan types, selectivity and cardinality, why indexing everything backfires, composite index ordering, the leftmost-prefix rule, and index-only scans

A trade, not a free winfaster reads, slower writes, more storage
3 scan typesSeq, Index, Bitmap — chosen by selectivity
Leftmost-prefix rulea composite index only narrows from its first column
Index-only scanskips the heap entirely, when it can

1.Why Indexes Speed Reads but Increase Storage and Write Cost

An index is a separate, ordered structure that lets PostgreSQL find matching rows without scanning the whole table — but that structure is itself real data that must be stored on disk and kept in sync with every INSERT, UPDATE (of an indexed column) and DELETE, so more indexes means faster reads at the direct cost of more storage and slower writes.

Key features

An index is a separate on-disk structure, occupying its own storage in addition to the table itself
Every INSERT must add an entry to every index on the table, not just write the row
Every UPDATE that changes an indexed column must update that index too (and, per the Storage and Table Internals section, disqualifies that update from the cheaper HOT path)
Every DELETE eventually requires cleanup in every index referencing the deleted row, alongside the heap cleanup
More indexes on a table directly means more write amplification — each logical write becomes several physical writes

What each index costs, per relevant write

What each index costs, per relevant write
Operation	Extra cost per index on the table
INSERT	a new entry added to every index
UPDATE (indexed column)	the index entry must be updated too; disqualifies HOT
DELETE	the index entry eventually needs cleanup as well

What an index actually trades

No index

INSERT/UPDATE/DELETE touch only the table
No extra storage
Reads must scan every row

With an index

Every write also updates the index
Extra on-disk storage, maintained in lockstep
Matching reads skip straight to the row

Command

sql

-- storage cost is directly observable
SELECT pg_size_pretty(pg_relation_size('accounts')) AS table_size,
       pg_size_pretty(pg_indexes_size('accounts')) AS all_indexes_size;

An index is a real, separate on-disk structure that must be kept in sync with every write — more indexes means faster reads for queries that use them, but more storage and slower INSERT/UPDATE/DELETE for everything else. Add indexes based on confirmed query patterns, not speculative "might need it" coverage.

2.Index Scan, Bitmap Index Scan and Sequential Scan

A Seq Scan reads every row in the table in physical order, checking each against the filter — best when most rows match or the table is small. An Index Scan walks the index for matching entries and fetches each matching row from the heap individually — best for very few matching rows. A Bitmap Index Scan + Bitmap Heap Scan is the middle ground: it collects all matching row locations from the index first, sorts them into physical order, then fetches from the heap in that order — better than a plain Index Scan when many (but not all) rows match, because it avoids scattered random I/O.

Key features

Seq Scan: reads the entire table in physical order, checking every row — chosen when most rows match, or the table is small enough that scanning is cheap regardless
Index Scan: walks an index for matching entries, fetching each matching row from the heap individually, in index order — chosen for a small number of matching rows
Bitmap Index Scan + Bitmap Heap Scan: collects all matching locations from the index, sorts them into physical order, then fetches from the heap in that order — chosen for a moderate number of matching rows
The planner picks based on cost estimates driven by selectivity (what fraction of rows are expected to match) — the same query can get a different scan type as data or statistics change
EXPLAIN shows exactly which scan type was chosen for a given query, making this a directly observable decision rather than a hidden one

Index Scan vs Bitmap Scan, at a moderate match count

Plain Index Scan

One random heap fetch per matching row
Cheap for a handful of matches
Expensive once matches are numerous — scattered I/O adds up

Bitmap Index Scan + Bitmap Heap Scan

Collects all matches first, sorts by physical location
Fetches from the heap in that sorted order
Turns scattered I/O into something closer to sequential — better for moderate match counts

Command

sql

EXPLAIN SELECT * FROM accounts WHERE id = 42;
-- Index Scan using accounts_pkey on accounts

EXPLAIN SELECT * FROM accounts WHERE balance > 900 AND balance < 1000;
-- Bitmap Heap Scan on accounts
--   ->  Bitmap Index Scan on idx_balance

EXPLAIN SELECT * FROM accounts;
-- Seq Scan on accounts

Seq Scan: read everything, best when most rows match. Index Scan: walk the index, fetch each match individually, best for very few matches. Bitmap Index Scan + Bitmap Heap Scan: collect matches, sort by physical location, then fetch — best for a moderate match count, turning scattered I/O into something closer to sequential. The planner chooses automatically, visible via EXPLAIN.

3.Selectivity and Cardinality

Selectivity is the fraction of a table's rows that a WHERE condition is expected to match — a highly selective condition matches very few rows (great for an index), a low-selectivity condition matches most of them (an index rarely helps). Cardinality generally refers to the number of distinct values in a column; a column with high cardinality (many distinct values, like an email address) tends to make equality filters on it highly selective, while low cardinality (few distinct values, like a boolean or a status with 3 states) tends to make filters on it low-selectivity.

Key features

Selectivity: the fraction of rows a condition is expected to match — low selectivity means many rows match, high selectivity means few do
Cardinality: generally, the number of distinct values in a column (or the size of a result set) — the planner tracks this via n_distinct in pg_stats
High-cardinality columns tend to make equality filters highly selective; low-cardinality columns tend to make them low-selectivity
The planner estimates selectivity from ANALYZE-gathered statistics (most-common values, histograms), not by scanning the actual data at query time
An index on a low-selectivity column is frequently NOT used by the planner even if it exists, because a sequential scan is genuinely cheaper for a condition that matches a large fraction of the table

Cardinality vs typical selectivity

Cardinality vs typical selectivity
Column example	Cardinality	Typical selectivity of an equality filter
is_active (boolean)	low (2 distinct values)	low — roughly half the table matches
status (5-state enum)	low-to-medium	low-to-medium — 1/5th of the table, roughly
email	high (near-unique)	high — typically one row matches

Cardinality drives selectivity — and whether an index actually helps

high selectivity — index helps a lotlow selectivity — index rarely helps

low cardinalityhigh cardinality

is_active (boolean): roughly half the table matches an equality filter
status (5-state enum): roughly 1/5th of the table matches
email: near-unique — an equality filter matches essentially one row

Command

sql

-- inspect the planner's own cardinality/selectivity estimates directly
SELECT attname, n_distinct, most_common_vals, most_common_freqs
  FROM pg_stats
 WHERE tablename = 'accounts' AND attname = 'status';

Selectivity is the fraction of rows a condition matches — low selectivity means an index rarely helps. Cardinality (distinct value count) usually drives it: high-cardinality columns (email) make equality filters highly selective; low-cardinality columns (booleans, small enums) usually do not. The planner estimates this from ANALYZE statistics, visible directly in EXPLAIN's row estimates.

4.Why Indexing Every Column Is Harmful

Indexing every column compounds the write cost from the earlier storage/write-cost trade-off across every single write, while most of those indexes contribute nothing to read performance — either because the column is low-selectivity (an index rarely helps regardless) or because no real query actually filters on it. The result is a table that is slow to write to for a read benefit that is mostly imaginary.

Key features

Every additional index compounds INSERT/UPDATE/DELETE cost, as covered earlier — indexing every column multiplies that cost by however many columns exist
Many columns are low-cardinality or simply never filtered on — an index there pays the full write cost with essentially no read benefit
Indexes also consume memory (shared_buffers competition) and need their own vacuum/maintenance attention — costs beyond just disk space
pg_stat_user_indexes' idx_scan column shows exactly how often each index has actually been used — the direct evidence for whether an index is earning its keep
The correct default is to index based on confirmed query patterns (WHERE, JOIN, ORDER BY columns actually used by real queries), not to index defensively for hypothetical future queries

What "index everything" actually costs vs delivers

What "index everything" actually costs vs delivers
Cost paid on every write	Read benefit delivered
Extra index entry per relevant index	only for the indexes actual queries use
Extra storage per index	zero for unused indexes
Extra vacuum/maintenance burden per index	zero for unused indexes

Command

sql

-- find indexes that are pure cost, no benefit
SELECT schemaname, relname, indexrelname, idx_scan
  FROM pg_stat_user_indexes
 WHERE idx_scan = 0
 ORDER BY relname;

Indexing every column compounds write cost across every single write, while most of those indexes deliver no read benefit — either because the column is low-selectivity or because no real query ever filters on it. pg_stat_user_indexes' idx_scan is the direct evidence for whether an index earns its keep; index based on confirmed query patterns, not defensive guessing.

5.Composite Index Ordering

A composite (multicolumn) index stores its entries sorted by its first column, then by its second column within each value of the first, and so on — the same way a phone book is sorted by last name, then first name within each last name, not independently by both. Column order in the index definition determines which queries can use it efficiently, not just which columns are included.

Key features

A composite index on (a, b, c) is sorted primarily by a, then by b within each a value, then by c within each (a, b) pair
Column order in CREATE INDEX matters — an index on (a, b) is a physically different structure from one on (b, a), even though they cover the same two columns
This ordering is the direct foundation for the leftmost-prefix rule (covered next): efficient use requires constraining columns starting from the leftmost one
Choosing which column goes first is a real design decision — usually driven by which column is more selective, or which is more consistently present in the actual queries that will use the index
A composite index is a single structure, not "two indexes combined" — its cost (storage, write overhead) is roughly proportional to one index over all its columns together, not the sum of separate single-column indexes

(a, b) vs (b, a) — different structures, different strengths

(a, b) vs (b, a) — different structures, different strengths
Index	Sorted primarily by	Efficient for
(a, b)	a, then b within each a	WHERE a = ? [AND b = ?]
(b, a)	b, then a within each b	WHERE b = ? [AND a = ?]

Column order sets the sort order, primary then secondary

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

customer_id

Primary sort key — the index is sorted by this column first

created_at

Secondary sort key — sorted within each customer_id value, not globally

Command

sql

-- sorted primarily by customer_id, then by created_at within each customer
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

-- efficiently supports:
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;
-- does NOT efficiently support a query filtering ONLY on created_at

A composite index on (a, b, c) is one structure, sorted primarily by a, then b within each a, then c within each (a, b). Column order is a real design decision — match the leading column to what real queries constrain first, since (a, b) and (b, a) are physically different structures with different strengths.

6.Leftmost-Prefix Behavior

A composite B-tree index on (a, b, c) can be used efficiently by a query that constrains a (with equality or a range), a and b, or all three — always starting from the leftmost column. A query that constrains only b, only c, or b and c but not a, cannot use the index to narrow the search — PostgreSQL would have to scan the whole index, which the planner will usually recognize is not worth doing over a sequential scan.

Key features

Efficient prefixes of an index on (a, b, c): a alone, (a, b), or (a, b, c) — always starting from the leftmost column
Equality constraints on leading columns let the index scan narrow to an exact range; the first column with only a range constraint (not equality) still narrows the scan, but columns after it do not further narrow it
A constraint on b or c alone, with nothing on a, cannot use this index to narrow the search at all — the planner will typically choose a different index or a sequential scan instead
Given WHERE a = 5 AND b >= 42 AND c < 77 on an index on (a, b, c): the scan narrows using a = 5 AND b >= 42, but c < 77 is only checked afterward (a "filter"), not used to narrow the scanned range
This is exactly why column order (the previous concept) matters so much — the leftmost-prefix rule is what makes that ordering decision consequential rather than cosmetic

Which prefixes of (a, b, c) are usable

a alone

usable

a, b

usable

a, b, c

usable

b alone (no a)

NOT usable to narrow the scan

Command

sql

CREATE INDEX idx_abc ON t (a, b, c);

SELECT * FROM t WHERE a = 5;                          -- uses the index (prefix: a)
SELECT * FROM t WHERE a = 5 AND b = 10;                -- uses the index (prefix: a, b)
SELECT * FROM t WHERE a = 5 AND b >= 10 AND c < 20;    -- uses a, b to narrow; c is a filter after
SELECT * FROM t WHERE b = 10;                          -- does NOT use the index to narrow

A composite B-tree index on (a, b, c) can only be used efficiently to narrow a search starting from a — usable prefixes are a, (a,b), or (a,b,c). A query constraining only b or only c, with nothing on a, cannot use the index to narrow the scan at all, even though those columns are technically part of it. Column order in the index definition is what determines which query shapes actually benefit.

7.Covering and Index-Only Scans

An index-only scan answers a query using ONLY the index, never touching the table's heap at all — possible when every column the query needs (in SELECT, WHERE, everything) is present in the index, and the relevant heap pages are known (via the visibility map) to have no uncommitted or invisible versions to worry about. A covering index is one deliberately designed to include extra, non-key columns (via INCLUDE) specifically so more queries can qualify for this scan.

Key features

Requires: every column the query references (SELECT, WHERE, everything) must be present in the index — B-tree indexes always support this; GIN indexes never do
Requires: the visibility map must show the relevant heap page as 'all visible' — otherwise a heap visit is still needed to check MVCC visibility for that specific row
A covering index uses INCLUDE to store extra columns as non-key payload, purely to let more queries qualify as index-only, without affecting the index's actual sort order
The visibility map is much smaller than the heap and usually stays cached in memory, which is why checking it is cheap compared to a real heap fetch
Index-only scans give the biggest benefit on tables that change infrequently — a table under heavy UPDATE/DELETE churn keeps invalidating visibility map bits, reducing how often the shortcut actually applies

Requirements for an index-only scan

Requirements for an index-only scan
Requirement	Why
Every needed column is in the index	no missing data the index cannot supply
Heap page marked 'all visible'	no need to double-check MVCC visibility via the heap

Does the query need to visit the heap?

Plain index (x)

SELECT x, y FROM tab WHERE x = ?
y is not in the index
Must visit the heap to fetch y — Index Scan

Covering index (x) INCLUDE (y)

Same query, same result
y is stored as payload in the index
Heap page all-visible → skips the heap — Index Only Scan

Command

sql

-- a covering index: x is the search key, y is included purely as payload
CREATE INDEX idx_x_covering_y ON tab (x) INCLUDE (y);

EXPLAIN SELECT y FROM tab WHERE x = 'key';
-- Index Only Scan using idx_x_covering_y on tab

An index-only scan skips the heap entirely when every needed column is in the index AND the visibility map confirms the page is all-visible. INCLUDE builds a covering index by storing extra columns as non-key payload specifically to enable this. The benefit is real but partial on heavily-updated tables, since writes keep invalidating the visibility map.

Spotted a mistake on this page? Tell us.

Read Sheet