beginnerintermediateadvanced

PostgreSQL · Section 21

B-Tree Indexes

Level: intermediate
Read: 34 min
Concepts: 6

PostgreSQL's default, general-purpose B-tree index type in full: what makes it the right default, the equality/range/ORDER BY/prefix use cases it directly supports (and the prefix-vs-suffix LIKE distinction that follows from its sorted structure), composite B-tree indexes as the home of the leftmost-prefix mechanics covered in depth in Index Fundamentals, partial and expression indexes as ways to narrow what an index actually covers, unique indexes and their NULL-handling rules, and which index types — B-tree always, GIN never — actually support an index-only scan.

This section

B-tree indexes - learn more about the heart of PostgreSQLSouthern California Linux Expo

What is true here

CREATE INDEX with no USING clause defaults to a B-tree — the right starting choice for most real query needs.
B-tree covers equality, range, and ORDER BY directly, and a prefix LIKE pattern is a range query in disguise — a suffix pattern is not.
Composite indexes are specifically a B-tree capability — the full column-ordering and leftmost-prefix mechanics live in Index Fundamentals.
Partial and expression indexes narrow what an index covers, at the cost of needing close, provable query-to-definition matching.
Only B-tree indexes can be UNIQUE, and multiple NULLs are allowed by default. B-tree always supports index-only scans; GIN never does.

What you will be able to do

Explain why B-tree is PostgreSQL's default and when a specialized index type is actually warranted instead
Predict which LIKE patterns a B-tree index can and cannot accelerate, and confirm via EXPLAIN's Index Cond
Design and reason about composite, partial, and expression B-tree indexes for a specific query shape
Explain unique index NULL-handling and avoid creating redundant indexes alongside a UNIQUE constraint
Judge whether an index-only scan is even possible for a given index type before assuming it

From the default structure to its edges

The default B-tree

equality, range, ORDER BY, prefix

Composite, partial, expression

narrower matches, narrower coverage

Uniqueness + index-only scans

B-tree-specific guarantees

The default structure

What makes B-tree the default, and the four use cases (equality, range, ORDER BY, prefix) it covers directly.

The Default B-Tree Index

coreintermediate

CREATE INDEX with no method specified creates a B-tree index — PostgreSQL's general-purpose, default index type, built as a balanced tree structure that keeps entries sorted, so any equality or range comparison can find its starting point in roughly logarithmic time rather than scanning everything.

Think of it as

A B-tree is the right default because it handles the overwhelming majority of real query needs from one structure: exact matches, ranges, sorting — all fall naturally out of "keep the data sorted in a tree that's cheap to search and cheap to keep balanced as data changes." Reaching for a different index type (covered in Other Index Types) is the exception, justified by a specific need a B-tree cannot serve well — full-text search, exact equality on an unordered type, or a few other special cases — not the default choice.

sql

CREATE INDEX idx_email ON accounts (email);
-- equivalent, explicit form:
CREATE INDEX idx_email ON accounts USING btree (email);

What we're doing: Confirm that an index created with no explicit method is in fact a B-tree, via pg_indexes / the catalog.

confirm_default_method.sqlsql

CREATE INDEX idx_email ON accounts (email);  -- no USING clause given

SELECT indexname, indexdef FROM pg_indexes
 WHERE tablename = 'accounts' AND indexname = 'idx_email';
-- indexdef: CREATE INDEX idx_email ON public.accounts USING btree (email)
-- confirms "btree" even though it was never explicitly requested

1: No USING clause at all — relying entirely on the default.
3–5: The catalog's own recorded definition shows "USING btree" explicitly, proving that is exactly what was created.

Output

 indexname | indexdef 
-----------+---------------------------------------------------------
 idx_email | CREATE INDEX idx_email ON public.accounts USING btree (email)

Why this works: This directly confirms the documented default rather than assuming it — pg_indexes' indexdef always spells out the actual index method used, which is the authoritative way to check what kind of index any CREATE INDEX statement actually produced, with or without an explicit USING clause.

