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.

Recipe

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.

Working Example

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:

• Composite index matching filter plus sort
• LIMIT satisfied without separate sort node when plan is ideal
• Proof via EXPLAIN after ANALYZE

Deep Dive

How It Works

• B-tree is default: equality on leading columns, then range on next column.
• INCLUDE columns enable index-only scans for selected payloads.
• WHERE partial indexes shrink size for stable subsets (status = 'open').
• Every index taxes INSERT/UPDATE/DELETE with WAL and buffer dirtying.

Design Checklist

SQL Notes

CREATE INDEX orders_open_customer_idx
ON orders (customer_id, created_at)
WHERE status = 'open';

Gotchas

• Indexing every filter column separately - Planner may bitmap combine many weak indexes. Fix: One purposeful composite per hot query.
• Wrong column order - (created_at, customer_id) fails WHERE customer_id = ?. Fix: Most selective equality column leftmost.
• Wide indexes including JSON blobs - Bloated pages, slow vacuum. Fix: INCLUDE only needed scalars.
• Creating index without CONCURRENTLY in prod - Writes block. Fix: CREATE INDEX CONCURRENTLY in migrations.
• Skipping ANALYZE after index - Planner ignores new index. Fix: Analyze table before validation.

Alternatives

FAQs

Related

• Multicolumn Index Order - Leading column rules
• Duplicate & Redundant Indexes - Waste audit
• Seq Scan vs Index Scan - When index wins
• B-tree Indexes - Access path details

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+.