PostgreSQL indexes: every kind, and when to use which

An index is a separate data structure that lets PostgreSQL find rows without reading the whole table. Choosing the right one can turn a query from minutes into milliseconds, and choosing badly slows every write for no benefit. This covers each index type in PostgreSQL 18, and the variations on them.

How PostgreSQL stores data

A table is stored in a heap: an unordered set of 8 KB pages. Each row version has a physical address, called a TID or ctid: a page number and a position in it.

An index maps values to TIDs. An index scan finds the TIDs, then fetches the rows from the heap.

Because of MVCC, an update creates a new row version, so old and new versions coexist. Most index entries don’t say whether a row is visible to a transaction, so PostgreSQL usually has to check the heap.

How the planner chooses

An index isn’t always used. The planner estimates the cost of each plan, using statistics, and picks the cheapest.

If a query matches 40% of a table, a sequential scan is often right, because random heap reads are slower than reading in order. So a sequential scan isn’t always a problem.

EXPLAIN (ANALYZE, BUFFERS) shows the real plan and timings:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

Bitmap scans can also combine several indexes, with BitmapAnd and BitmapOr.

B-tree: the default

CREATE INDEX idx_orders_customer ON orders (customer_id);

A B-tree keeps keys sorted in a shallow, balanced tree of pages. Lookups are O(log n), and the tree stays shallow: billions of rows usually need only a few levels.

Handles: =, <, <=, >, >=, BETWEEN, IN, IS NULL, sorting, and LIKE 'abc%' when the prefix is fixed.

Doesn’t help with: LIKE '%abc', or functions applied to the column.

Sorting for free. A query with ORDER BY created_at DESC LIMIT 20 can walk the index and stop after 20 rows, with no sort at all. This is often one of the best uses of an index.

Uniqueness. Only B-tree supports unique indexes.

Deduplication. Since version 13, repeated values are stored compactly, which shrinks indexes on low-cardinality columns.

Limits. An indexed value has to fit in about a third of a page, around 2,700 bytes. Large text needs a hash of it, or an expression index on a prefix.

Use it for almost everything. It’s the right default.

Hash

CREATE INDEX idx_sessions_token ON sessions USING hash (token);

A hash index stores a 32-bit hash of each value, and only supports =.

Good for equality on long values, because it stores only the hash, so the index can be smaller than a B-tree on the same column.

Limits. No range queries, no sorting, no uniqueness, one column only, and no index-only scans. Since version 10, hash indexes are crash-safe. In practice, B-tree is close enough that hash indexes are a niche choice.

GIN: for values containing many items

CREATE INDEX idx_posts_tags ON posts USING gin (tags);
CREATE INDEX idx_docs_body ON docs USING gin (to_tsvector('english', body));

GIN (Generalized Inverted Index) is like the index of a book: each key points to a list of the rows that contain it. It suits columns that hold many values.

Good for:

• Arrays: tags @> ARRAY['postgres'].
• JSONB: data @> '{"status": "active"}'.
• Full-text search: @@ on a tsvector.
• Fuzzy text: trigrams with pg_trgm.

JSONB has two operator classes.

If only containment queries run, jsonb_path_ops is the better choice.

Writes are the cost. One row can add many keys, so inserts are expensive. GIN uses a pending list to batch them (fastupdate), which helps writes, and can make reads slower until it’s flushed.

Limits. No sorting, and no index-only scans. Results need rechecking against the heap in some cases.

GiST: for ranges, geometry and nearest neighbour

CREATE INDEX idx_bookings_period ON bookings USING gist (during);
CREATE INDEX idx_places_location ON places USING gist (location);

GiST is a balanced tree framework, where each entry describes a bounding region, rather than a sort order. Entries can overlap, so a search may follow several branches.

Good for:

• Ranges: overlap (&&) and containment.
• Geometry and geography, with PostGIS.
• Nearest neighbour: ORDER BY location <-> point LIMIT 10.
• Full-text search, smaller and with faster updates than GIN, and slower to read.

Exclusion constraints are a powerful use. This prevents double-booking a room in the database itself:

CREATE TABLE bookings (
  room int,
  during tstzrange,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);

(That needs the btree_gist extension, to use = on room.)

Limits. GiST can be lossy, so matches may need a heap recheck.

SP-GiST: for space that divides cleanly

CREATE INDEX idx_ips ON logs USING spgist (client_ip inet_ops);

SP-GiST is a framework for non-balanced structures like quadtrees, k-d trees and radix trees. It suits data with natural non-overlapping divisions.

Good for: IP addresses, phone-number-like prefixes, and some spatial data. It can be efficient for LIKE 'abc%' style prefix searches.

Limits. One key column only, and it’s less commonly used, so B-tree or GiST is usually tried first.

BRIN: tiny indexes for huge, ordered tables

CREATE INDEX idx_events_time ON events USING brin (created_at);

BRIN (Block Range Index) doesn’t index rows. It stores a summary, typically the minimum and maximum, for each range of pages, 128 by default. A query skips ranges that can’t match.