Assuming a specialized index type is needed without first checking whether the default B-tree already handles the query

Wrong

sql

-- reaching for a specialized index type out of habit or unfamiliarity
-- with what B-tree already covers:
CREATE INDEX idx_created_at ON events USING gist (created_at);
-- GiST offers no benefit here -- a plain range query on a timestamp
-- is squarely a B-tree use case

Better

sql

CREATE INDEX idx_created_at ON events (created_at);  -- plain B-tree
-- fully sufficient for equality, range, and ORDER BY on a timestamp

What you see: A specialized index type is chosen for a query pattern (simple equality, range, or sorting) that a default B-tree would have served identically well, adding unnecessary unfamiliarity and, for some index types, real overhead with no corresponding benefit.

Why: B-tree already covers equality, range comparisons, and ORDER BY — the vast majority of real query needs — so reaching for GiST, GIN, or another specialized type only makes sense when the query pattern genuinely needs something B-tree cannot do (covered in Other Index Types), not as a default habit.

What CREATE INDEX defaults to when USING is omitted

CREATE INDEX idx_email ON accounts (email);

CREATE INDEX idx_email

Index name — the name recorded in pg_indexes

ON accounts

Target table — the table the index is built over

(email)

Indexed column — sorted, balanced entries for this column

Remember: CREATE INDEX with no USING clause creates a B-tree — the default, general-purpose index type covering the overwhelming majority of real query needs (equality, range, ORDER BY). Only B-tree indexes can be UNIQUE. Reach for a specialized index type only when a specific need justifies it, not as a default habit.

Equality, Range, ORDER BY and Prefix Use Cases

coreintermediate

A B-tree index directly supports the operators <, <=, =, >=, > — which covers equality lookups, range queries, and constructs built from them like BETWEEN and IN. Because entries are kept sorted, the same index can also satisfy an ORDER BY without a separate sort step. For pattern matching, it can support LIKE 'prefix%' (anchored at the start) but NOT '%suffix' (anchored at the end, or with a wildcard first).

Think of it as

All four use cases fall out of the same underlying fact: a B-tree's entries are sorted. Equality is finding one point in that sorted order; a range is finding a contiguous span of it; ORDER BY is just reading that span out in the order it's already stored in; and a prefix pattern like 'foo%' is really a range query in disguise ("everything starting with 'foo'" is a contiguous sorted range) — which is exactly why 'foo%' can use the index but '%foo' cannot, since a suffix match has no meaningful position in a structure sorted left-to-right by the string's beginning.

sql

CREATE INDEX idx_name ON accounts (name);

SELECT * FROM accounts WHERE name = 'Alice';           -- equality
SELECT * FROM accounts WHERE name > 'M';                -- range
SELECT * FROM accounts ORDER BY name;                   -- satisfied directly by the index
SELECT * FROM accounts WHERE name LIKE 'Al%';           -- prefix, usable
SELECT * FROM accounts WHERE name LIKE '%ice';          -- suffix, NOT usable

What we're doing: Confirm via EXPLAIN that a B-tree index is used for a prefix LIKE pattern but not for a suffix pattern on the same column.

prefix_vs_suffix.sqlsql

EXPLAIN SELECT * FROM accounts WHERE name LIKE 'Al%';
-- Index Scan using idx_name on accounts
--   Index Cond: ((name >= 'Al'::text) AND (name < 'Am'::text))
--   Filter: (name ~~ 'Al%'::text)

EXPLAIN SELECT * FROM accounts WHERE name LIKE '%ice';
-- Seq Scan on accounts
--   Filter: (name ~~ '%ice'::text)

1–4: PostgreSQL literally rewrites 'Al%' into a range condition (>= 'Al' AND < 'Am') internally -- direct proof it is treating the prefix as a range.
6–7: '%ice' has no corresponding range -- no fixed starting point to search from -- so the planner correctly falls back to scanning everything.

Output

-- 'Al%': Index Scan, rewritten internally as a range condition
-- '%ice': Seq Scan, no usable range exists

