Database Optimization: How a Real Investigation Goes

"The database is slow" is not a diagnosis. Here is the sequence that turns it into one, in the order you should actually run it.

Step 1: Confirm It Is the Database

Before touching a query, check whether the database is even busy. Connection count against max_connections, active versus idle-in-transaction sessions, and whether you are CPU-bound or waiting on I/O.

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity WHERE state = 'active'
GROUP BY 1,2 ORDER BY 3 DESC;

A pile of idle in transaction sessions is an application bug — someone opened a transaction and went to make coffee — and it blocks vacuum and holds locks. That is a different problem from a slow query and no amount of index work will fix it.

Step 2: Rank by Total Time, Not Worst Case

SELECT calls, round(total_exec_time::numeric) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(100 * total_exec_time / sum(total_exec_time) OVER (), 1) AS pct,
       left(query, 80)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 15;

The pct column is the one that matters. It is common for one statement to account for 30–60 percent of all database time, and it is almost never the one people complain about — it is a fast query called constantly. Fixing the top entry usually beats fixing the ten below it combined.

Step 3: EXPLAIN With BUFFERS

EXPLAIN (ANALYZE, BUFFERS) on the offending statement. Read three things:

• Estimated versus actual rows. A node predicting 12 rows and returning 90,000 means the planner picked a nested loop it never should have. Run ANALYZE tablename; if that does not fix it, the column may need a higher statistics target or an extended statistics object for correlated columns.
• Buffers read. shared read is disk, shared hit is cache. Reading 180,000 blocks to return 40 rows is the shape of a missing index regardless of wall-clock time on a warm cache.
• The node consuming the time. Times are cumulative including children, so subtract to find where the cost actually lives. A Sort spilling to disk (external merge Disk: 84MB) means work_mem is too low for that query, and you can raise it per-session rather than globally.

Step 4: Index, Carefully

Column order in a composite index matters: equality predicates first, then the range or sort column. An index on (status, created_at) serves WHERE status = 'open' ORDER BY created_at; the reverse order does not. A partial index (WHERE deleted_at IS NULL) can be a fraction of the size when the predicate is selective. A covering index with INCLUDE enables an index-only scan, but only if the visibility map is current, which means autovacuum has to be keeping up.

Always build with CREATE INDEX CONCURRENTLY in production — the plain form takes a lock that blocks writes for the whole build, which on a large table is an outage. Concurrent builds take roughly twice as long and can fail, leaving an invalid index you must drop and retry.

Step 5: Check What You Broke

Every index slows writes and consumes space. Before adding one, look for indexes nobody uses:

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE idx_scan < 50 ORDER BY pg_relation_size(indexrelid) DESC;

Unused indexes accumulate for years. Dropping them is usually a bigger write-throughput win than any single index you add. Check statistics have been reset recently enough to be meaningful before trusting idx_scan.

The Fixes That Are Not Indexes

A large share of "slow database" reports are application-shaped. An ORM issuing N+1 queries makes the database look busy while every individual query is fast. Missing pagination pulls 200,000 rows to display 20. SELECT * on a table with a large JSONB column moves megabytes to read one integer. And connection churn without a pooler — PgBouncer in transaction mode — can cost more than the queries, since each Postgres connection is a process with real setup cost.

Measure the Result the Same Way

Reset pg_stat_statements, wait a representative interval, and rerun the same ranking query. If the statement you fixed has dropped out of the top ten and total database time is down, you are done. If a different statement simply moved into first place at the same percentage, you removed a bottleneck and exposed the next one — which is progress, and the loop starts again.