Databases3 min read
PostgreSQL indexing: which index, on which column, and when not to
Adding an index is easy. Knowing which one, in which column order, and which existing indexes to delete is the part that changes query times.
EXPLAIN ANALYZE runs a query and reports the plan with actual timings and row counts. Read it inside-out, compare estimated rows to actual rows at each node, and start with the node where that ratio is worst — a bad estimate is what causes the planner to choose the wrong join strategy and produce a slow plan.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open' AND o.created_at > now() - interval '30 days';
-- Nested Loop (cost=0.71..8231.44 rows=12 width=48)
-- (actual time=0.042..842.113 rows=48213 loops=1)
-- -> Index Scan using idx_open_orders on orders o
-- (actual time=0.021..31.204 rows=48213 loops=1)
-- -> Index Scan using customers_pkey on customers c
-- (actual time=0.014..0.015 rows=1 loops=48213)The tell is right there: the planner estimated 12 rows and got 48,213. It chose a nested loop because 12 lookups is cheap; at 48,213 it becomes 48,213 index lookups. Fixing the estimate — usually by running ANALYZE or adding extended statistics — lets it pick a hash join instead.
| Node | Meaning | Concerning when |
|---|---|---|
| Seq Scan | Full table read | On a large table with a selective filter |
| Nested Loop | Row-by-row join | Outer row count is large |
| Hash Join | Build hash, probe | Rarely a problem; watch memory |
| Sort | Ordering rows | Sort Method shows 'external merge' (spilled to disk) |
| Bitmap Heap Scan | Index then fetch pages | Recheck cond removing most rows |
| Materialize | Caching a subplan | Often a sign of a missing index |
-- Requires the pg_stat_statements extension
SELECT calls, round(mean_exec_time::numeric, 2) AS avg_ms,
round(total_exec_time::numeric) AS total_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;Most slow-query incidents we investigate are not database problems; they are application problems visible in the database. The ORM issued 300 queries because a relation was accessed in a loop, or fetched every column of a wide table to render three of them.
Yes — including writes. Wrap data-modifying statements in a transaction you roll back, or use plain EXPLAIN for the estimated plan only.
Within an order of magnitude is usually fine. A 1000× discrepancy almost always means stale statistics, correlated columns needing extended statistics, or a predicate the planner cannot estimate.
PostgreSQL deliberately has no hints. Fix the underlying cause — statistics, indexes, query shape — rather than forcing a plan that will be wrong when the data distribution changes.
Autovacuum handles it for normal workloads. After a bulk load or a large migration, run it manually — the planner is only as good as its statistics.
Harshal Patel
Founder & Lead Engineer, ROVQIX
Harshal leads engineering at ROVQIX, where he has shipped production Next.js, Node.js and PostgreSQL systems for startups, SaaS teams and ecommerce brands. He writes about the trade-offs behind architecture decisions rather than the framework of the week.
ROVQIXdesigns and builds production web platforms — Next.js front ends, Node.js APIs and the infrastructure behind them. Tell us what you're building and we'll scope it with you.
Adding an index is easy. Knowing which one, in which column order, and which existing indexes to delete is the part that changes query times.
Read Committed prevents dirty reads. It does not prevent two people spending the same balance — and that is the bug you will get.
Every PostgreSQL connection is a process. Ten containers with a pool of twenty is two hundred processes, and your database was configured for one hundred.
No spam. Just the occasional case study and craft breakdown.