Why this works: Seeing the Index Cond rewritten as an explicit range (>= 'Al' AND < 'Am') makes concrete exactly what was described conceptually: a prefix pattern IS a range query once you see how PostgreSQL actually executes it, and a suffix pattern has no equivalent range to express, which is why it cannot benefit the same way.

Expecting a B-tree index to help a suffix or "contains" pattern search

Wrong

sql

CREATE INDEX idx_email ON accounts (email);
SELECT * FROM accounts WHERE email LIKE '%@gmail.com';
-- expectation: the index on email will speed this up
-- reality: Seq Scan -- a suffix pattern gives the B-tree no range to use

Better

sql

-- for suffix/contains matching, a different tool is needed --
-- e.g. a trigram index (pg_trgm extension, GIN-based), covered in
-- Full-Text Search / Other Index Types, or storing the reversed
-- string in a separate indexed column for suffix-specific lookups

What you see: A query filtering by email domain (a suffix pattern) remains slow despite an index existing on the email column, with EXPLAIN showing a sequential scan regardless.

Why: A B-tree's sort order only gives it a usable starting point for patterns anchored at the beginning of the string — a suffix pattern needs a fundamentally different indexing approach (like a trigram index) that this concept's B-tree does not provide, which is worth recognizing explicitly rather than assuming any index on the column will help any pattern on it.

What a B-tree can and cannot use its sort order for

usable by the indexnot usable by the index

no fixed starting pointhas a fixed starting point

name = 'Alice': equality — one point in sorted order
name > 'M': range — a contiguous span
ORDER BY name: reads the span out already sorted
LIKE 'Al%': prefix — a range in disguise
LIKE '%ice': suffix — no fixed starting point to search from

What each use case relies on

What each use case relies on
Use case	Relies on
Equality (=)	finding one point in sorted order
Range (<, BETWEEN)	finding a contiguous span in sorted order
ORDER BY	reading the span out already in that sorted order
Prefix (LIKE 'foo%')	a fixed prefix is itself a contiguous range

Remember: B-tree covers equality, range, and ORDER BY directly, all from the same sorted structure. A prefix pattern (LIKE 'foo%') is really a range query in disguise and CAN use the index; a suffix or unanchored pattern (LIKE '%foo') has no corresponding range and CANNOT — confirm with EXPLAIN's Index Cond rather than assuming.

Narrowing what it covers

Composite indexes, and partial/expression variants that cover a deliberately narrower slice of the data.

Composite B-Tree Indexes

standardintermediate

A composite (multicolumn) index is specifically a B-tree capability — GIN, GiST, hash and BRIN all handle multiple columns differently or not at all, but a B-tree naturally extends its single-column sorted-order idea to multiple columns, sorted first by the leading column, then by the next within ties, and so on. The mechanics of that ordering — and the leftmost-prefix rule it implies — are covered in full depth in Index Fundamentals, since they apply to composite B-tree indexes specifically, not to indexes in general.

Think of it as

It's worth being explicit that "composite index" and "B-tree" are tied together in practice: when this roadmap (or any PostgreSQL discussion) says "composite index" without qualification, it almost always means a composite B-tree index, because that is the index type where multi-column sort order is the natural, primary mechanism. Other index types can sometimes index multiple columns too, but the ordering semantics that make composite B-tree indexes so useful — the leftmost-prefix rule — are a B-tree-specific property.

sql

-- explicitly a B-tree, though the default method would produce this anyway
CREATE INDEX idx_orders_customer_date ON orders USING btree (customer_id, created_at);

What we're doing: Confirm a composite index defaults to btree just like a single-column one, and that it is usable for a query matching its leading columns.

composite_is_btree.sqlsql

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

SELECT indexdef FROM pg_indexes WHERE indexname = 'idx_orders_customer_date';
-- CREATE INDEX idx_orders_customer_date ON public.orders USING btree (customer_id, created_at)

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;
-- Index Scan using idx_orders_customer_date -- both the filter AND the
-- ORDER BY are satisfied by this one composite B-tree index

