PostgreSQL Slow Queries: A Practical Tuning Checklist

When a PostgreSQL database gets slow, the first instinct is to scale up: more CPU, more RAM, a bigger instance class. Sometimes that is the right call. Far more often, the database spends most of its time on a small number of queries that could be made dramatically cheaper — and a bigger server only makes the same waste more expensive.

This is the checklist we work through on a performance engagement, in roughly this order. It is ordered by how often each step turns out to be the real problem, not by how interesting it is.

1. Find the queries that actually cost you time

Don't start with the query someone complained about. Start with where the database spends its time overall. The pg_stat_statements extension aggregates execution statistics for every normalised query. Add it to shared_preload_libraries, run CREATE EXTENSION pg_stat_statements, and sort by total time:

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Two very different problems show up at the top of this list. A query that runs in 4 ms but is called 30 million times a day needs a different fix from a 40-second report that runs twice. total_exec_time tells you which queries matter; mean_exec_time and calls tell you which kind of problem you have.

2. Read the plan with real numbers

For each expensive query, run EXPLAIN (ANALYZE, BUFFERS) against a realistic copy of the data — never against a near-empty staging database, where every plan looks fast. Keep in mind that ANALYZE really executes the statement, so wrap writes in a transaction and roll it back. Look for:

• Row estimate errors. If the planner expected 50 rows and got 500,000, everything built on that estimate — join order, join method, memory use — is probably wrong.
• Sequential scans on large tables inside queries that return only a handful of rows.
• Sorts and hashes spilling to disk. Sort Method: external merge in the output means the operation did not fit in work_mem.
• Many buffers read for few rows returned — a sign the query touches far more data than it needs.

3. Fix indexing — including the indexes you shouldn't have

Most of the large wins come from here. The patterns we find most often:

• Composite indexes in the wrong column order. An index on (created_at, customer_id) does little for WHERE customer_id = $1 ORDER BY created_at DESC; an index on (customer_id, created_at) serves both the filter and the sort.
• Functions on indexed columns. WHERE lower(email) = … cannot use a plain index on email; it needs an expression index on lower(email).
• Missing partial indexes. If almost every query filters on status = 'active', an index defined with that condition is smaller and faster than one covering every row.
• Unused and duplicate indexes. Every index slows down writes and vacuum. Indexes with idx_scan = 0 in pg_stat_user_indexes over a representative period are candidates for removal.

4. Give the planner accurate statistics

Bad row estimates usually come from stale or too coarse statistics. Check when large tables were last analysed, raise the statistics target on skewed columns, and for correlated columns — city and postal code, for example — create extended statistics with CREATE STATISTICS so the planner stops assuming they are independent.

5. Check that autovacuum keeps up

On busy tables the default autovacuum settings are often too conservative. The result is bloat: queries read more pages to return the same rows, and index-only scans degrade because the visibility map falls behind. Watch n_dead_tup in pg_stat_user_tables and tune autovacuum per table for your busiest tables rather than globally.

6. Only then: memory and configuration

Configuration matters, but it is rarely the first fix. The settings that most often need attention:

• shared_buffers — around a quarter of RAM is a common starting point on a dedicated server.
• work_mem — raise it carefully if sorts spill to disk. It is allocated per operation and per connection, so a large value multiplied by hundreds of connections can exhaust memory.
• random_page_cost — on SSD storage, lowering it towards 1.1 helps the planner choose index scans when they really are cheaper.
• Connection count. Hundreds of mostly idle connections cost memory and cause contention. A connection pooler such as PgBouncer usually helps more than any single parameter.

7. Look at the application, not just the database

Some of the most expensive "database problems" are access patterns: N+1 queries generated by an ORM, pagination with large OFFSET values, or loading whole rows when two columns are used. They don't show up as one slow query — they show up as a fast query with an enormous call count in pg_stat_statements.