Good for very large, append-only tables, where values follow physical order, such as logs and time-series data. A BRIN index can be a tiny fraction of a B-tree’s size, and it has minimal write overhead.

The key requirement is correlation. If created_at rises with physical position, each range covers a narrow span. If rows are in random order, every range covers everything, and the index is useless. pg_stats.correlation shows this.

Limits. It’s lossy, so all rows in matching ranges are rechecked. Single-row lookups are slower than a B-tree. New pages aren’t summarized until later, unless autosummarize is on. Heavy updates and deletes hurt correlation.

Comparing the types

Index variations

These are techniques that apply on top of the types.

Multicolumn indexes

CREATE INDEX idx_orders_cust_date ON orders (customer_id, created_at);

Order matters. This helps customer_id = 42, and customer_id = 42 AND created_at > .... It traditionally didn’t help a query on created_at alone, for the same reason a phone book sorted by surname doesn’t help with first names.

A useful rule is to put equality columns first, then range columns.

Skip scan. PostgreSQL 18 adds B-tree skip scan, which can use an index even with no condition on the leading column, by jumping between its distinct values. It works best when the leading column has few distinct values, and the planner chooses it only if it looks cheaper.

Indexes can have up to 32 columns, and B-tree, GiST, GIN and BRIN support multiple key columns.

Partial indexes

CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

A partial index covers only rows matching a condition. If 1% of orders are pending, the index is about 1% of the size, and other rows never touch it on write. The query’s condition has to imply the index’s condition for it to be used.

This is also how to enforce a conditional unique rule:

CREATE UNIQUE INDEX one_active_sub ON subscriptions (user_id) WHERE active;

Expression indexes

CREATE INDEX idx_users_email_lower ON users (lower(email));

An index on an expression helps queries that use exactly that expression, such as WHERE lower(email) = 'a@b.com'. The function has to be IMMUTABLE. So date_trunc on a timestamptz column can’t be indexed directly, because its result depends on the time zone setting.

Covering indexes

CREATE INDEX idx_orders_cust ON orders (customer_id) INCLUDE (total, status);

INCLUDE adds columns to leaf pages, which lets a query run as an index-only scan. Included columns can’t be used for searching, and don’t count towards uniqueness. B-tree, GiST and SP-GiST support this.

Index-only scans and the visibility map

An index-only scan reads no heap pages, as long as PostgreSQL knows all rows on a page are visible. The visibility map tracks that, and VACUUM maintains it. On a heavily updated table, or one that vacuum hasn’t reached, an index-only scan falls back to heap fetches. EXPLAIN shows this as Heap Fetches.

Sort order

CREATE INDEX idx_posts_feed ON posts (user_id, created_at DESC);

A single-column B-tree can be read in either direction. Direction matters for mixed sorts such as ORDER BY a ASC, b DESC. NULLS FIRST and NULLS LAST can also be set.

Operator classes and text

With a non-C locale, a plain B-tree can’t serve LIKE 'abc%'. The text_pattern_ops class fixes that:

CREATE INDEX idx_name_prefix ON users (name text_pattern_ops);

Collation matters too: an index only serves comparisons using its own collation.

Unique indexes and NULLs

By default, NULL values don’t conflict in a unique index, so several rows can have NULL. Since version 15, NULLS NOT DISTINCT changes this.

Indexes from extensions

pg_trgm splits text into three-character pieces, so ILIKE '%term%' can use an index:

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING gin (name gin_trgm_ops);

pgvector offers two approximate index types for embeddings.

The costs

Every index has a price.

Writes. Each index adds work to inserts, and to some updates.

HOT updates. If an update changes no indexed column, and the page has room, PostgreSQL can use a heap-only tuple update, which touches no indexes. An extra index on a frequently changed column disables that, and write load can jump. A lower fillfactor leaves room in pages to make HOT updates more likely.

Space and memory. Indexes use disk, and compete for cache.

Bloat. Dead entries build up on heavily updated tables. REINDEX CONCURRENTLY rebuilds an index without blocking writes.

Common reasons an index isn’t used

Foreign keys

PostgreSQL doesn’t automatically index foreign key columns. Without one, deleting a parent row scans the child table, and joins suffer. It’s a common cause of slow deletes.

Running indexes in production

Build without blocking. A plain CREATE INDEX blocks writes. CREATE INDEX CONCURRENTLY doesn’t, though it takes longer, can’t run in a transaction, and can leave behind an INVALID index if it fails.

Find unused indexes.

SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Check replicas too before dropping one, because statistics are per server.

Partitioned tables. An index on the parent creates one per partition. A unique index has to include the partition key.

Choosing an index

The takeaway

Start with a B-tree, and shape it with partial, expression and covering options. Reach for GIN when one row holds many values, GiST for ranges and geometry, and BRIN for huge ordered tables. Every index slows writes, so add them for real queries, check with EXPLAIN ANALYZE, and remove the ones nobody uses.