Index Types

Postgres Index Types

Postgres supports multiple index access methods, each optimized for different query patterns. The default CREATE INDEX uses B-tree, but choosing the right access method can make orders-of-magnitude difference in query performance.

-- General syntax
CREATE INDEX idx_name ON table_name USING <access_method> (column);

---

B-tree (default)

CREATE INDEX without a USING clause creates a B-tree. See BTrees for internals (B+ tree variant with linked leaf nodes).

Supports:

• Equality (=)
• Range (<, >, <=, >=, BETWEEN)
• Sorting (ORDER BY)
• Pattern matching with a fixed prefix (LIKE 'foo%', but not LIKE '%foo')
• IS NULL / IS NOT NULL

Multi-column indexes and the leftmost prefix rule

CREATE INDEX idx_orders ON orders (customer_id, created_at, status);

This single index satisfies queries that filter on:

• customer_id alone
• customer_id + created_at
• customer_id + created_at + status

But not queries filtering only on created_at or status — the leftmost column(s) must be present. Think of it as a phone book sorted by last name, then first name: you can look up “Smith” but not efficiently look up everyone named “Alice” across all last names.

Covering indexes with INCLUDE

CREATE INDEX idx_orders_covering ON orders (customer_id)
    INCLUDE (email, total);

The INCLUDE columns are stored in the leaf pages but are not part of the search key. This enables index-only scans — Postgres can answer the query entirely from the index without visiting the heap table.

-- This can be an index-only scan:
EXPLAIN SELECT email, total FROM orders WHERE customer_id = 42;

Tip

INCLUDE columns do not affect the sort order of the index and cannot be used in WHERE clauses for index lookups. They exist purely to avoid heap fetches.

---

Hash

Equality-only. No range support, no sorting, no prefix matching.

CREATE INDEX idx_session_token ON sessions USING hash (token);

See Hash Index for how hash indexes work (hash function, buckets, collision resolution).

Warning

Before Postgres 10, hash indexes were not WAL-logged — they could not survive a crash and were not replicated to standbys. Since Postgres 10 they are crash-safe and fully WAL-logged.

When hash beats B-tree: when the indexed column contains very wide keys (e.g., long text strings or UUIDs represented as text). The hash index stores a fixed-size hash rather than the full key, resulting in a smaller index. For typical key sizes, B-tree is almost always the better choice.

---

GIN (Generalized Inverted Index)

The name confuses people — GIN has nothing to do with geography. It is an inverted index: it maps each element/key to a posting list of row IDs (heap tuple pointers) that contain that element. The same structure that powers search engines like Lucene.

Use cases

Full-text search with tsvector and the @@ operator — see Full-Text Search and ParadeDB for details:

CREATE INDEX idx_docs_fts ON documents USING gin(to_tsvector('english', body));

JSONB containment and existence (@>, ?, ?|, ?&):

CREATE INDEX idx_events_data ON events USING gin(data jsonb_path_ops);

Info

jsonb_path_ops is a specialized GIN operator class for JSONB that only supports the @> (containment) operator but produces a significantly smaller index than the default jsonb_ops. Use jsonb_ops if you need ?, ?|, or ?&.

Array overlap and containment (&&, @>, <@):

CREATE INDEX idx_posts_tags ON posts USING gin(tags);
-- Supports: WHERE tags && ARRAY['rust', 'postgres']
-- Supports: WHERE tags @> ARRAY['rust']

Trigram similarity with the pg_trgm extension — pg_trgm breaks strings into overlapping 3-character sequences called trigrams (e.g. “cat” → ” ca”, “cat”, “at ”), then indexes those trigrams so that fuzzy matches and LIKE '%substring%' can use the index instead of scanning every row (%, similarity(), LIKE '%substring%'):

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);
-- Now supports: WHERE name LIKE '%widget%'  (uses the index!)
-- And: WHERE name % 'widgit'  (fuzzy match)

Performance characteristics

GIN is fast for lookups but slow for updates. When a row is inserted or updated, updating the posting lists is expensive. To amortize this cost, GIN maintains a pending list — new entries are appended to an unsorted staging area and merged into the main index structure later.

-- Control pending list behavior:
CREATE INDEX idx_docs_fts ON documents USING gin(tsv)
    WITH (fastupdate = on);  -- default: on

VACUUM merges the pending list into the main index. Until it does, queries must scan both the main index and the pending list, which degrades read performance on write-heavy tables.

Tip

