beginnerintermediateadvanced

PostgreSQL · Section 20

Index Fundamentals

Level: intermediate
Read: 36 min
Concepts: 7

The real trade-off every index makes — faster reads at the direct cost of storage and write throughput — the three scan types the planner chooses between (Sequential, Index, Bitmap) based on estimated selectivity, selectivity and cardinality as the actual factors that determine whether an index helps at all, the concrete harm of indexing every column defensively rather than from confirmed query patterns, composite index ordering as a genuine design decision (not a stylistic one), the leftmost-prefix rule that makes that ordering consequential, and covering/index-only scans as the mechanism that can skip the heap entirely.

PostgreSQL overview

Learn PostgreSQL Tutorial - Full Course for BeginnersfreeCodeCamp.org

What is true here

An index is a real, separate structure kept in sync with every write — a deliberate trade of write cost and storage for read speed.
The planner chooses Seq Scan, Index Scan, or Bitmap Index+Heap Scan based on estimated selectivity, visible directly in EXPLAIN.
Selectivity is usually driven by cardinality — low-cardinality columns rarely benefit from a plain index on equality.
Indexing every column compounds write cost while delivering benefit only for the fraction of indexes real queries actually use.
A composite index is one structure sorted primarily by its first column — the leftmost-prefix rule is the direct, practical consequence.

What you will be able to do

Explain the write/storage cost every index imposes, and audit for indexes that are pure cost via idx_scan
Read EXPLAIN output to identify which scan type was chosen and why
Judge whether a column is a good indexing candidate based on its likely selectivity
Design composite index column order to match real query patterns, and explain the leftmost-prefix rule
Recognize an index-only scan in EXPLAIN and use INCLUDE to enable one deliberately

From the trade-off to using it well

The trade-off

faster reads, slower writes, more storage

Selectivity decides if it helps

the planner picks a scan type accordingly

Composite ordering + coverage

leftmost-prefix rule, index-only scans

The trade-off

What an index actually costs, and the three scan types the planner chooses between.

Why Indexes Speed Reads but Increase Storage and Write Cost

coreintermediate

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.

Think of it as

An index is not free lookup speed conjured from nothing — it is a second copy of (part of) the data, organized differently, that has to be maintained in lockstep with the table it indexes. Every write that touches an indexed column must also update every index covering that column, which is real, additional work on top of the write to the table itself. This is why "just add an index" is not a universally safe optimization — it is a deliberate trade of write cost and storage for read speed, worth making only when the read benefit outweighs those costs for the actual workload.

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;

What we're doing: Compare INSERT throughput on an unindexed table vs the same table with three indexes, making the write-cost trade-off directly observable.

index_write_cost.sqlsql

CREATE TABLE events_no_index (id SERIAL PRIMARY KEY, kind text, payload jsonb, created_at timestamptz);
\timing
INSERT INTO events_no_index (kind, payload, created_at)
  SELECT 'click', '{}', now() FROM generate_series(1, 100000);
-- Time: 850 ms

CREATE TABLE events_indexed (id SERIAL PRIMARY KEY, kind text, payload jsonb, created_at timestamptz);
CREATE INDEX idx_kind ON events_indexed (kind);
CREATE INDEX idx_created_at ON events_indexed (created_at);
CREATE INDEX idx_payload ON events_indexed USING gin (payload);

INSERT INTO events_indexed (kind, payload, created_at)
  SELECT 'click', '{}', now() FROM generate_series(1, 100000);
-- Time: 2400 ms -- roughly 3x slower, from maintaining 3 additional indexes

3–5: The baseline: 100,000 rows into a table with only its implicit primary-key index.
7–10: Three additional indexes, each of which now needs a new entry for every one of those same 100,000 rows.
13: The measured cost difference is the write-amplification effect made concrete — not a hypothetical, but a directly timed comparison.

Output

INSERT 0 100000
Time: 850.331 ms

INSERT 0 100000
Time: 2400.552 ms

