PostgreSQL Query Optimization: How We Cut Response Times by 30%

A client came to me with a specific, painful problem: during peak season, key dashboard queries were timing out. Not slow — timing out. Here's the diagnostic process that found the fix, because the process matters more than any single query rewrite.

Start with EXPLAIN ANALYZE, not guesses

The instinct is to add an index and see if it helps. Don't. Run EXPLAIN (ANALYZE, BUFFERS) on the actual slow query first:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE tenant_id = 42 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

In this case, the plan showed a sequential scan over 4 million rows for a query that should have touched a few hundred. That's the actual problem — everything downstream follows from there.

The fix wasn't one index — it was the right composite index

A single-column index on tenant_id didn't help much, because Postgres still had to filter and sort the remaining rows. The fix was a composite index matching the query's actual access pattern:

CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);

Order matters here — tenant_id first because it's the equality filter with the highest selectivity, status second, created_at DESC last so the index itself satisfies the ORDER BY without a separate sort step.

Connection pooling was the other half of it

Indexes fixed the query plan, but timeouts were still happening under load — because the app was opening a new connection per request against a database with a hard connection cap. Adding PgBouncer in transaction-pooling mode let far more concurrent requests share a smaller pool of actual Postgres connections, which is what turned "fast in staging, slow in production" into just "fast."

The result

Query times on the affected endpoints dropped roughly 30%, and — more importantly for the client — the peak-season timeouts stopped entirely. No schema rewrite, no migration to a different database. Just matching the indexes to the real query patterns and fixing how connections were managed.

If your PostgreSQL database is fine most of the time and falls over under load, that's usually not a "we need a bigger database" problem — it's a "something specific is scanning too much" problem, and it's almost always findable with EXPLAIN ANALYZE.