How EXPLAIN ANALYZE helped us cut PostgreSQL query time by 80% under peak load

#postgres #database #performance #backend

On an SME advisory platform I worked on, used by more than 1,000 business owners, the database CPU spiked every time traffic peaked. Real-time advisory sessions lagged, and the first instinct on the team was "we need a bigger database."

We didn't need one. Rebuilding the indexes around how the application actually queried data cut query time by 80% and removed the CPU spikes. This is the process I used, and it works on most slow-Postgres problems.

1. Find the queries that matter

Don't guess. Under load, find out which queries take up the database's time. The pg_stat_statements extension is the easiest way:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total time, not mean time. A 5 ms query called 200,000 times hurts more than one 2-second report.

2. Read the plan, not just the timing

Take the worst offender and run it with EXPLAIN (ANALYZE, BUFFERS) against realistic data:

EXPLAIN (ANALYZE, BUFFERS)
SELECT s.*
FROM sessions s
WHERE s.advisor_id = 42
  AND s.status = 'scheduled'
ORDER BY s.starts_at
LIMIT 20;

What to look for:

Seq Scan on a large table inside a hot path: Postgres is reading every row because no index fits the query.
Estimated rows far from actual rows: stale statistics, so the planner is choosing blind. Run ANALYZE.
Sort steps on large inputs: an index in the right order often removes them entirely.
High shared read buffers: you are going to disk on every request.

In our case, the hot paths were doing sequential scans. The indexes that existed had been designed around an earlier version of the product, and no longer matched how it filtered and sorted.

3. Design indexes from access patterns

Ask what each hot query filters on, then what it sorts by. For the query above, a composite index in that order does the job:

CREATE INDEX CONCURRENTLY idx_sessions_advisor_status_start
    ON sessions (advisor_id, status, starts_at);

Two rules worth remembering:

Equality columns first, range or sort columns last. (advisor_id, status, starts_at) serves WHERE advisor_id = ? AND status = ? ORDER BY starts_at without a separate sort.
Use CONCURRENTLY in production. It builds the index without blocking writes. It is slower, and it cannot run inside a transaction.

If most queries only touch a subset of rows, such as active records, a partial index is smaller and faster:

CREATE INDEX CONCURRENTLY idx_sessions_scheduled
    ON sessions (advisor_id, starts_at)
    WHERE status = 'scheduled';

4. Verify with the plan again

Re-run EXPLAIN (ANALYZE, BUFFERS). You want to see an Index Scan or Index Only Scan, far fewer buffers read, and the sort step gone. Then compare pg_stat_statements before and after under similar load.

Don't forget the other side of the trade: every index makes writes slower and takes disk space. Look for indexes nobody uses and drop them:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

5. Stop it coming back

Our last step mattered most over time: we added load testing to CI. Performance regressions now fail a build instead of surfacing in production. A query that is fine on a developer laptop can still fall over at peak, and this catches it.

Summary

Measure with pg_stat_statements and sort by total time.
Read plans with EXPLAIN (ANALYZE, BUFFERS).
Build composite or partial indexes from real filter and sort patterns, CONCURRENTLY.
Verify with the plan, and drop indexes nobody uses.
Put load testing in CI.

It's rarely the most exciting fix, but it often beats a bigger instance, and it costs nothing per month.

I'm Trevor Ogina, a senior backend and cloud engineer in Nairobi. I lead teams building Go and Spring Boot platforms on Azure, AWS and GCP. GitHub · LinkedIn

DEV Community