3–4: Confirms the composite index defaults to 'btree', same as any single-column index would.
6–7: One index satisfies both the equality filter (customer_id) and the sort (created_at) — a genuine composite B-tree win.

Output

-- indexdef confirms "USING btree"
-- EXPLAIN confirms one Index Scan handles both the filter and the sort

Why this works: This is the concrete payoff of a composite B-tree index over two separate single-column indexes: because the index is already sorted by (customer_id, created_at), a query that filters by customer_id and sorts by created_at gets BOTH the filtering and the sorting from one structure, without a separate sort step — an outcome only achievable because this is a B-tree, not because it happens to cover two columns.

Creating two separate single-column indexes instead of one composite B-tree index, expecting equivalent performance

Wrong

sql

CREATE INDEX idx_customer ON orders (customer_id);
CREATE INDEX idx_date ON orders (created_at);
-- expectation: the planner will combine these two indexes efficiently
-- for "WHERE customer_id = 42 ORDER BY created_at"

Better

sql

CREATE INDEX idx_customer_date ON orders (customer_id, created_at);
-- one composite B-tree index, already sorted exactly the way the
-- query needs -- no separate sort step, no need to combine two indexes

What you see: A query filtering on one column and sorting by another performs worse than expected with two separate single-column indexes, showing an extra Sort node in EXPLAIN that a single composite index would have avoided.

Why: Two single-column B-tree indexes can be combined by the planner (via a BitmapAnd, for instance), but that combination does not preserve a useful sort order the way one composite index — already sorted exactly as the query needs — naturally does, which is why a composite index is often the better choice specifically when a query both filters and sorts.

Remember: A composite index is specifically a B-tree capability — sorted by its leading column, then the next within ties. The full mechanics (why column order matters, the leftmost-prefix rule) are the same ground as Index Fundamentals' composite-index-ordering and leftmost-prefix-behavior concepts; this is that capability's home within the B-tree-specific context.

Partial Indexes and Expression Indexes

coreintermediate

A partial index covers only a SUBSET of a table's rows, selected by a WHERE clause on the CREATE INDEX statement itself — smaller, cheaper to maintain, and only usable by queries whose own WHERE clause matches the same condition. An expression index indexes the RESULT of a function or expression applied to a column (like lower(email)) rather than the raw column value — usable only by queries that apply the exact same expression in their WHERE clause.

Think of it as

Both variants exist to make the index match the query more precisely than "every row, raw column value" would. A partial index says "I only care about this slice of rows" — useful when most queries only ever touch that slice (active orders, not billed ones), so indexing the rest is pure waste. An expression index says "I only care about this transformed VIEW of the value" — useful when queries filter not on the raw column but on something derived from it (a case-insensitive email match), since a plain index on the raw column cannot help a query that transforms the value before comparing.

sql

-- partial: only unbilled orders get an entry
CREATE INDEX idx_unbilled ON orders (order_nr) WHERE billed IS NOT TRUE;

-- expression: indexes lower(email), not email itself
CREATE INDEX idx_lower_email ON accounts (lower(email));
SELECT * FROM accounts WHERE lower(email) = 'alice@example.com';  -- uses it

What we're doing: Show both an unmatched partial-index query and an unmatched expression-index query falling back to a sequential scan, confirming the exact-match requirement for each.

partial_and_expression_exact_match.sqlsql

CREATE INDEX idx_unbilled ON orders (order_nr) WHERE billed IS NOT TRUE;
EXPLAIN SELECT * FROM orders WHERE billed IS NOT TRUE AND order_nr < 100;
-- Index Scan using idx_unbilled -- predicate matches exactly

EXPLAIN SELECT * FROM orders WHERE order_nr < 100;
-- Seq Scan -- no "billed IS NOT TRUE" in this query's WHERE clause at
-- all, so the planner cannot prove the partial index covers every
-- matching row

CREATE INDEX idx_lower_email ON accounts (lower(email));
EXPLAIN SELECT * FROM accounts WHERE email = 'alice@example.com';
-- Seq Scan -- filters on the RAW email column, not lower(email) --
-- the expression index does not match this query at all

