Adding an index without EXPLAIN ANALYZE is debt — you'll feel write amplification next sprint. Common mistakes: B-tree on every FK, GIN on every JSONB, dismissing partial indexes as optional. Yet most list queries filter WHERE status = 'active'; partial indexes shine there.
Keep the decision flow simple: equality → B-tree, full-text or JSONB containment → GIN, time series and append-only logs → BRIN. Consider a slow order list query:
SELECT * FROM orders WHERE tenant_id = $1 AND status = 'open' ORDER BY created_at DESC LIMIT 20;
A composite partial index (tenant_id, status, created_at DESC) WHERE status = 'open' often beats a generic FK index.
Bench at production-like volume with cold cache and concurrent writes. The planner output talks — not the slide deck.