Pick a depth. Each prompt opens in your AI pre-loaded with the lesson. Click a row to preview the prompt.
There is a specific and maddening failure mode where a query is slow in production and fast when you run it yourself. Sometimes that is caching, sometimes it is a different parameter value hitting a different part of the distribution, and sometimes the planner genuinely chose a different plan because the statistics or the memory settings differed. In all three cases, rerunning the query by hand tells you nothing, because you are not reproducing the conditions that produced the slowness. auto_explain solves this by making Postgres log the actual plan of any statement that exceeds a duration you set, at the moment it happens, with real row counts and real buffer numbers from that execution. It is the difference between a theory about what happened and a record of what happened. The cost is not free — log_analyze adds instrumentation overhead to every statement it considers — so the settings deserve the same care as the MongoDB profiler in the previous task.
The configuration, the overhead each setting adds, and what a captured 3am plan looks like when it lands in your log.
-- auto_explain must be preloaded. In postgresql.conf, or via the
-- command flags in the compose file from task 1:
-- shared_preload_libraries = 'pg_stat_statements,auto_explain'
-- ── The settings, with what each one costs ───────────────────────
-- Log the plan of anything over this duration. The only mandatory one.
SET auto_explain.log_min_duration = '200ms';
-- Include ACTUAL row counts and timings, not just estimates. This is the
-- whole point. Cost: per-node timing instrumentation on every statement
-- that gets considered. Measure it; on some workloads it is 2-3%, on
-- short-statement-heavy workloads it can be much worse.
SET auto_explain.log_analyze = on;
-- Buffer counts: shared hit vs read. Module 8 depends on this.
-- Cheap once log_analyze is on.
SET auto_explain.log_buffers = on;
-- Per-node wall-clock timing. This is the expensive part of log_analyze.
-- Turning it OFF keeps row counts and drops timings — a good trade if
-- the overhead bites.
SET auto_explain.log_timing = on;
-- Log the parameter values that produced this plan. Without it you see
-- "WHERE user_id = $1" and cannot tell which user was slow.
-- (PostgreSQL 16+; use log_nested_statements for queries inside functions.)
SET auto_explain.log_parameter_max_length = 1024;
-- Sample: only consider this fraction of statements. The equivalent of
-- MongoDB's sampleRate, and the main overhead control.
SET auto_explain.sample_rate = 0.1;
-- Machine-readable output, if a log parser is going to read it:
SET auto_explain.log_format = 'json';
-- ── What lands in the log at 03:14 ───────────────────────────────
/*
2026-09-12 03:14:02.771 UTC [4412] LOG: duration: 2841.203 ms plan:
Query Text: SELECT * FROM orders WHERE user_id = $1 AND status = $2
Parameters: $1 = '8812', $2 = 'refunded'
Seq Scan on orders (cost=0.00..48210.00 rows=1 width=64)
(actual time=1204.11..2841.02 rows=23 loops=1)
Filter: ((user_id = 8812) AND (status = 'refunded'::text))
Rows Removed by Filter: 1999977
Buffers: shared hit=1024 read=31186
*/
-- Everything you need is in there:
-- Seq Scan -> no index was used
-- rows=1 vs actual rows=23 -> the estimate was wrong (module 7)
-- Rows Removed by Filter -> 2 million rows read and thrown away
-- read=31186 -> 31k pages came off disk, not cache
-- Parameters -> user 8812 specifically, so you can repro
-- Compare against running it by hand at noon, when those 31k pages are
-- already in shared_buffers: same plan, 180ms, and you conclude there
-- is no problem.
-- ── Verify it is live ────────────────────────────────────────────
SHOW shared_preload_libraries; -- must list auto_explain
SELECT * FROM pg_settings WHERE name LIKE 'auto_explain%';