Why this works: Each of the three extra indexes on the second table needed a new entry for every one of the 100,000 inserted rows — that is 300,000 additional index-entry writes on top of the 100,000 row writes, which is exactly why the timing roughly tripled: the cost is proportional to how many indexes exist, not a fixed overhead.

Adding an index for every column that might ever appear in a WHERE clause "just in case"

Wrong

sql

CREATE INDEX idx_col1 ON events (col1);
CREATE INDEX idx_col2 ON events (col2);
CREATE INDEX idx_col3 ON events (col3);
CREATE INDEX idx_col4 ON events (col4);
-- ... every column gets its own index "to be safe" ...
-- meanwhile this table receives heavy INSERT traffic

Better

sql

-- index only the columns actual queries filter/sort/join on,
-- confirmed via real query patterns (pg_stat_statements, EXPLAIN),
-- not speculative "might need it someday" coverage
CREATE INDEX idx_col1 ON events (col1);  -- confirmed: used by the dashboard's main filter

What you see: A table with many speculative indexes has unexpectedly slow write throughput, and most of the indexes turn out — on inspection with pg_stat_user_indexes — to have near-zero actual scans, meaning their write cost was paid continuously for a read benefit that was never realized.

Why: Every index is a standing cost paid on every relevant write, whether or not it is ever actually used to answer a query — indexing speculatively "just in case" pays that cost in full for a benefit that may never materialize, which is why indexes should be added in response to confirmed query patterns, not anticipated ones.

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

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

Remember: 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.

Index Scan, Bitmap Index Scan and Sequential Scan

coreintermediate

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.

Think of it as

The three scan types trade off differently as the fraction of matching rows grows. A plain Index Scan pays one random heap fetch per matching row — cheap for a handful of rows, expensive once there are many, because random I/O adds up. A Seq Scan pays one sequential read of the whole table regardless of how selective the filter is — wasteful if only a few rows match, but efficient if most do. A Bitmap scan sits between them: it still uses the index to know WHICH rows match, but batches the heap fetches into physical order first, turning scattered random I/O into something closer to sequential I/O — the right choice specifically for a moderate number of matches, where a plain Index Scan's per-row randomness would be expensive but a full Seq Scan would still read far more than necessary.

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

What we're doing: Trigger all three scan types on the same table by varying selectivity, confirming the planner's choice via EXPLAIN each time.

three_scan_types.sqlsql

EXPLAIN SELECT * FROM accounts WHERE id = 1;
-- Index Scan using accounts_pkey on accounts
--   Index Cond: (id = 1)

EXPLAIN SELECT * FROM accounts WHERE balance BETWEEN 500 AND 600;
-- Bitmap Heap Scan on accounts
--   Recheck Cond: ((balance >= 500) AND (balance <= 600))
--   ->  Bitmap Index Scan on idx_balance

EXPLAIN SELECT * FROM accounts WHERE balance > 0;
-- Seq Scan on accounts
--   Filter: (balance > 0)

1–3: A single-row lookup by primary key — the classic Index Scan case, very few matches.
5–8: A moderate range, matching enough rows that batching heap fetches via a bitmap beats a plain Index Scan.
10–11: Nearly every row matches — the planner correctly judges a full sequential read cheaper than consulting an index at all.

Output

-- three different scan strategies, chosen automatically based on
-- each query's actual estimated selectivity

Why this works: Nothing about these three queries was hand-tuned to force a particular scan type — the planner arrived at each independently based on its cost estimate for that specific WHERE clause's selectivity, which is exactly the mechanism this concept describes: the choice tracks how many rows are expected to match, not the query's syntax.

Assuming an index is "not being used" just because EXPLAIN does not show a plain Index Scan

Wrong

sql

EXPLAIN SELECT * FROM accounts WHERE balance BETWEEN 500 AND 600;
-- Bitmap Heap Scan on accounts
--   ->  Bitmap Index Scan on idx_balance
-- "this isn't an Index Scan, so my index on balance must not be
-- helping" -- WRONG conclusion

Better

sql

-- a Bitmap Index Scan on idx_balance in the plan means the index
-- IS being used -- just via the bitmap strategy, appropriate for
-- this query's moderate selectivity, not the plain Index Scan strategy