2–3: This query's WHERE clause exactly matches the partial index's predicate — usable.
5–8: No predicate on billed at all — the planner has no way to know every matching row is covered, so it cannot safely use the partial index.
10–12: The expression index only helps a query filtering on lower(email) specifically — filtering on the raw email column does not match, even though logically related.

Output

-- matching predicate: Index Scan
-- non-matching predicate: Seq Scan
-- raw column filter vs expression index: Seq Scan

Why this works: Both partial and expression indexes require the query to match the index's own definition closely, not merely be "logically related" to it — the planner needs a provable guarantee that the index actually covers what the query is asking for, and that proof generally requires the query's condition to textually or provably imply the index's predicate/expression.

Creating an expression index but querying the raw column instead of the expression

Wrong

sql

CREATE INDEX idx_lower_email ON accounts (lower(email));
-- application code, unaware of the index's exact definition:
SELECT * FROM accounts WHERE email = 'Alice@Example.com';
-- Seq Scan -- filters on the raw column, never touches lower(email)

Better

sql

CREATE INDEX idx_lower_email ON accounts (lower(email));
-- query must apply the SAME expression to use the index:
SELECT * FROM accounts WHERE lower(email) = lower('Alice@Example.com');
-- Index Scan using idx_lower_email

What you see: An expression index created specifically to speed up case-insensitive email lookups is never actually used, because the application queries the raw email column directly instead of wrapping it in the same lower() call the index was built with.

Why: An expression index is indexed on the OUTPUT of the expression, not the raw column — a query has to apply that exact same expression for the planner to recognize the match, which means the index and every query meant to use it need to agree, explicitly, on the exact expression used.

Two ways to narrow what an index covers

Partial — WHERE on CREATE INDEX

+Narrows which ROWS are indexed
+CREATE INDEX ... WHERE billed IS NOT TRUE
+Usable only if the query's WHERE implies the same predicate

Expression — fn(col) as the key

−Narrows what VALUE is indexed
−CREATE INDEX ... (lower(email))
−Usable only by a query applying the exact same expression

Partial vs expression indexes

Partial vs expression indexes
Kind	What it narrows	Usable by
Partial (WHERE ...)	which ROWS are indexed	queries whose WHERE implies the same predicate
Expression ((fn(col)))	what VALUE is indexed	queries applying the exact same expression

Remember: A partial index (WHERE ...) covers only a subset of rows — usable only by queries whose own WHERE clause provably implies the same predicate. An expression index ((fn(col))) indexes a transformed value — usable only by queries applying the exact same expression. Both need close, provable matching between the index definition and the query, not just logical relatedness.

B-tree-specific guarantees

Two capabilities that follow specifically from being a B-tree: uniqueness enforcement, and always-eligible index-only scans.

Unique Indexes

coreintermediate

A UNIQUE index (only B-trees can be declared unique) rejects any INSERT or UPDATE that would create two rows with equal values across all of its indexed columns. By default, multiple NULLs are allowed even in a unique column — NULL is never considered equal to another NULL — unless the index is declared with NULLS NOT DISTINCT. A UNIQUE constraint on a table is implemented, under the hood, as exactly this kind of index.

Think of it as

A unique index does double duty: it is both an ordinary B-tree (usable for the same equality/range/ORDER BY purposes as any other) AND an enforcement mechanism that makes duplicate values impossible to insert, not just discouraged. This is why PostgreSQL automatically creates a unique index whenever a UNIQUE constraint or PRIMARY KEY is declared — the constraint's actual enforcement IS an index doing its normal duplicate-detection job, not a separate mechanism layered on top.

sql

CREATE UNIQUE INDEX idx_email_unique ON accounts (email);
-- multiple NULL emails ARE allowed by default

CREATE UNIQUE INDEX idx_email_unique_strict ON accounts (email) NULLS NOT DISTINCT;
-- only ONE NULL email now allowed

