A hands-on guide to PostgreSQL indexes: read EXPLAIN ANALYZE like a pro, order composite columns correctly, shrink indexes with partial predicates, and stop over-indexing.

Francis K Njenga
Senior Developer
A query that takes 4 seconds on 2 million rows will take 40 seconds on 20 million — unless the right index exists. Indexing is the highest-leverage performance tool in PostgreSQL, but it is also the most misused: teams add indexes by guesswork, slow down every write, and still run sequential scans on their hottest queries. This guide shows the workflow we use on client databases: measure, design, verify, prune.
EXPLAIN ANALYZE executes the query and reports what actually happened. Add BUFFERS to see cache vs disk reads. The three things to look for:
WHERE — the classic missing-index signal.ANALYZE) or correlated columns.EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'paid'
AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;
/* Look for:
Seq Scan on orders (actual time=0.08..812.4 rows=50 loops=1)
Filter: (status = 'paid' AND created_at >= ...)
Rows Removed by Filter: 1984203
Buffers: shared read=48210 <- disk reads, ouch
*/Seq Scan + 1.9M rows removed by filter = an index is begging to exist.
A composite index on (a, b) is sorted by a first, then b within each a. The rule: equality columns first, range/sort columns last. For the query above, status is an equality filter and created_at is both a range and the sort key — so (status, created_at DESC) lets Postgres walk the index in exactly the order the query wants and stop after 50 rows.
The reverse order (created_at, status) would scan every recent row and filter status afterwards — dramatically worse when 'paid' is a small fraction of orders.
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at DESC);
-- CONCURRENTLY: no write lock on the table (safe in production,
-- but cannot run inside a transaction block)
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'paid'
AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;
/* Index Scan Backward using idx_orders_status_created
(actual time=0.05..0.19 rows=50 loops=1)
Buffers: shared hit=6 */After: Index Scan Backward, ~0.2 ms, a handful of buffer hits.
If 95% of rows are status = 'archived' but every hot query filters status = 'paid', a full index wastes space and write throughput. A partial index stores only the matching subset — often 90%+ smaller, faster to scan, cheaper to maintain:
CREATE INDEX CONCURRENTLY idx_orders_open
ON orders (created_at DESC)
INCLUDE (customer_id, total)
WHERE status IN ('pending', 'paid');
-- The planner uses it only when the query's WHERE implies the predicate:
SELECT customer_id, total, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
-- => Index Only Scan using idx_orders_openINCLUDE columns make it a covering index — Postgres never touches the table (index-only scan).
orders.customer_id, every customer delete/update triggers a full scan of orders. Index every FK.LIKE 'term%' can use a B-tree with text_pattern_ops (or the C collation). Leading-wildcard '%term%' needs trigram: CREATE EXTENSION pg_trgm + a GIN index.@> queries use a GIN index on the column or a specific path.Each INSERT/UPDATE maintains every index on the table. Unused indexes are pure overhead. Find them with pg_stat_user_indexes (stats accumulate since the last reset, so observe over weeks, not minutes):
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
idx_scan AS times_used,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%pkey' -- keep primary keys
ORDER BY pg_relation_size(indexrelid) DESC;
-- Also useful: duplicate/overlapping indexes
-- (an index on (a) is redundant if (a, b) exists)Drop one at a time, watch the plans, keep a rollback script.
pg_stat_statements, not from folkloreEXPLAIN (ANALYZE, BUFFERS) before and after every changeCREATE INDEX CONCURRENTLY in productionidx_scan monthly; drop what nobody usesReal result: on a recent client audit, three targeted indexes (one partial, one composite reorder, one dropped duplicate) took a dashboard endpoint from 3.8 s to 41 ms at p95 — no schema changes, no caching layer, one afternoon.
Database performance is part of our performance optimization service. If your app feels slow as data grows, let's look at the plans together.

Senior Developer
Francis Njenga is an experienced Lead Developer specializing in React, Next.js, and modern JavaScript frameworks, with a strong background in web development.