What you see: A developer drops or redesigns an index because EXPLAIN shows "Bitmap Heap Scan" instead of the expected "Index Scan," mistakenly concluding the index is unused, when in fact the Bitmap Index Scan step directly underneath it in the same plan is using that exact index.

Why: A Bitmap Heap Scan's plan always includes a Bitmap Index Scan step naming the index actually being consulted — the index absolutely is in use, just via the strategy the planner judged cheapest for this query's specific selectivity, which is a completely normal, expected outcome rather than a sign of a problem.

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

Remember: 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.

Does it actually help?

Selectivity and cardinality as the real factors, and the concrete harm of indexing defensively.

Selectivity and Cardinality

coreintermediate

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.

Think of it as

Selectivity is what actually determines whether an index helps a given query — not whether an index EXISTS on the column, but how much it would actually narrow down the search. An index on a boolean column (2 distinct values) rarely helps much, because matching one value still leaves roughly half the table — low cardinality drives low selectivity. An index on a column like email (near-unique values) is extremely selective, since an equality match narrows the result to essentially one row — high cardinality drives high selectivity. This is precisely the reasoning the planner performs, using ANALYZE's statistics, when it decides whether an index is worth using for a specific WHERE clause.

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';

What we're doing: Compare EXPLAIN's row estimates for a high-selectivity filter (email) vs a low-selectivity filter (is_active), confirming the planner's cardinality-driven reasoning directly.

selectivity_comparison.sqlsql

EXPLAIN SELECT * FROM accounts WHERE email = 'alice@example.com';
-- Index Scan using idx_email on accounts (rows=1)
--   Index Cond: (email = 'alice@example.com'::text)

EXPLAIN SELECT * FROM accounts WHERE is_active = true;
-- Seq Scan on accounts (rows=482103)
--   Filter: (is_active = true)

1–3: email is high-cardinality — the planner correctly estimates this equality filter matches essentially one row, and an Index Scan is the obvious choice.
5–7: is_active is low-cardinality — the planner estimates roughly half the table matches, correctly concluding a Seq Scan beats using the index even though one may exist.

Output

-- email filter: rows=1, Index Scan
-- is_active filter: rows=482103, Seq Scan (even if idx_is_active exists)

Why this works: The two row estimates (1 vs 482,103) are the planner's selectivity estimate made visible — everything downstream, including which scan type gets chosen, follows directly from that number, which itself comes from ANALYZE's recorded cardinality information about each column, not from re-examining the actual data at query time.

Adding an index on a low-cardinality column expecting it to speed up an equality filter

Wrong

sql

CREATE INDEX idx_is_active ON accounts (is_active);
-- expectation: "queries filtering on is_active will now be fast"
-- reality: the planner still chooses a Seq Scan for WHERE is_active = true,
-- because roughly half the table matches regardless of the index existing

Better

sql

-- a PARTIAL index targeting the SELECTIVE side of a low-cardinality
-- column can still help, if queries specifically target the rare value:
CREATE INDEX idx_inactive_accounts ON accounts (id) WHERE is_active = false;
-- useful IF is_active = false is actually rare -- verify with pg_stats first

What you see: An index created specifically to speed up a common filter shows near-zero scans in pg_stat_user_indexes, and EXPLAIN continues to show a Seq Scan for the exact query the index was meant to help.

Why: Low cardinality directly produces low selectivity for an equality filter, and the planner correctly recognizes that scanning the whole table is cheaper than an index scan when a large fraction of rows match — an index on such a column is frequently a wasted write cost with no real read benefit for equality filters on the common value, though it can still help a highly selective PARTIAL index or a filter on the column's rare value.

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

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

Remember: 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.

Why Indexing Every Column Is Harmful

standardintermediate

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.

Think of it as

