The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Create a B-tree index | CREATE INDEX orders_customer_idx
ON orders (customer_id); | View examples |
| Enforce uniqueness | CREATE UNIQUE INDEX users_email_uidx ON users (email); | View examples |
| Index several columns | CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC); | View examples |
| Include payload columns | CREATE INDEX orders_status_idx
ON orders (status) INCLUDE (total); | View examples |
| Index a subset | CREATE INDEX orders_open_idx
ON orders (created_at)
WHERE status = 'open'; | View examples |
| Index an expression | CREATE INDEX users_lower_email_idx
ON users (lower(email)); | View examples |
| Index composite values with GIN | CREATE INDEX documents_tags_idx
ON documents
USING GIN (tags); | View examples |
| Summarize ordered storage with BRIN | CREATE INDEX events_time_brin_idx
ON events
USING BRIN (occurred_at); | View examples |
| Build without blocking writes | CREATE INDEX CONCURRENTLY orders_customer_idx
ON orders (customer_id); | View examples |
| Drop with less write blocking | DROP INDEX CONCURRENTLY orders_customer_idx; | View examples |
| Inspect estimated plan | EXPLAIN SELECT * FROM orders WHERE customer_id = 42; | View examples |
| Measure execution | EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42; | View examples |
| Return a machine-readable plan | EXPLAIN (FORMAT JSON)
SELECT *
FROM orders
WHERE customer_id = 42; | View examples |
| Refresh planner statistics | ANALYZE orders; | View examples |
| List table indexes | SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders'; | View examples |
An index trades storage and write work for faster access to selected rows. Start from real query predicates and ordering, verify with EXPLAIN, and remove indexes that do not justify their maintenance cost. Plan output is evidence about one database state, not a permanent promise.
Step by step
Detailed examples
Index access paths that queries actually use
B-tree is the default and supports equality, range, and ordered access for compatible operator classes. A unique index also enforces a data rule, although a UNIQUE constraint is often clearer when uniqueness belongs to the schema. Every index consumes space and must be updated by writes, so duplicates and speculative indexes have a cost.
CREATE INDEX orders_customer_idx
ON orders (customer_id);
CREATE UNIQUE INDEX users_email_uidx
ON users (lower(email));Order multicolumn keys from query shape
A multicolumn B-tree is most effective when predicates constrain leading columns. Equality conditions on leading columns can be followed by range or ordering columns. PostgreSQL can sometimes scan or skip within other combinations, but an index beginning with customer_id is not a general substitute for one beginning with created_at.
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);
SELECT order_id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;Match specialized indexes to operators and data distribution
Expression indexes require query expressions the planner can match. Partial indexes save space and update work when a useful subset has a stable predicate, but parameterized or logically different predicates may not imply it. GIN works well for values containing multiple keys, while BRIN is compact and effective only when values correlate with physical block order.
CREATE INDEX orders_open_created_idx
ON orders (created_at)
WHERE status = 'open';
CREATE INDEX users_lower_email_idx
ON users (lower(email));
CREATE INDEX events_time_brin_idx
ON events USING BRIN (occurred_at);Treat index-only scans as an optimization, not a guarantee
INCLUDE stores columns as payload without making them search keys or part of uniqueness. An index-only scan is possible only when all referenced columns are available from the index, and it is profitable when visibility-map state avoids many heap visits. Wide payload columns increase index size and can exceed index tuple limits.
CREATE INDEX orders_status_cover_idx
ON orders (status)
INCLUDE (order_id, total);
SELECT order_id, total
FROM orders
WHERE status = 'open';Use concurrent builds deliberately in production
CREATE INDEX CONCURRENTLY avoids blocking normal inserts, updates, and deletes, but performs more work, takes longer, and cannot run inside a transaction block. A failed concurrent build can leave an invalid index that still adds update overhead. Check completion and validity before depending on it; concurrent drop has its own restrictions and supports one index at a time.
CREATE INDEX CONCURRENTLY orders_customer_idx
ON orders (customer_id);
SELECT i.relname, x.indisvalid, x.indisready
FROM pg_index AS x
JOIN pg_class AS i ON i.oid = x.indexrelid
WHERE i.relname = 'orders_customer_idx';
-- Run separately when removal is intended:
DROP INDEX CONCURRENTLY orders_customer_idx;Read plans from the outside inward
EXPLAIN shows a tree of plan nodes. Costs are planner units, not milliseconds; the first number is startup cost and the second is total cost if all rows are consumed. Estimated rows and width influence join and access choices. A sequential scan can be correct for a large fraction of a table or a small table even when an index exists.
EXPLAIN
SELECT order_id, total
FROM orders
WHERE customer_id = 42;
EXPLAIN (FORMAT JSON)
SELECT order_id, total
FROM orders
WHERE customer_id = 42;Measure safely with EXPLAIN ANALYZE
ANALYZE executes the statement, so using it on INSERT, UPDATE, DELETE, or DDL changes data unless you deliberately contain and roll back the operation. Compare estimated rows with actual rows at each node, multiply per-loop values when interpreting repeated nodes, and use BUFFERS to distinguish cache and I/O behavior. Test with representative parameters and data.
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT order_id, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;Keep planner statistics representative
ANALYZE samples values and records distribution statistics used for estimates. Autovacuum normally runs it automatically, but bulk changes or skewed columns may require attention. A bad estimate does not automatically mean an index is missing; inspect data distribution, predicates, statistics targets, and parameter behavior first.
ANALYZE orders;
SELECT schemaname, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders'
ORDER BY indexname;Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
- PostgreSQL Global Development GroupPostgreSQL: Indexespostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Using EXPLAINpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: CREATE INDEXpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Multicolumn Indexespostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Index-Only Scans and Covering Indexespostgresql.org
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.