The difference between a slow query and a fast one is usually not the hardware — it's the plan. Here's the playbook I use to take queries from seconds to milliseconds.
Every slow query has a story. The table was small when it was written, the index looked right at the time, or the ORM generated something nobody would write by hand. The fix starts the same way every time: look at what the database actually does, not what you think it does.
“The database doesn't care what you intended. It cares what the plan says.”
Start With EXPLAIN
Before touching anything, run EXPLAIN (ANALYZE, BUFFERS) on the query. The plan tells you where the time goes: a sequential scan on a large table, a nested loop doing 10,000 lookups, a sort spilling to disk. Every one of those has a different fix.
The most common offender in real systems is the N+1: one query for the list, then one query per row. It's invisible in development and catastrophic in production. The fix is usually a JOIN or a batched IN clause — but only after the plan confirms it.
Indexes Are Tradeoffs
An index is a bet that a query pattern will repeat. Composite indexes should match the WHERE + ORDER BY shape. Partial indexes cover hot subsets. Covering indexes eliminate table lookups entirely. But every index slows writes and bloats the table — so the discipline is: measure, add, measure again.
The 80/20 of PostgreSQL performance is: correct join strategy, indexes that match real query patterns, and keeping the planner's statistics fresh. That combination resolves the vast majority of production slow-query tickets.
The Discipline
Set a budget before you start: the query must go from X to Y milliseconds, measured on production-shaped data. Then make one change at a time, re-measure after each, and record what worked. Query tuning is compounding too — every pattern you document makes the next diagnosis faster.