Your slowest ten queries are a twenty-minute report

The claim

Before you scale up the database instance, add a read replica, or rewrite an endpoint, run one query. In almost every under-performing application we are asked to look at, a handful of statements — usually fewer than ten — account for the majority of total database time, and they are visible in a report that takes twenty minutes to enable and read. Buying hardware to make slow queries run faster is paying to do the wrong work at higher speed.

Turn on the one extension that matters

pg_stat_statements ships with Postgres and records execution statistics for every query shape, aggregated across all the times it ran. Enable it:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
# then restart, and once:
CREATE EXTENSION pg_stat_statements;

Let it collect for a representative period — a normal business day is plenty — then ask it the only question that matters at first: where does the time go?

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

Read the report correctly

The column that matters is total_ms, not mean_ms, and this is where intuition misleads people. A query that takes 2 milliseconds but runs four million times a day consumes far more of your database than a five-second report someone runs twice. Optimising the five-second report feels productive and changes nothing; shaving the 2-millisecond query that runs constantly is where the capacity actually comes from.

You are looking for two distinct culprits. High total_ms with high calls and low mean_ms is a query that is individually fine but called too often — frequently the N+1 pattern, where rendering a page of 50 items issues 50 separate queries. High mean_ms is a query that is slow every single time — usually a missing index.

Diagnose the slow-every-time query

Take the worst offender and ask the planner what it is doing:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 8812 ORDER BY created_at DESC;

The word to search for in the output is Seq Scan. A sequential scan on a large table in a query that filters on a specific value means there is no usable index, and the database is reading every row to find the matching ones. The fix is frequently a single line:

CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

That composite index serves both the filter and the sort. Re-run the EXPLAIN and you want to see an Index Scan where the Seq Scan was, with a dramatically lower execution time. One index, one query, often a ten-to-hundred-fold improvement on that statement.

Diagnose the called-too-often query

When the problem is call count rather than per-call cost, the fix is in the application, not the database. The page that issues 50 queries to render 50 rows should issue one query that fetches all 50 rows, or two if it needs related data. This is the single most common performance defect in applications built on an ORM, because the ORM makes the extra queries invisible — each one looks like a simple attribute access in the code. Log query counts per request during development and treat any page issuing more than a handful as a defect to investigate.

The index you should also look for: the unused one

Indexes are not free — every one slows down writes and consumes space and cache. While you have the statistics open, find the indexes nobody uses:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

An index with zero scans since the statistics were last reset is pure overhead — it was added for a query that no longer runs, or never ran. Dropping it speeds up writes and reclaims cache for the indexes that earn their place.

The order of operations

The discipline is to measure before you spend. Enable the extension, collect a day, read the top ten, and act on what the numbers show:

1. Add the missing indexes the EXPLAIN output points to. Cheapest, fastest wins.
2. Fix the N+1 patterns the call counts reveal. Requires code, but no new infrastructure.
3. Drop the unused indexes to recover write throughput.
4. Only now, if the top-ten report is genuinely all necessary work running as fast as it can, consider a larger instance or a replica.

Most teams find that steps one and two remove the pressure that had them shopping for a bigger database, at the cost of an afternoon and no additional monthly spend. The report is free, it is already installed, and it will tell you the truth about where your database's time actually goes — which is almost never where the team assumed before they looked.