"The database is slow" is the least useful sentence in backend engineering, and I have said it myself more than once. The database is almost never slow. A specific query is slow, for a specific reason, and there are about six reasons that account for nearly all of them.
Here is the order I actually work through when something is crawling, from the fastest checks to the ones that need real thought.
First, Find the Actual Query
People skip this and start optimising whatever they last touched. Get the data first.
In Postgres, pg_stat_statements is the single most valuable thing you can enable:
SELECT
round(total_exec_time::numeric, 0) AS total_ms,
calls,
round(mean_exec_time::numeric, 2) AS avg_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;
Sort by total time, not average. A query taking 4ms called two million times costs far more than a 900ms report run twice a day, and the 900ms one is the one people notice and try to fix.
Also turn on slow query logging — log_min_duration_statement = 500 — so anything over half a second lands in your logs with its parameters.
The Six Causes, In Order of Frequency
1. A missing index
By a wide margin the most common. Run EXPLAIN (ANALYZE, BUFFERS) and look for Seq Scan on a large table. The tell is a query that was fine for months and then degraded over a few weeks as the table grew — the plan flipped when the numbers crossed a threshold.
2. N+1 queries
Not one slow query but hundreds of fast ones. Fetch 50 orders, then loop and load each customer separately: 51 round trips where one join would do. Individually each is 2ms and nothing looks wrong in pg_stat_statements except a very high call count.
// 51 round trips
const orders = await Order.findAll({ limit: 50 });
for (const o of orders) o.customer = await Customer.findByPk(o.customerId);
// 1
const orders = await Order.findAll({ limit: 50, include: [Customer] });
The way to catch this early is to log query count per request in development. When a page issues 60 queries you see it immediately instead of a year later.
3. Selecting far more than you need
SELECT * on a table with a large JSONB or text column drags that column across the network on every row, even when you only wanted an ID and a name. It also prevents index-only scans. Naming your columns is unfashionable and it genuinely helps.
Same category: no LIMIT. An endpoint that returns "all records" is fine until a customer has 80,000 of them.
4. OFFSET pagination on deep pages
This one surprises people. LIMIT 20 OFFSET 100000 does not skip ahead — the database generates and discards 100,000 rows first. Page 5,000 is genuinely thousands of times slower than page 1.
-- Slow, and gets slower the deeper you go
SELECT * FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 100000;
-- Keyset pagination: constant time at any depth
SELECT * FROM events
WHERE created_at < $1 -- the last row's timestamp from the previous page
ORDER BY created_at DESC
LIMIT 20;
Keyset pagination also fixes the correctness bug in OFFSET — rows shifting between pages when data changes underneath you.
5. Functions applied to indexed columns
WHERE date(created_at) = '2026-09-14' cannot use an index on created_at. Rewrite as a range:
WHERE created_at >= '2026-09-14' AND created_at < '2026-09-15'
Same with lower(email), string casts, and arithmetic on the column. Transform the parameter, never the column.
6. Lock contention
The query itself is fast; it is waiting. Usually a long-running transaction holding a lock, and often a transaction that stayed open across an external API call. Check pg_stat_activity for anything in idle in transaction — that state is where a lot of mysterious slowness lives.
Reading a Plan Without Getting Lost
Plans are read inside-out, and you only need to look at a few things.
The largest actual time, not the estimate. Find the node consuming most of the total and start there.
Rows estimated versus rows actual. If the planner expected 100 and got 400,000, it chose its strategy on bad information. Run ANALYZE on the table and look again.
Nested Loop with a large outer side. Nested loops are excellent for a handful of rows and terrible for a hundred thousand. Seeing one over a big result usually means a bad row estimate.
Rows Removed by Filter. If a node reads 2 million rows and discards 1.99 million, you are filtering after the fact instead of using an index.
Buffers: read versus hit. hit is cache, read is disk. Lots of reads means the working set does not fit in memory — that is a sizing conversation, not a query one.
Things That Are Slow For Non-Query Reasons
Worth ruling these out before rewriting SQL, because no amount of query tuning fixes them.
Connection pool exhaustion. Requests time out while the database looks idle. Something is holding connections — a long transaction, a slow external call inside one, or too many app instances each with a large pool. Serverless without a pooler causes this reliably.
Table bloat. Postgres marks deleted rows dead until vacuum reclaims them. A long-running transaction blocks vacuum, dead tuples pile up, and scans get slower because they read through the corpses. Check n_dead_tup in pg_stat_user_tables.
Analytics on the primary. A heavy report competing with transactional traffic. That is what a read replica is for.
Network round trips. Forty sequential fast queries across a network with 20ms latency is 800ms of waiting, and every query looks fine individually.
What I Actually Do First
Given a "the app is slow" report, my order is: enable pg_stat_statements if it is not on, sort by total time, take the top query, run EXPLAIN (ANALYZE, BUFFERS) on it with real parameters, and look for a sequential scan or a bad row estimate.
That sequence finds the cause in most cases within about fifteen minutes. The fix is usually one index, and I have watched a single well-chosen index take a page from nine seconds to under a hundred milliseconds more than once.
The slow part is not the fixing. It is that most teams never look, and instead upgrade the database instance — which buys headroom and leaves the query exactly as wrong as it was.
