.tip

Nov 18, 2025 · one of the small things

Fix bad row estimates with CREATE STATISTICS

The planner assumes columns are independent. When city and postcode are correlated, it multiplies their selectivities, estimates far too few rows, and picks a nested loop that takes minutes.

CREATE STATISTICS addr_stats (dependencies) ON city, postcode FROM addresses;
ANALYZE addresses;

Look for plans where the row estimate is off by a factor of ten or more. That gap is nearly always a correlation the planner can't see.

postgresqlperformance

~/tips/today — 2026-10-19

~cat tips/today

Never build an index on a live table without CONCURRENTLY

postgresqlmigrations

A plain CREATE INDEX takes a lock that blocks every write to the table for the whole build. On a big table that means minutes of outage that look like a hung app.

CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);

It's slower and can't run inside a transaction, so you'll have to tell your migration tool. If it fails halfway, it leaves an invalid index behind, so check pg_index.indisvalid and drop it before you retry.

↵ open y yesterday l ls /tips

esc close