PostgreSQL Is Slow: Finding and Fixing the Queries That Kill Your Server

The first time a database of mine got slow in production, I did what everyone does: blamed the hardware. More RAM, bigger VPS, same problem. The actual culprit was a single query doing a sequential scan over two million rows because a developer had added a WHERE clause on an unindexed column. The fix took one line of SQL and about forty seconds. The server upgrade had cost me three months of higher bills.

PostgreSQL almost never gets slow "just because". It gets slow for findable reasons, and finding them is a skill worth having. Here is the exact workflow I use now, in the order I use it.

Step 1: See what is running right now

Before touching any config, look at the live picture:

SELECT pid, now() - query_start AS duration, state, wait_event_type, left(query, 120) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;

This one query answers half of all "the database is slow" reports. If you see a query that has been running for 20 minutes, you found your suspect. If you see forty copies of the same small query all waiting, that is a different problem — usually lock contention or connection exhaustion, not a slow query at all.

The wait_event_type column is the tell. Lock means queries are queued behind another transaction. IO usually means real disk work. NULL with state active means the query is computing.

Step 2: Make slow queries announce themselves

You cannot optimize what you cannot see. Turn on slow query logging in postgresql.conf:

log_min_duration_statement = 500
log_line_prefix = '%t [%p] %q%u@%d '

Then reload — no restart needed:

sudo -u postgres psql -c 'SELECT pg_reload_conf();'

Now anything taking over half a second lands in the log with the user and database attached. Start at 500 ms and tighten to 100 ms once the obvious offenders are gone. On a busy production box I leave it at 500 permanently; on a small self-hosted app, 100 ms is fine and very educational.

One warning from experience: do not also set log_statement = 'all' while debugging. On a busy server that logs every single query and can fill a disk in hours. log_min_duration_statement is the surgical tool.

Step 3: EXPLAIN ANALYZE, the truth serum

Take the slow query from the log and run it with:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

EXPLAIN alone shows the plan PostgreSQL thinks it will use. EXPLAIN ANALYZE actually executes the query and shows real timings — the difference matters, because bad row estimates are the root of most bad plans.

What to look for, in order:

1. Seq Scan on a big table — the classic missing index. If the table has millions of rows and the filter is selective, an index on the filtered column usually fixes it instantly.
2. Rows estimated vs actual wildly different — if the planner expected 40 rows and got 2,000,000, your statistics are stale. Run ANALYZE tablename; and check again before changing anything else.
3. Nested Loop with a huge inner count — often the same missing-index problem wearing a different hat.
4. External Merge / temp files — sorts or hashes spilling to disk. That is a work_mem problem, not an index problem.

Step 4: The fixes that cover 90% of cases

Add the index. For a query filtering on user_id and sorting by created_at:

CREATE INDEX CONCURRENTLY idx_events_user_created ON events (user_id, created_at DESC);

CONCURRENTLY means the table stays writable while the index builds — on a live database this is not optional. Plain CREATE INDEX takes a write lock and your app will stall for the whole build.

Refresh statistics. After big imports or deletes:

ANALYZE;

Autovacuum usually handles this, but bulk operations routinely outrun it. If a query got slow right after a data load, this is my first guess.

Kill the N+1 pattern in the app. Sometimes the slow part is not one query but ten thousand tiny ones. pg_stat_statements shows this beautifully:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls, mean_exec_time, total_exec_time, left(query, 100)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

If the top entry is a trivial SELECT ... WHERE id = $1 with two million calls, no index will save you — the fix is in the application code, batching or caching those lookups.

When it is not the query at all

Two impostors to rule out before you spend a weekend on indexes:

• Locks. A long-running transaction holding a lock makes every other query look slow. Check pg_stat_activity for something in idle in transaction state for hours — that is usually a forgotten migration console, and killing it fixes everything.
• Connection storms. Hundreds of app connections fighting for shared_buffers looks like database slowness but is really connection management. A pooler like PgBouncer in transaction mode helps more than any tuning here.

My actual checklist when someone says "Postgres is slow"

1. pg_stat_activity — is something running long or waiting on locks?
2. log_min_duration_statement = 500 — catch the offenders in the act
3. EXPLAIN (ANALYZE, BUFFERS) on the worst query — seq scans and bad estimates first
4. CREATE INDEX CONCURRENTLY or ANALYZE — measure again
5. pg_stat_statements — if nothing is individually slow, look at call counts

Most of the time you will have the answer inside fifteen minutes. And please, do the diagnosis before the server upgrade — your wallet will thank you.