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 return just 16 — the telltale sign of a missing or unusable index. The −N filtered on that node is Rows Removed by Filter: 79,984 — every row Postgres read and then immediately threw away, pure wasted work (~21 ms of it). With the right index this same lookup drops to a fraction of a millisecond — the ~700× difference you'll create in Step 3.

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 — a fraction of a millisecond instead of a full table read. That's the loop: the plan named the problem, and the fix targeted the cause.

Why the plain index didn't help: sargability

It's worth seeing exactly why an ordinary index on user_email is useless here. Add one and run both lookups: on the bare column it's used instantly; wrap that same column in lower(...) and the index is ignored — straight back to a full Seq Scan:

DROP TABLE IF EXISTS people;
CREATE TABLE people (email text);
INSERT INTO people SELECT 'User' || g || '@Example.com' FROM generate_series(1, 80000) g;
CREATE INDEX people_email ON people (email);   -- a plain index on the raw column
ANALYZE people;

-- bare column → Index Scan, sub-millisecond
EXPLAIN (ANALYZE) SELECT * FROM people WHERE email = 'User42@Example.com';

-- same column, wrapped in a function → Seq Scan, ~80,000 Rows Removed by Filter
EXPLAIN (ANALYZE) SELECT * FROM people WHERE lower(email) = 'user42@example.com';

An index stores values in the column's raw order; lower(user_email) is a different value the index knows nothing about, so the planner can't use it. A predicate an index can satisfy is called sargable (“Search ARGument-able”). The classic index-killers all make a predicate non-sargable by touching the indexed column:

• wrapping the column in a function — lower(col), date(col), col + 1 (fix: index the expression, or move the work to the other side, e.g. col = upper($1));
• a cast forced onto the column — comparing a text column to a number, or col::text = …;
• a leading wildcard — LIKE '%foo' can't use a B-tree, though a trailing one ('foo%') can.

Rule of thumb: keep the indexed column bare on one side of the comparison and most of these evaporate.

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';