Indexes

Auxiliary structures that let PostgreSQL find rows without reading the whole table.

Syntax

CREATE INDEX [IF NOT EXISTS] name ON table (col [, col2]);
CREATE UNIQUE INDEX name ON table (col);
CREATE INDEX name ON table (col) WHERE condition;      -- partial
CREATE INDEX name ON table ((lower(email)));           -- expression
CREATE INDEX name ON table USING brin (created_at);    -- BRIN
CREATE INDEX name ON table (col) INCLUDE (other_col);  -- covering
DROP INDEX [IF EXISTS] name;

The default B-tree index serves =, <, >, BETWEEN, IN, IS NULL, ORDER BY and prefix LIKE 'abc%' (with the C collation or a text_pattern_ops index).
Selectivity matters: an index helps when the query returns a small fraction of rows. For a value that matches most of the table, a sequential scan is cheaper and the planner will (correctly) ignore the index.
Composite (a, b): used for conditions on a, or a and b. Since PostgreSQL 18, B-tree skip scan can also use it for conditions on b alone when a has few distinct values. Put equality columns first, range columns last.
Partial indexes cover only rows matching a WHERE (e.g. status = 'pending'): small and fast.
Expression indexes match queries that use exactly the same expression (lower(email)).
BRIN: tiny index for huge tables whose values follow physical order (timestamps of append-only data).
Primary keys and unique constraints create indexes automatically; foreign keys do not.
Every index slows down writes and takes space. Run ANALYZE after big data changes so estimates are accurate.
Functions on the column (WHERE date(created_at) = ..., WHERE lower(email) = ...) prevent use of a plain index on that column.

Examples

Before and after an index (modifies schema) perf

EXPLAIN SELECT * FROM users WHERE email = 'user4242@example.com';

CREATE INDEX IF NOT EXISTS users_email_idx ON users (email);

EXPLAIN SELECT * FROM users WHERE email = 'user4242@example.com';

The first plan is a Seq Scan on users; the second uses users_email_idx.

Partial index for a rare status (modifies schema) perf

CREATE INDEX IF NOT EXISTS orders_pending_idx ON orders (created_at) WHERE status = 'pending';

EXPLAIN SELECT order_id, created_at
FROM orders
WHERE status = 'pending' AND created_at >= '2025-06-01'
ORDER BY created_at
LIMIT 20;

The index only contains the ~3% pending orders, already sorted by created_at.

List indexes perf

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

pg_indexes shows each index with its CREATE INDEX definition.

Pitfalls

Indexing low-selectivity columns (booleans, a status where one value is 95%) and expecting speedups.
Wrapping the indexed column in a function or cast in WHERE.
Leading wildcard LIKE '%abc' (a B-tree cannot help).
Creating many overlapping indexes (slower writes, no read benefit).
Forgetting that foreign-key columns are not indexed automatically.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: Clustered vs nonclustered indexes; filtered indexes instead of partial; INCLUDE columns.
MySQL: InnoDB tables are clustered on the primary key; functional indexes since 8.0.13; no partial indexes.
BigQuery: No traditional indexes; use partitioning and clustering.
Snowflake: No user-created indexes on standard tables; micro-partition pruning and optional clustering keys.