Skip to main content

Indexes

An index is a separate data structure that lets the database find rows by the values of some columns without reading the whole table. The default type is a B-tree: a sorted tree that answers =, <, >, BETWEEN and ORDER BY in logarithmic time. Indexes speed up reads but cost disk space and slow down writes: every INSERT, and every UPDATE of an indexed column, has to update the indexes too. PRIMARY KEY and UNIQUE create an index automatically; foreign key columns and other filtered columns need one created by hand. Whether an index is actually used is shown by EXPLAIN.

Applies to: PostgreSQL 18.

Details​

Kinds of Indexes​

IndexExampleUse for
Single columnCREATE INDEX ON orders (user_id)Filters and joins on one column, foreign keys
CompositeCREATE INDEX ON orders (user_id, created_at)Filter by the first column, sort or filter by the next one
UniqueCREATE UNIQUE INDEX ON users (email)Enforces uniqueness like a UNIQUE constraint; can also be partial or on an expression
PartialCREATE INDEX ON orders (created_at) WHERE status = 'pending'Queries on a small subset of rows; the index stays small
ExpressionCREATE INDEX ON users (lower(email))Queries that filter by the same expression
CoveringCREATE INDEX ON orders (user_id) INCLUDE (total)Index-only scans: the answer is read from the index

Index types other than B-tree:

TypeUse for
GINjsonb (@>), arrays, full-text search
GiSTGeometry, ranges, nearest-neighbor search
BRINHuge tables whose rows are physically ordered, e.g. by time
HashEquality only; rarely better than B-tree

Column Order in a Composite Index​

An index on (user_id, created_at) is sorted by user_id, then by created_at within each user_id, like a phone book sorted by last name, then first name. So it helps:

  • WHERE user_id = 42;
  • WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;
  • but usually not WHERE created_at > … alone. (Since PostgreSQL 18, a skip scan can use it if user_id has few distinct values.)

Put columns compared with = first and the range or sort column last.

When an Index Is Not Used​

  • The column is wrapped in a function: WHERE lower(email) = … needs an expression index on lower(email).
  • LIKE '%text' with a leading wildcard. A plain B-tree also skips LIKE 'text%' unless the database uses the C collation or the index was created with text_pattern_ops.
  • The query returns a large share of the table: reading the table sequentially is cheaper than jumping through the index.
  • The table is small, or the statistics are outdated (ANALYZE updates them).

Reading EXPLAIN​

Plan nodeMeaning
Seq ScanReads the whole table
Index ScanFinds rows through the index, then reads them from the table
Index Only ScanAnswers from the index; visits the table only for pages that VACUUM has not yet marked all-visible
Bitmap Heap ScanCollects matching row locations from the index, then reads them in physical order

EXPLAIN shows the plan and estimates; EXPLAIN ANALYZE also runs the query and shows the actual time and row counts.

Example​

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id integer NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO orders (user_id, status, created_at)
SELECT i % 10000, CASE WHEN i % 100 = 0 THEN 'pending' ELSE 'done' END,
       now() - i * interval '1 minute'
FROM generate_series(1, 200000) AS i;
ANALYZE orders;

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
-- Seq Scan on orders … Rows Removed by Filter: 199980 … Execution Time: 3.5 ms

CREATE INDEX orders_user_id_created_at_idx ON orders (user_id, created_at);

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
-- Bitmap Heap Scan on orders -> Bitmap Index Scan on orders_user_id_created_at_idx
-- … Execution Time: 0.08 ms

EXPLAIN SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;
-- Index Scan Backward using orders_user_id_created_at_idx: no separate sort

Follow-up Questions​

  • Why not index every column? Each index slows down writes, takes space and memory, and must be maintained; unused indexes are pure cost.
  • How do you add an index to a large table in production? CREATE INDEX CONCURRENTLY: it does not block writes, but takes longer and cannot run inside a transaction.
  • What is a clustered index? The table itself is stored as a B-tree ordered by the clustered key: always the primary key in MySQL InnoDB, the primary key by default in SQL Server. PostgreSQL tables are heaps; all indexes are separate, and the one-time CLUSTER command does not keep the order.
  • How do you find slow queries? pg_stat_statements for the most expensive queries, then EXPLAIN ANALYZE on them.

Common Mistakes​

  • Expecting an index on (a, b) to help a query filtered only by b.
  • Forgetting indexes on foreign key columns.
  • Adding an index for a query that returns most of the table.
  • Filtering by lower(column) or column::text with an index on the bare column.

See Also​