PostgreSQL Indexing Cheat Sheet for Busy Developers

Most slow PostgreSQL queries come down to a missing or wrong index. Here is a compact cheat sheet.

Start with EXPLAIN

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Look for Seq Scan on large tables and for large differences between estimated and actual rows.

B-tree (the default)

Equality and range queries, sorting:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

Column order matters: put the column you filter by equality first. CONCURRENTLY avoids blocking writes on a busy table.

Partial indexes

Index only the rows you query:

CREATE INDEX CONCURRENTLY orders_pending_idx
  ON orders (created_at) WHERE status = 'pending';

Covering indexes

Let the query be answered from the index alone:

CREATE INDEX CONCURRENTLY orders_customer_cover_idx
  ON orders (customer_id) INCLUDE (total, status);

GIN for JSONB and full-text search

CREATE INDEX CONCURRENTLY events_payload_idx ON events USING GIN (payload);

Don't over-index

Every index slows down writes and uses disk. Check pg_stat_user_indexes for indexes with idx_scan = 0 and drop the ones you never use.