The harm from indexing everything is not a single big cost — it is a large number of small costs (each index's own write overhead, storage, and vacuum/maintenance burden) accumulating with nothing proportionate to show for it, since only a fraction of those indexes are ever actually used by real queries. The right amount of indexing is "exactly what confirmed query patterns need," and this concept is the roadmap's explicit warning against treating "more indexes" as an unconditionally safe habit rather than a deliberate, evidence-based trade-off.

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;

What we're doing: Audit a table that was indexed defensively on every column, using idx_scan to find which indexes are pure overhead.

audit_unused_indexes.sqlsql

-- a table with 8 columns, all indexed "just in case"
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes
 WHERE relname = 'accounts'
 ORDER BY idx_scan;

--    indexrelname    | idx_scan |  size
-- --------------------+----------+---------
--  idx_middle_name    |        0 | 45 MB    -- never used, pure cost
--  idx_fax_number     |        0 | 45 MB    -- never used, pure cost
--  idx_email          |   184213 | 45 MB    -- heavily used, earning its keep
--  accounts_pkey      |   920481 | 45 MB    -- heavily used, essential

8–9: Two indexes with zero scans, ever — pure write/storage cost with no observed read benefit at all.
10–11: Two heavily-used indexes — clearly earning back their write cost through real query performance.

Output

indexrelname     | idx_scan | size
------------------+----------+-------
idx_middle_name  |        0 | 45 MB
idx_fax_number   |        0 | 45 MB
idx_email        |   184213 | 45 MB
accounts_pkey    |   920481 | 45 MB

Why this works: idx_scan = 0 on two of the four indexes is direct, unambiguous evidence they have never once been used to answer a query, since this instance started — meaning every write to this table has paid their full maintenance cost for a read benefit that has never actually materialized, exactly the harm this concept describes made concrete and measurable.

Indexing a table defensively at design time, before any real query patterns exist

Wrong

sql

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY, email text, first_name text, last_name text,
  middle_name text, fax_number text, notes text, internal_flag boolean
);
CREATE INDEX ON accounts (email);
CREATE INDEX ON accounts (first_name);
CREATE INDEX ON accounts (last_name);
CREATE INDEX ON accounts (middle_name);   -- speculative
CREATE INDEX ON accounts (fax_number);    -- speculative
CREATE INDEX ON accounts (internal_flag); -- speculative, and low-cardinality too

Better

sql

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY, email text, first_name text, last_name text,
  middle_name text, fax_number text, notes text, internal_flag boolean
);
CREATE UNIQUE INDEX ON accounts (email);  -- confirmed: login lookup
-- add first_name/last_name indexes later, IF a real search feature
-- actually needs them -- confirmed by usage, not assumed at design time

What you see: A newly-launched table already carries five or six indexes before a single real query pattern has been observed, and months later pg_stat_user_indexes shows most of them at idx_scan = 0, having paid their full write cost the entire time for nothing.

Why: Indexing decisions made before real query patterns exist are guesses, and guesses are frequently wrong about which columns actually end up being filtered, sorted, or joined on in practice — waiting for confirmed usage (or adding an index reactively once a specific slow query is diagnosed) avoids paying the ongoing cost of indexes that a defensive, upfront guess got wrong.

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

Remember: 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.

Designing composite indexes

Column ordering, the leftmost-prefix rule it enables, and covering/index-only scans as the deepest optimization.

Composite Index Ordering

coreintermediate

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.

Think of it as

