Set log_min_duration_statement = 200 and reload — no restart needed. That logs every statement slower than 200 ms. To also get the query plan, which is the part that tells you why it was slow, add auto_explain to shared_preload_libraries (that one does need a restart) and set auto_explain.log_min_duration = '200ms'. Do not use log_statement = 'all' for this — see the warning below.
-- postgresql.conf, or ALTER SYSTEM SET, then SELECT pg_reload_conf();
logging_collector = on
log_min_duration_statement = 200
log_line_prefix = '%m [%p] %u@%d '
log_checkpoints = on
log_autovacuum_min_duration = 0Table of Contents
A real slow query, and what lands in the log
2.5 million orders, 50,000 customers, a reporting query with no useful index:
SELECT c.email, count(*) AS orders, sum(o.total) AS lifetime
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.email
ORDER BY lifetime DESC
LIMIT 20;
Time: 351.965 msWith only log_min_duration_statement set, that produces one line telling you the query was slow:
2026-08-10 16:44:09.988 CDT [39782] postgres@postgres LOG: duration: 351.790 ms statement:
SELECT c.email, count(*) AS orders, sum(o.total) AS lifetime
FROM customers c JOIN orders o ON o.customer_id = c.id ...With auto_explain also loaded, you get the reason. Trimmed to the load-bearing lines — the full plan in the log is 30 lines deep:
LOG: duration: 347.430 ms plan:
Limit (actual time=341.654..347.420 rows=20 loops=1)
-> Sort (actual time=341.654..347.418 rows=20 loops=1)
Sort Method: top-N heapsort Memory: 27kB
-> Finalize GroupAggregate (actual time=288.450..342.832 rows=50000 loops=1)
-> Gather Merge (actual time=288.440..308.768 rows=149967 loops=1)
Workers Planned: 2 Workers Launched: 2
-> Sort (actual time=286.508..289.099 rows=49989 loops=3)
Sort Method: external merge Disk: 5240kB
-> Partial HashAggregate (actual time=223.927..267.443 rows=49989 loops=3)
Planned Partitions: 4 Batches: 5 Disk Usage: 11928kB
-> Hash Join (actual time=5.942..128.805 rows=416441 loops=3)
-> Parallel Seq Scan on orders o
Filter: (status = 'paid'::text)
Rows Removed by Filter: 416888Configuration that produced it:
shared_preload_libraries = 'auto_explain' # requires a restart
auto_explain.log_min_duration = '200ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = onauto_explain ships with PostgreSQL itself — including this Homebrew build, where auto_explain.dylib sits in the package library directory. It has no CREATE EXTENSION step and never appears in pg_available_extensions, which leads people to conclude it isn’t installed. Confirm it loaded by looking for its settings: SELECT name, setting FROM pg_settings WHERE name LIKE 'auto_explain%'; — thirteen rows means it is live. Be aware that log_analyze = on means the query is actually instrumented, which adds measurable overhead on every statement, not only the slow ones; use auto_explain.sample_rate below 1 on a busy server.
The slow-query log line and the logged plan above, and the work_mem comparison, password leak and pg_stat_statements ranking below, are checked by enable-slow-query-log.sh, a runnable verification script that builds its own cluster, loads both modules and deletes everything on exit.
What the plan told me, and what happened when I believed it
Sort Method: external merge Disk: 5240kB is the classic finding: the sort didn’t fit in work_mem and spilled. So raise work_mem. Three runs at each setting, durations taken from the log:
work_mem=4MB duration: 346.694 ms / 338.700 ms / 346.657 ms
Sort Method: external merge Disk: 5240kB (x9)
work_mem=64MB duration: 369.777 ms / 370.307 ms / 371.886 ms
Sort Method: top-N heapsort Memory (no disk sort at all)The spill is gone and the query got slower. The spill was never the bottleneck — the parallel sequential scan over 2.5 million rows was, and with more work_mem the planner chose a different aggregate strategy that happened not to pay off here. This is the entire argument for logging rather than guessing, and it is why I’ve left the result in instead of quietly picking an example where the obvious fix worked.
log_statement is not a performance tool, and it leaks passwords
Using log_statement = 'all' for slow query analysis has two problems. It logs every statement regardless of duration, so on any real workload it buries the slow ones and fills the disk. And it logs statements verbatim, including the ones with credentials in them:
postgres=# ALTER SYSTEM SET log_statement = 'all';
postgres=# SELECT pg_reload_conf();
postgres=# CREATE ROLE hw_leak LOGIN PASSWORD 'Sup3rSecret!';
-- the log:
2026-08-10 16:44:32.361 CDT [45462] postgres@postgres LOG: statement:
CREATE ROLE hw_leak LOGIN PASSWORD 'Sup3rSecret!';The catalog stores a hash — SCRAM-SHA-256$4096:p... — and the log stores the password. Server logs are routinely shipped to a log aggregator with far more readers than the database has. Leave log_statement at none, or use ddl if you want a schema change audit trail, and accept that even ddl captures CREATE ROLE ... PASSWORD. Set passwords with \password in psql, which sends a pre-hashed verifier.
pg_stat_statements: the aggregate view
pg_stat_statements is present in the Homebrew postgresql@17 package and loads normally:
postgres=# SELECT name, default_version FROM pg_available_extensions WHERE name='pg_stat_statements';
name | default_version
--------------------+-----------------
pg_stat_statements | 1.11Add it to shared_preload_libraries, restart, then CREATE EXTENSION pg_stat_statements;. It answers a different question from the log: not “which executions were slow” but “which statements cost the most in total”, which is where you find the 4 ms query that runs 100,000 times an hour:
SELECT round(total_exec_time::numeric,1) AS total_ms, calls,
round(mean_exec_time::numeric,1) AS mean_ms,
left(regexp_replace(query,'\s+',' ','g'),46) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
total_ms | calls | mean_ms | query
----------+-------+---------+------------------------------------------------
5761.3 | 2 | 2880.6 | INSERT INTO orders (customer_id, status, total
893.5 | 3 | 297.8 | SELECT c.email, count(*) AS orders, sum(o.tota
257.6 | 1 | 257.6 | SELECT DISTINCT customer_id, status FROM order
172.5 | 2 | 86.3 | ANALYZE ordersUse both. pg_stat_statements ranks your problems; the log plus auto_explain explains the one you picked. For what is running right now, neither helps — that’s pg_stat_activity.
Two settings people set wrong
log_checkpoints is on by default on this build and worth leaving on; if num_requested in pg_stat_checkpointer outpaces num_timed, your max_wal_size is too small. And log_directory: the default is 'log', not 'pg_log', and putting logs inside the data directory means pg_basebackup copies them. Point it somewhere outside $PGDATA on a real server.
Finally, do not restart to apply these. log_min_duration_statement, log_statement, log_checkpoints, log_directory and all the auto_explain.* settings have context superuser or sighup — a reload is enough. Only shared_preload_libraries and logging_collector need the restart. SELECT pg_current_logfile(); tells you which file to tail.
Verified 2026-08-10 on PostgreSQL 17.10 (Homebrew), aarch64-apple-darwin25.6.0. Every setting, log line, plan fragment and timing above was produced on a throwaway cluster and pasted unedited — including the password leak and the work_mem change that made things worse. The plan block is the only excerpt trimmed: the cost= estimates and the Buffers: lines were deleted to make it fit, and nothing else was altered.