For write-heavy tables where GIN update cost is a problem, consider GiST as an alternative (faster updates, slower reads).

---

GiST (Generalized Search Tree)

A balanced tree framework that supports custom data types via pluggable strategies. Each internal node stores a bounding predicate that encompasses all entries in its subtree. Searches traverse the tree by checking which bounding predicates are consistent with the query.

Use cases

PostGIS geometry (bounding box overlap &&, distance <->):

CREATE INDEX idx_places_geom ON places USING gist(geom);
-- Supports: WHERE geom && ST_MakeEnvelope(...)
-- Supports: ORDER BY geom <-> ST_MakePoint(-73.99, 40.73)

Range types (overlap &&, containment @>, <@):

CREATE INDEX idx_reservations ON reservations USING gist(during);
-- WHERE during && tstzrange('2026-03-23', '2026-03-24')

tstzrange is a built-in type

Range types (tstzrange, int4range, daterange, etc.) and their GiST operator classes are part of core Postgres since 9.2. No extension required. The btree_gist extension is only needed if you want to combine range types with scalar types in exclusion constraints (e.g., EXCLUDE USING gist (room_id WITH =, during WITH &&) — the = operator on int requires btree_gist).

Full-text search (alternative to GIN):

CREATE INDEX idx_docs_fts_gist ON documents USING gist(tsv);

Trigram similarity (alternative to GIN with pg_trgm):

CREATE INDEX idx_name_trgm_gist ON products USING gist(name gist_trgm_ops);

Exclusion constraints — GiST is the only access method that supports EXCLUDE:

CREATE TABLE reservations (
    room_id int,
    during  tstzrange,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

This enforces that no two reservations for the same room can have overlapping time ranges — a constraint that cannot be expressed with UNIQUE.

GiST vs GIN tradeoff

	GIN	GiST
Read speed	Faster (direct posting list lookup)	Slower (tree traversal + recheck)
Write speed	Slower (posting list maintenance)	Faster (simpler tree updates)
Index size	Larger	Smaller
Best for	Read-heavy workloads	Write-heavy workloads

Why GiST needs recheck (lossy matches). GiST internal nodes store bounding predicates — simplified approximations that encompass all entries in their subtree. For PostGIS, these are bounding boxes. For full-text search, these are compressed signatures where multiple terms can hash to the same bit. Because the approximation is lossy (it can produce false positives), GiST returns candidate rows that might match, and Postgres must recheck each candidate against the actual heap data to filter out false positives. You’ll see Recheck Cond: in EXPLAIN ANALYZE output when this happens. GIN indexes are exact (no false positives) because they store full posting lists per key — no approximation needed.

---

SP-GiST (Space-Partitioned GiST)

For data with natural clustering or partitioning. Unlike GiST (which builds a balanced tree), SP-GiST allows unbalanced partitioning that mirrors how the data naturally clusters.

Internally, SP-GiST supports data structures like:

• Quad-trees — each internal node splits 2D space into four quadrants (NW, NE, SW, SE), recursively partitioning points by location
• k-d trees (k-dimensional trees) — generalise the quad-tree to arbitrary dimensions by alternating the split axis at each level; each node splits the remaining points by their median value on the current axis
• Radix trees (also called Patricia tries) — compressed prefix trees for strings and IP addresses, where shared prefixes are stored once and branches split at the first differing character

-- IP address lookups
CREATE INDEX idx_logs_ip ON access_logs USING spgist(client_ip inet_ops);
 
-- Text prefix searches
CREATE INDEX idx_urls ON pages USING spgist(url text_ops);

Info

SP-GiST shines when the data has an inherent decomposition structure. IP addresses decompose naturally by prefix (10.x → 10.1.x → 10.1.2.x). Geographic points decompose into quadrants. Text decomposes character by character. If your data does not have this structure, GiST is usually a better choice.

---

BRIN (Block Range Index)

A tiny index for naturally ordered data. Instead of indexing every row, BRIN stores summary information (min/max values by default) for each block range — a contiguous group of heap pages (default: 128 pages).

CREATE INDEX idx_events_created ON events USING brin(created_at)
    WITH (pages_per_range = 128);

When to use

BRIN works when the physical order of rows on disk correlates with the indexed column. The classic case is a created_at timestamp on an append-only table — new rows always have later timestamps and are appended to the end of the table.

Size advantage

A BRIN index on a 100GB table might be a few hundred KB, compared to several GB for a B-tree index on the same column. This is because BRIN stores one summary entry per block range rather than one entry per row.

The tradeoff: false positives

If a query asks for WHERE created_at > '2026-03-01', BRIN identifies which block ranges might contain matching rows (based on min/max). Some of those ranges may contain a mix of matching and non-matching rows, resulting in extra blocks being scanned. This is acceptable for large sequential scans but makes BRIN unsuitable for highly selective point lookups.

Warning

BRIN is useless if the physical row order does not correlate with the indexed column. If rows are inserted in random order relative to the indexed column, every block range will span nearly the entire value domain, and the index will exclude nothing. Check correlation with:

SELECT correlation FROM pg_stats
WHERE tablename = 'events' AND attname = 'created_at';

A correlation close to 1.0 or -1.0 means BRIN will work well. Near 0.0 means it will not help.

---

Custom Index Types (Extensions)

Postgres allows extensions to register entirely new index access methods. Notable examples:

• pgvector: hnsw and ivfflat for approximate nearest neighbor vector search
• ParadeDB: bm25 for Tantivy-powered BM25 full-text search
• RUM (rum): a GIN extension developed by Postgres Professional. RUM stores additional per-entry metadata (e.g., document timestamps) alongside the posting lists, allowing ORDER BY on indexed data (e.g., returning full-text search results ordered by recency) without a separate sort step — something plain GIN cannot do

-- pgvector HNSW
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
 
-- ParadeDB BM25
CREATE INDEX ON chunks USING bm25 (content);
 
-- RUM (if installed)
CREATE INDEX ON documents USING rum (tsv rum_tsvector_ops);

---

Decision Guide

Question	Use
Equality + range + ORDER BY?	B-tree
Full-text search (read-heavy)?	GIN
Full-text search (write-heavy)?	GiST
JSONB containment / key existence?	GIN
Geometry / spatial queries?	GiST (PostGIS)
IP addresses, text prefixes?	SP-GiST
Time-series, append-only?	BRIN
Vector similarity?	HNSW or IVFFlat (pgvector)
Equality only, very wide keys?	Hash
No-overlap constraints?	GiST (exclusion constraint)
Fuzzy text matching (LIKE '%..%')?	GIN (pg_trgm)

---

Reading EXPLAIN Output

Understanding which scan type Postgres chooses tells you whether your index is being used effectively.

Index Scan

The index directly locates matching rows and fetches them one by one from the heap:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders on orders  (cost=0.43..8.45 rows=1 width=64)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=4

Info

An Index Only Scan appears when all required columns are in the index (via INCLUDE or as indexed columns). No heap fetch is needed — the fastest scan type.

Bitmap Index Scan

When many rows match, Postgres builds a bitmap of matching pages first, then fetches those pages in physical order (reducing random I/O):

EXPLAIN ANALYZE SELECT * FROM events WHERE created_at > '2026-03-01';

Bitmap Heap Scan on events  (cost=12.45..1024.56 rows=5000 width=128)
  Recheck Cond: (created_at > '2026-03-01')
  -> Bitmap Index Scan on idx_events_created  (cost=0.00..11.20 rows=5000 width=0)
       Index Cond: (created_at > '2026-03-01')

Tip

Bitmap scans appear when the selectivity is too low for an index scan but too high for a sequential scan. They are also the default scan type for GIN indexes — GIN always produces bitmap scans, never plain index scans.

Sequential Scan

No index is used. Every row in the table is examined:

EXPLAIN ANALYZE SELECT * FROM orders WHERE notes LIKE '%urgent%';

Seq Scan on orders  (cost=0.00..25000.00 rows=100 width=64)
  Filter: (notes ~~ '%urgent%')
  Rows Removed by Filter: 99900

Warning

A sequential scan is not always bad — for small tables or queries returning a large fraction of rows, it may be the optimal plan. Postgres’s query planner chooses Seq Scan when it estimates reading the whole table is cheaper than the index overhead. Investigate only when a Seq Scan appears on a large table with a selective filter.

Forcing index usage for debugging

-- Temporarily disable sequential scans to see if an index plan exists:
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_seqscan = on;

This does not guarantee the index plan is faster — it just reveals what alternative plans exist.

See also

• BTrees — B+tree internals (the default index type)
• Hash Index — hash index internals
• pgvector — HNSW and IVFFlat vector indexes
• Full-Text Search and ParadeDB — GIN-powered FTS and the BM25 alternative
• Index Types and Storage Engines — MySQL index type comparison