beginnerintermediateadvanced

PostgreSQL · B-Tree Indexes

B-Tree Indexes

PostgreSQL's default, general-purpose index type in depth — equality/range/ORDER BY/prefix use cases, composite indexes, partial/expression variants, unique enforcement, and which index types actually support an index-only scan

The defaultCREATE INDEX with no USING clause
'foo%' works, '%foo' doesn'ta prefix pattern is a range in disguise
Only B-tree can be UNIQUENULLs are distinct from each other by default
Always index-only-scan eligibleunlike GIN, which never is

1.The Default B-Tree Index

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.

Key features

CREATE INDEX ON table (column) with no USING clause creates a B-tree index — it is the default method
B-tree indexes fit the most common situations, per PostgreSQL's own documentation, and are the right starting choice absent a specific reason otherwise
A B-tree keeps its entries sorted and self-balances as data is inserted, updated and deleted, keeping search cost consistent rather than degrading over time
Only B-tree indexes can be declared UNIQUE — this is one of several capabilities other index types generally do not offer
The next several concepts in this section (equality/range/ORDER BY/prefix use cases, composite ordering, partial/expression variants, index-only scans) are all facets of this one index type

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

Command

sql

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

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.

2.Equality, Range, ORDER BY and Prefix Use Cases

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).

Key features

Equality (=) and range (<, <=, >=, >) are the core B-tree operators; BETWEEN and IN are built from the same underlying capability
A B-tree index can satisfy ORDER BY directly, avoiding a separate sort step, when the query's ordering matches the index's sort order (including DESC if the index or query specifies it)
LIKE 'foo%' and the regex equivalent '^foo' can use a B-tree index, because a fixed prefix corresponds to a contiguous range in sorted order
LIKE '%foo' cannot use a B-tree index this way — a suffix or unanchored pattern has no corresponding contiguous range to jump to
The C locale (or a text_pattern_ops-based index) is sometimes needed for pattern-matching index use, depending on collation — worth confirming with EXPLAIN rather than assuming

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

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

Command

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

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.

3.Composite B-Tree Indexes

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.

Key features

A composite B-tree index on (a, b, c) is one sorted structure — sorted primarily by a, then b within each a, then c within each (a, b)
Full mechanics (why order matters, the (a,b) vs (b,a) distinction, worked examples) are covered by Index Fundamentals' composite-index-ordering concept — this is the same ground, credited from the B-tree-specific angle
The leftmost-prefix rule (which query shapes can actually use a composite index) is likewise a B-tree-specific consequence of this sort order, covered in Index Fundamentals' leftmost-prefix-behavior concept
Composite indexes are commonly used for queries that filter on one column and sort or filter further on a second, within the results of the first
A composite B-tree index can still support ORDER BY on its leading column(s), the same as a single-column B-tree can, extending naturally to multiple sort keys

Command

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

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.

4.Partial Indexes and Expression Indexes

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.

Key features

A partial index is created with CREATE INDEX ... WHERE condition — only rows satisfying that condition get an entry
A partial index can only be used by a query whose WHERE clause provably implies the index's predicate — usually this means matching it closely, even textually
An expression index is created with CREATE INDEX ... (expression) instead of a plain column name — e.g. (lower(email))
An expression index can only be used by a query that applies the EXACT SAME expression in its WHERE clause, not merely something equivalent
Both reduce index size and write-maintenance cost relative to indexing every row/raw value, at the cost of being usable by a narrower set of queries

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

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

Command

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

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.

5.Unique Indexes

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.

Key features

Only B-tree indexes can be declared UNIQUE
A composite unique index only rejects rows where ALL indexed columns are equal together — it does not enforce uniqueness on any single column alone
By default, multiple NULLs are allowed in a unique column, because NULL is never considered equal to another NULL in this comparison
NULLS NOT DISTINCT (added as an option to CREATE UNIQUE INDEX) makes NULLs count as equal to each other, so only one NULL row would then be allowed
A UNIQUE constraint (ALTER TABLE ... ADD CONSTRAINT ... UNIQUE) and a PRIMARY KEY both work by automatically creating exactly this kind of unique index — never manually create a redundant plain index on the same columns

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

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

Command

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

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.

6.Index-Only Scan Requirements, by Index Type

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.

Key features

B-tree indexes always support index-only scans, given the general requirements (columns present in the index, visibility map confirms the page)
GiST and SP-GiST support index-only scans only for some operator classes, not universally
GIN indexes never support index-only scans, regardless of column coverage or visibility — a structural limitation of how GIN stores its entries
This means switching from a B-tree to a different index type for some other benefit (e.g. GIN for array containment) can silently give up index-only scan eligibility a query previously had
The rest of the mechanism — required column coverage, the visibility map's role, and INCLUDE for building a covering index — is the same regardless of index type and is covered in Index Fundamentals' covering-and-index-only-scans concept

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

Command

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

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.

Spotted a mistake on this page? Tell us.

Read Sheet