Think of a composite index on (a, b, c) as one single sorted list, ordered first by a, and only secondarily by b (within a's ties), and only tertiarily by c. This means the index is extremely good at narrowing down by a first — but has no meaningful global ordering by b or c alone, the same way a phone book sorted by (last_name, first_name) is useless for finding everyone named "John" without checking every single last name. Composite index column order should mirror how queries actually narrow down: the most selective or most commonly-filtered-first column usually belongs first.

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

What we're doing: Create a composite index one way, then the reverse, and show each is efficient for a different query shape via EXPLAIN.

composite_order_matters.sqlsql

CREATE INDEX idx_a_b ON events (event_type, created_at);

EXPLAIN SELECT * FROM events WHERE event_type = 'click' AND created_at > now() - interval '1 day';
-- Index Scan using idx_a_b -- efficient: constrains event_type (leftmost)
--                              first, then created_at within it

EXPLAIN SELECT * FROM events WHERE created_at > now() - interval '1 day';
-- Seq Scan -- idx_a_b is NOT useful here -- no constraint on the
-- leftmost column (event_type) means the index's sort order provides
-- no help narrowing down by created_at alone

1: This index's primary sort key is event_type -- created_at is only secondary, within each event_type value.
3–5: A query constraining event_type first uses the index exactly as it is sorted — efficient.
7–9: A query with no constraint on event_type at all cannot use this index's sort order productively — it would need to check every event_type's created_at range separately, which is not what the index provides.

Output

-- WHERE event_type = ... AND created_at ...: Index Scan using idx_a_b
-- WHERE created_at ... alone: Seq Scan

Why this works: The second query's WHERE clause never mentions event_type, the column this index is primarily sorted by — without that constraint, the index's entries for a given created_at value are scattered across every event_type, not grouped together, which is exactly why the planner correctly judges a sequential scan cheaper than trying to use this particular index.

Creating a composite index in whichever column order feels natural, without considering actual query shapes

Wrong

sql

-- most queries filter by created_at range across ALL event types --
-- but the index was created "alphabetically" by column name instead:
CREATE INDEX idx_events ON events (event_type, created_at);
-- the actual dominant query pattern (created_at alone) gets no benefit

Better

sql

-- match the index's leading column to the actual dominant query pattern:
CREATE INDEX idx_events ON events (created_at, event_type);
-- now WHERE created_at > ... is efficient, and
-- WHERE created_at > ... AND event_type = ... is ALSO still efficient

What you see: A composite index exists on exactly the two columns a slow query filters by, yet EXPLAIN still shows a sequential scan — because the query's actual filter pattern does not constrain the index's leading column, even though it does use one of the two indexed columns.

Why: Which column goes first in a composite index is not an arbitrary stylistic choice — it determines the index's actual sort order, and that sort order must match how real queries narrow down the data for the index to help, which is exactly why "the two columns a query needs are indexed" is not sufficient on its own without also getting their order right.

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

(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 = ?]

Remember: 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.

Leftmost-Prefix Behavior

coreintermediate

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.

Think of it as

This follows directly from composite ordering: since the index is sorted by a first, PostgreSQL can jump straight to the range of entries matching a specific a value (or range) — but within that, entries are only sorted by b, and within THAT, only by c. Skipping a and going straight for b is like trying to use a phone book (sorted by last name, then first name) to find everyone whose first name is "John" — the book's sort order gives no shortcut for that at all, because first names are only locally sorted within each last name, not globally.

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

What we're doing: Confirm the leftmost-prefix rule directly with EXPLAIN: the same index helps a query constraining its first column, and does not help a query constraining only its second.

leftmost_prefix_confirmed.sqlsql

CREATE INDEX idx_customer_status ON orders (customer_id, status);

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Index Scan using idx_customer_status
--   Index Cond: ((customer_id = 42) AND (status = 'pending'::text))

EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- Seq Scan on orders
--   Filter: (status = 'pending'::text)
-- idx_customer_status is NOT used, despite covering the "status" column

1: customer_id is the leftmost column — this is the column the leftmost-prefix rule cares about.
3–5: Constraining customer_id first (leftmost) lets the index narrow the scan directly.
7–9: status is technically IN this index, but constraining it alone, with no constraint on customer_id, cannot use the index — exactly the leftmost-prefix rule in action.

Output

-- customer_id + status: Index Scan
-- status alone: Seq Scan, even though status IS one of the index's columns

Why this works: This is the single most direct, observable demonstration of the leftmost-prefix rule — the exact same index either gets used or does not, based purely on whether the query's WHERE clause constrains the leading column, which is the concrete, practical consequence of a composite index being one sorted structure rather than several independent per-column indexes.

Assuming a composite index helps any query that mentions any of its columns

Wrong

sql

CREATE INDEX idx_customer_status ON orders (customer_id, status);
-- assumption: "this index covers status, so queries filtering by
-- status alone will be fast too"
SELECT * FROM orders WHERE status = 'pending';  -- Seq Scan, not Index Scan

Better

sql

-- if BOTH query shapes (customer_id-led AND status-alone) are common,
-- consider a SECOND index with status leading:
CREATE INDEX idx_status ON orders (status);
-- now WHERE status = 'pending' alone also gets an efficient index scan

What you see: A query that filters only on a composite index's non-leading column performs a full sequential scan despite that column technically being part of an existing index, surprising a developer who reasonably expected "the column is indexed" to be sufficient.

Why: A composite index's later columns are only locally sorted within the leading column's groups, not globally — so "column X is part of an index" is not the same guarantee as "queries filtering only on column X will use that index," which is precisely the distinction the leftmost-prefix rule draws and precisely why it needs to be understood explicitly rather than assumed.

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

Remember: 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.

Covering and Index-Only Scans

coreadvanced

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.

Think of it as

Normally an index only tells PostgreSQL WHERE a matching row lives — it still has to visit the heap to fetch the actual row data and check MVCC visibility. An index-only scan skips that heap visit entirely, for two things to both be true: the index itself already contains every column the query needs (so there's no missing data to fetch), and the visibility map confirms the heap page is “all visible” (so there's no need to double-check MVCC state there). The visibility map is what makes the second condition cheap to check — a tiny bitmap, not a heap read.

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

What we're doing: Compare a plain index (forcing a heap visit for an unindexed column) against a covering index (INCLUDE) that lets the same query become index-only.

covering_index_comparison.sqlsql

CREATE INDEX idx_x_only ON tab (x);
EXPLAIN SELECT x, y FROM tab WHERE x = 'key';
-- Index Scan using idx_x_only on tab
--   (must visit the heap to fetch "y", which is not in this index)

DROP INDEX idx_x_only;
CREATE INDEX idx_x_covering_y ON tab (x) INCLUDE (y);

VACUUM tab;  -- ensures the visibility map is up to date
EXPLAIN SELECT x, y FROM tab WHERE x = 'key';
-- Index Only Scan using idx_x_covering_y on tab
--   (y is available directly from the index -- no heap visit needed)

1–4: y is not in this index at all — an Index Scan still needs to visit the heap for it.
6–7: INCLUDE (y) stores y as extra payload in the index, without making it part of the sort key.
9–11: VACUUM refreshes the visibility map, and now the same query genuinely never touches the heap.

Output

-- before: Index Scan (heap visit required for y)
-- after: Index Only Scan (no heap visit at all)

Why this works: INCLUDE (y) is precisely the tool for turning a query that would otherwise need the heap into one that does not — it adds y as extra data carried alongside each index entry, without changing the index's sort order (y is not a search key, just payload), which is exactly the "covering" part of a covering index.

Expecting an index-only scan on a heavily-updated table without accounting for visibility map churn

Wrong

sql

CREATE INDEX idx_x_covering_y ON tab (x) INCLUDE (y);
-- table receives constant UPDATE/DELETE traffic, autovacuum running normally
EXPLAIN SELECT x, y FROM tab WHERE x = 'key';
-- expectation: always "Index Only Scan"
-- reality: often falls back to a regular Index Scan, because recently
-- modified pages are not marked "all visible" in the visibility map

Better

sql

-- index-only scans deliver their biggest win on tables that are
-- mostly read, or where autovacuum reliably keeps up -- for a
-- heavy-churn table, treat the benefit as partial/best-effort,
-- not a guarantee for every row

What you see: A covering index is created expecting consistent index-only scans, but EXPLAIN sometimes still shows heap fetches for some rows, and the query's overall performance benefit is smaller than a purely index-based explanation would predict.

Why: The visibility map bit for a page is cleared whenever that page is modified, and only set again once VACUUM confirms the page is fully visible to everyone — a table with continuous write activity keeps invalidating that bit faster than it can be re-set, so index-only scans on such a table are a real but partial benefit, not an absolute guarantee, exactly as the documentation itself qualifies it.

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

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

Remember: 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.