Debugging Slow Queries

Step 1 — Find it

You can't fix what you can't see. The standard tool is the pg_stat_statements extension, which aggregates every query's total time, call count, and mean latency — so you can rank queries by total impact (a fast query run a million times often beats a slow one run twice). In production that's your starting point: ORDER BY total_exec_time DESC.

Step 2 — Read the plan

Once you know which query, EXPLAIN (ANALYZE, BUFFERS) tells you why. It runs the query and shows the plan tree with estimated vs actual rows, per-node time, and buffer reads. Run it below — the tree highlights the node that spent the most time on itself (the bottleneck), and flags any node whose row estimate was wildly off.

DROP TABLE IF EXISTS events;
CREATE TABLE events (id int primary key, user_email text, kind text, at date);
INSERT INTO events
SELECT g,
     'User' || (g % 5000) || '@Example.com',
     (ARRAY['click','view','purchase','signup'])[1 + (g % 4)],
     date '2024-01-01' + (g % 365)
FROM generate_series(1, 80000) g;
ANALYZE events;
SELECT count(*) FROM events;

SELECT * FROM events WHERE lower(user_email) = 'user42@example.com'

That's a Seq Scan reading all 80,000 rows to find a handful — the telltale sign of a missing (or unusable) index. The −N filtered on that node is Rows Removed by Filter — all the rows Postgres read and then threw away.

Step 3 — Fix the cause

The filter is lower(user_email) = …. A normal index on user_email can't help, because the query indexes the result of a function, not the column. The fix is an expression index on exactly that expression:

CREATE INDEX IF NOT EXISTS events_lower_email ON events (lower(user_email));
EXPLAIN SELECT * FROM events WHERE lower(user_email) = 'user42@example.com';

Re-run the visualizer above (after creating the index) and the Seq Scan becomes an Index Scan — milliseconds instead of a full table read. That's the loop: the plan named the problem, and the fix targeted the cause, not the symptom.

The usual suspects

Most slow queries are one of a handful of patterns — and the plan tells you which:

• Missing / unusable index — a Seq Scan with a selective filter, or an index defeated by a function or type mismatch on the column (use an expression index, or don't wrap the column).
• Bad row estimates — a big gap between estimated and actual rows (flagged in red above) means stale statistics → the planner costs everything wrong. Fix with ANALYZE (or more detailed / extended statistics).
• Reading too much — fetching columns or rows you don't need; a covering index or a tighter WHERE/LIMIT helps.
• N+1 queries — one query per row in a loop, from the app side. One join beats a thousand round-trips.
• Unnecessary sorts / hashes spilling to disk — a Sort or Hash that exceeds work_mem writes to disk; an index providing order, or more work_mem, avoids it.

Step 4 — Verify

Always re-EXPLAIN (ANALYZE) after a change and confirm the plan and the actual time improved — not just that you added an index the planner ignored. Measuring before and after is the whole discipline; the plan is your evidence.

CREATE INDEX IF NOT EXISTS events_at ON events (at);
EXPLAIN SELECT count(*) FROM events WHERE at = date '2024-03-01';