What we're doing: Demonstrate the default NULL-permissive behavior of a unique index, then show NULLS NOT DISTINCT changing it.

unique_index_null_behavior.sqlsql

CREATE TABLE contacts (id SERIAL PRIMARY KEY, backup_email text);
CREATE UNIQUE INDEX idx_backup_email ON contacts (backup_email);

INSERT INTO contacts (backup_email) VALUES (NULL);
INSERT INTO contacts (backup_email) VALUES (NULL);
-- BOTH succeed -- NULL is never considered equal to NULL by default

DROP INDEX idx_backup_email;
CREATE UNIQUE INDEX idx_backup_email ON contacts (backup_email) NULLS NOT DISTINCT;

INSERT INTO contacts (backup_email) VALUES (NULL);
-- with one NULL already present, this SECOND one now fails:
-- ERROR: duplicate key value violates unique constraint

4–6: Two NULL values, both accepted — the default, permissive behavior.
9–9: NULLS NOT DISTINCT changes the rule: NULLs now count as equal to each other, for uniqueness purposes.
11–13: The same operation that succeeded under the default now correctly fails.

Output

INSERT 0 1
INSERT 0 1

(after NULLS NOT DISTINCT)

ERROR:  duplicate key value violates unique constraint "idx_backup_email"

Why this works: The default behavior — NULL never equals NULL, even for uniqueness purposes — matches NULL's general SQL semantics as "unknown" rather than a specific value, which is why "multiple unknowns" does not count as "multiple duplicates." NULLS NOT DISTINCT is the explicit override for the less common case where an application specifically wants to treat "no value" as a single, deduplicated state.

Manually creating a plain index on a column that already has a UNIQUE constraint

Wrong

sql

ALTER TABLE accounts ADD CONSTRAINT accounts_email_unique UNIQUE (email);
-- this already creates a unique index automatically --
CREATE INDEX idx_email ON accounts (email);  -- REDUNDANT --
-- a second, plain index on the exact same column, paying full write
-- cost for zero additional benefit

Better

sql

ALTER TABLE accounts ADD CONSTRAINT accounts_email_unique UNIQUE (email);
-- the automatically-created unique index already serves BOTH purposes:
-- enforcing uniqueness AND speeding up lookups on email --
-- no separate index needed

What you see: A table carries two indexes on the exact same column — one from a UNIQUE constraint, one manually added — doubling the write/storage cost of that column's indexing for zero additional read benefit, since both serve identical lookup patterns.

Why: A UNIQUE constraint's enforcement mechanism IS a unique index — it already provides everything a plain index on the same column would provide (fast equality/range lookup) plus the uniqueness guarantee, which is exactly why adding a second, separate plain index on the same column duplicates work the constraint's own index already does for free.

A UNIQUE constraint IS a unique index, under the hood

ADD CONSTRAINT ... UNIQUE

declared on the table

unique B-tree index created

automatically, under the hood

serves both jobs

enforcement AND fast lookup — for free

Unique index NULL behavior

Unique index NULL behavior
Declaration	Multiple NULLs allowed?
UNIQUE (default)	yes — NULL is never equal to NULL
UNIQUE ... NULLS NOT DISTINCT	no — only one NULL row permitted

Remember: Only B-tree indexes can be UNIQUE. A composite unique index rejects duplicates only when ALL its columns match together. Multiple NULLs are allowed by default (NULL never equals NULL) — use NULLS NOT DISTINCT to change that. A UNIQUE constraint IS a unique index under the hood — never add a redundant plain index on the same columns.

Index-Only Scan Requirements, by Index Type

standardadvanced

The general mechanism behind an index-only scan — needing every referenced column in the index, plus a visibility-map-confirmed page — is covered in full in Index Fundamentals. The piece worth calling out specifically here: B-tree indexes ALWAYS support index-only scans (given those two conditions), which is not true of every index type — GIN indexes, for instance, never support them at all, regardless of how the query is written.

Think of it as

