Finding and Fixing Slow Queries

In one line

Find slow queries with pg_stat_statements (by total time, not just mean), reproduce them with EXPLAIN ANALYZE, and fix the usual causes: missing or wrong index, functions or implicit casts on indexed columns, SELECT *, unbounded result sets, large OFFSETs, N+1 query loops, and poor estimates.

0/5 · 0%

Think of it like this

A clinic triage nurse. They don't treat whoever complains loudest; they check who is costing the most (total time = frequency × duration) and fix those first.

Key ideas

01
pg_stat_statements groups queries by shape and reports calls, total and mean time, rows, and buffers. A 2 ms query called 50K times/sec costs more than a 10 s report run once an hour.
02
Sargability: WHERE date(created_at) = '2026-09-01' or WHERE lower(email) = ? can't use a plain index on the column. Rewrite as a range (created_at >= '2026-09-01' AND created_at < '2026-09-02') or add an expression index.
03
Implicit casts: comparing a varchar column with a numeric parameter, or a JDBC driver sending bigint for an int column, can disable an index or add casts on every row. Match parameter types to column types.
04
Bound everything: every list query has LIMIT; every API has a max page size; background jobs process in batches. SELECT only needed columns (avoids TOAST reads and enables index-only scans).
05
Log slow queries (log_min_duration_statement = 250ms) and capture their plans with auto_explain, so you see the plan that was slow in production, not the one you get on your laptop.

Code & diagrams

find-slow.sqlsql

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT round(total_exec_time::numeric / 1000, 1) AS total_s,
       calls, round(mean_exec_time::numeric, 2) AS mean_ms,
       rows, left(query, 70) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;
--  total_s | calls    | mean_ms | rows     | query
--   8120.4 | 41203321 |    0.20 | 41203321 | SELECT * FROM users WHERE id = $1
--   2210.9 |      412 | 5366.30 |      412 | SELECT ... FROM orders WHERE date(created_at) = $1

-- non-sargable -> sargable
-- before: WHERE date(created_at) = '2026-09-01'
SELECT count(*) FROM orders
WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02';

Interview problem

The problem

The dashboard that got slower every month

An admin dashboard runs SELECT * FROM orders WHERE status <> 'delivered' AND to_char(created_at,'YYYY-MM') = to_char(now(),'YYYY-MM') ORDER BY created_at DESC and now takes 30 s with 200M orders. Optimize it.

When it breaks

Optimizing by mean time only

What you see

The team tunes a rare 10 s report while a 1 ms query at 80K calls/sec consumes most of the CPU.

Fix & prevent

Rank by total_exec_time and by buffers; cache or batch the high-frequency queries.

Explain it without notes

What makes a predicate non-sargable?

Practice

Rewrite WHERE extract(year FROM created_at) = 2026 to use an index on created_at.

Trade-offs

↔
Query rewrites are free at runtime but need code changes; indexes are quick to add but cost every write.

Done when you can

I can find the most expensive queries and fix common anti-patterns.

Command Palette