Index Design Basics
Indexes accelerate lookups that match a left-to-right key prefix. Design for real query shapes: WHERE, JOIN, and ORDER BY columns that appear together in PostgreSQL 18.4 plans.
//
Search across all documentation pages
Indexes accelerate lookups that match a left-to-right key prefix. Design for real query shapes: WHERE, JOIN, and ORDER BY columns that appear together in PostgreSQL 18.4 plans.
Quick-reference recipe card - copy-paste ready.
CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at DESC);
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 1001
ORDER BY created_at DESC
LIMIT 50;When to reach for this: Frequent filters or joins show seq scans on large tables with provable selectivity.
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
total numeric(12,2) NOT NULL
);
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);
INSERT INTO orders (customer_id, status, created_at, total)
SELECT
(random() * 5000)::bigint,
CASE WHEN random() < 0.1 THEN 'shipped' ELSE 'pending' END,
now() - (random() * interval '180 days'),
(random() * 200)::numeric(12,2)
FROM generate_series(1, 300000);
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 25;What this demonstrates:
LIMIT satisfied without separate sort node when plan is idealEXPLAIN after ANALYZEINCLUDE columns enable index-only scans for selected payloads.WHERE partial indexes shrink size for stable subsets (status = 'open').INSERT/UPDATE/DELETE with WAL and buffer dirtying.| Query clause | Index hint |
|---|---|
WHERE a = ? AND b > ? | (a, b) |
JOIN ON child.parent_id | Index on parent_id |
ORDER BY created_at DESC | Match sort direction in index |
| Low-selectivity flag | Partial index with predicate |
CREATE INDEX orders_open_customer_idx
ON orders (customer_id, created_at)
WHERE status = 'open';(created_at, customer_id) fails WHERE customer_id = ?. Fix: Most selective equality column leftmost.INCLUDE only needed scalars.CREATE INDEX CONCURRENTLY in migrations.| Alternative | Use When | Don't Use When |
|---|---|---|
| BRIN on time series | Append-only timestamps | Point lookups |
| GIN on JSONB keys | Containment queries | Simple scalar equality |
| Denormalized cache table | Extreme read skew | Strong normalization needs |
No fixed max; watch write rate and autovacuum pressure. Audit unused indexes quarterly.
UNIQUE enforces constraint and provides lookup path. Use UNIQUE when business rule requires.
Yes for backward index scans avoiding sorts on ORDER BY ... DESC.
INCLUDE stores extra columns in leaf pages without search key ordering.
Rare in modern PostgreSQL; B-tree handles most equality cases.
CREATE INDEX ON lower(email) when queries use same expression.
Child join and cascade deletes suffer. Index child FK columns.
Random UUIDs fragment indexes; consider time-ordered IDs for insert-heavy tables.
pg_stat_user_indexes.idx_scan over rolling window.
Multicolumn index order for left-prefix rules.
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.
Reviewed by Chris St. John·Last updated Jul 18, 2026