Index-only scan support is a property of the INDEX TYPE, not something every index automatically gets just by satisfying the column/visibility requirements. B-tree earns this support because it physically stores the actual indexed values it was built on — there is nothing to "reconstruct." A B-tree is therefore the safe, default assumption for index-only scan eligibility; the other index types need to be checked individually (some support it partially, GIN never does), which is exactly why this fact belongs specifically in the B-tree section rather than being assumed universal.

sql

-- B-tree: eligible for index-only scan
CREATE INDEX idx_btree ON tab USING btree (x) INCLUDE (y);

-- GIN: NEVER eligible, no matter how the query is written
CREATE INDEX idx_gin ON tab USING gin (tags);
SELECT tags FROM tab WHERE tags @> ARRAY['urgent'];  -- always visits the heap

What we're doing: Confirm a B-tree index qualifies for an index-only scan while an otherwise-similar GIN index on the same table never does, even for a query needing only indexed data.

btree_vs_gin_index_only.sqlsql

CREATE INDEX idx_btree_status ON orders (status);
VACUUM orders;
EXPLAIN SELECT status FROM orders WHERE status = 'pending';
-- Index Only Scan using idx_btree_status

CREATE INDEX idx_gin_tags ON orders USING gin (tags);
VACUUM orders;
EXPLAIN SELECT tags FROM orders WHERE tags @> ARRAY['urgent'];
-- Bitmap Heap Scan on orders
--   ->  Bitmap Index Scan on idx_gin_tags
-- NEVER "Index Only Scan" -- GIN cannot support it, regardless of
-- VACUUM, column coverage, or anything else

3–4: The B-tree index qualifies cleanly, exactly as the general mechanism predicts.
8–11: The GIN index CANNOT qualify -- this is a structural limitation of GIN itself, not something fixable by satisfying the usual conditions more thoroughly.

Output

-- B-tree: Index Only Scan
-- GIN: Bitmap Heap Scan, always -- never Index Only, structurally

Why this works: This makes the index-type-specific limitation concrete: both queries only need a column that IS present in their respective index, and both tables were freshly VACUUMed, yet only the B-tree query achieves an index-only scan — proving the gap is about GIN's own structure, not anything fixable by satisfying the general requirements more carefully.

Switching a column from a B-tree to a GIN index for a new capability, without noticing the loss of index-only scan eligibility for existing queries

Wrong

sql

-- originally: a B-tree index, and an existing query relies on
-- its index-only scan for good performance
CREATE INDEX idx_tags ON orders (tags);  -- B-tree, array equality only

-- later: switched to GIN to support containment queries (@>)
DROP INDEX idx_tags;
CREATE INDEX idx_tags ON orders USING gin (tags);
-- the EXISTING equality query loses its index-only scan silently --
-- GIN cannot provide one, ever

Better

sql

-- keep BOTH if both capabilities and the index-only scan benefit matter:
CREATE INDEX idx_tags_btree ON orders (tags);        -- keeps index-only scan eligibility
CREATE INDEX idx_tags_gin ON orders USING gin (tags); -- adds containment support
-- a real storage/write trade-off, made deliberately rather than by accident

What you see: A previously-fast query that used to run as an index-only scan quietly starts visiting the heap on every execution after an index was switched from B-tree to GIN for an unrelated reason, with no error and no obvious signal beyond a changed EXPLAIN plan.

Why: GIN's inability to support index-only scans is structural, not a configuration issue — switching an index's type for one benefit (containment queries) can silently cost a completely different benefit (index-only scans) that was riding on the same index, which is exactly the kind of trade-off worth checking explicitly via EXPLAIN before and after, rather than assuming.

Index-only scan support, by index type

Index-only scan support, by index type
Index type	Supports index-only scans
B-tree	always (given the general requirements)
GiST / SP-GiST	only for some operator classes
GIN	never

Remember: B-tree indexes always support index-only scans, given the general requirements (columns in the index, visibility map confirms the page) covered in Index Fundamentals. GiST/SP-GiST support it only for some operator classes; GIN never does, structurally. Switching an index's type can silently cost index-only scan eligibility a query relied on — verify with EXPLAIN.