What pg_stat_statements and auto_explain do, and who should use them

pg_stat_statements and auto_explain answer two different questions, and they work best in that order.

pg_stat_statements records execution statistics for every statement the server runs. It aggregates them by normalized query shape: literal constants are replaced with placeholders such as $1, so WHERE id = 42 and WHERE id = 97 count as one entry under a single queryid. For each shape you get call counts, total and mean execution time, rows returned, and buffer activity. This answers "which queries cost the most?" across the whole workload, but it shows no plans.

auto_explain writes execution plans to the server log, but only for statements that run longer than a threshold you set. When configured to include actual timings and row counts, it records what the planner chose for that execution and how the estimates compared with reality. This answers "why was this specific execution slow?"

Used together, they replace guesswork with a repeatable process. Aggregate statistics show which query shape deserves attention. auto_explain then captures the plan PostgreSQL actually used in production, with production data and parameters. You don't need to reproduce the query by hand in a session that may plan it differently.

aggregate stats -> pick queryid -> capture plan -> diagnose -> fix -> reset and verify -> (back to aggregate stats)

The final step matters. Resetting the statistics after a fix gives you a clean baseline, so you can show that the change worked rather than assume it did.

This tutorial is for backend and platform engineers who own query performance on PostgreSQL. That includes self-managed servers and managed services that expose these extensions and their settings.

+---------------------------+        +---------------------------+
| 1. Aggregate stats        |        | 2. Pick queryid           |
|    pg_stat_statements     |------->|    top total / mean time  |
|    calls, time, buffers   |        |    one normalized shape   |
+---------------------------+        +---------------------------+
             ^                                     |
             |                                     v
+---------------------------+        +---------------------------+
| 6. Reset and verify       |        | 3. Capture plan           |
|    pg_stat_statements_    |        |    auto_explain logs plan |
|    reset(), re-measure    |        |    when duration > limit  |
+---------------------------+        +---------------------------+
             ^                                     |
             |                                     v
+---------------------------+        +---------------------------+
| 5. Fix                    |        | 4. Diagnose               |
|    index, rewrite, stats  |<-------|    estimated vs actual    |
|    or config change       |        |    rows, scans, timing    |
+---------------------------+        +---------------------------+

The tuning loop: pg_stat_statements ranks normalized query shapes by cost, auto_explain logs the actual plan for slow executions, and a statistics reset after the fix provides a clean baseline to verify the result.

How to enable both extensions

Both tools hook into the server process, so they must be loaded before they can collect anything. They are set up differently.

Preload the libraries

Add both modules to shared_preload_libraries as a comma-separated list, for example 'pg_stat_statements,auto_explain'. You can set this in postgresql.conf or with ALTER SYSTEM. First run SHOW shared_preload_libraries and keep any existing entries. Overwriting the list silently unloads whatever another extension depended on.

pg_stat_statements has to be preloaded because it allocates a fixed block of shared memory at startup to hold its statistics. That allocation can only happen when the server starts. The first enablement therefore requires a full restart, not a reload. Plan it into a maintenance window. Later changes to most auto_explain thresholds take effect on reload.

Create the extension where you query from

Run CREATE EXTENSION pg_stat_statements in the database you will connect to for analysis. This installs the view and the reset function into that database. Collection itself is cluster-wide, so the view lists statements from every database, and the dbid column tells them apart.

auto_explain needs no CREATE EXTENSION

auto_explain is a plain loadable module with no SQL objects. For experiments, run LOAD 'auto_explain' in a single session and set its parameters there. This lets you test thresholds without touching the server config. LOAD normally requires superuser privileges.

Managed services

Hosted PostgreSQL usually exposes these settings through the provider's configuration interface instead of the config file. Providers may also restrict which libraries can be preloaded, block LOAD, or limit some auto_explain parameters. Check what your provider allows before planning around a setting.

-- 1. Check what is already preloaded so you don't drop existing entries.
SHOW shared_preload_libraries;

-- 2. Append both modules to the existing list. If the SHOW above returned
--    entries, include them here, e.g. 'existing_lib,pg_stat_statements,auto_explain'.
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements,auto_explain';

-- 3. A reload is NOT enough for this setting. Restart the server from a shell,
--    for example: sudo systemctl restart postgresql  (or: pg_ctl restart -D $PGDATA)

-- 4. After the restart, confirm the libraries are loaded.
SHOW shared_preload_libraries;

-- 5. In the database you connect to for analysis, install the view and functions.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Collection is cluster-wide; dbid identifies the source database.
SELECT dbid, calls, total_exec_time, left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

-- 6. auto_explain has no CREATE EXTENSION. To experiment in one session only
--    (normally requires superuser), load it and set thresholds locally.
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '250ms';  -- log plans of statements slower than this
SET auto_explain.log_analyze = on;            -- include actual timings (adds overhead)

-- 7. Server-wide auto_explain threshold changes apply on reload, not restart.
ALTER SYSTEM SET auto_explain.log_min_duration = '500ms';
SELECT pg_reload_conf();

Preloading both modules, restarting, creating pg_stat_statements in the analysis database, and loading auto_explain in a single session for testing.

Triage with pg_stat_statements: ranking by total time, mean time and calls

Once the extension is collecting data, rank statements three ways: by total execution time, by mean time, and by calls. Each ranking answers a different question. Total time shows where the server actually spends its time. Mean time shows which queries feel slow to users. Calls shows which queries are frequent enough that a small per-call saving matters.

Total time usually surfaces the biggest wins, because a cheap query that runs often can outweigh an expensive one that runs rarely.

Illustrative estimate (assumed numbers, not a benchmark)

Formula: daily cost = mean time × calls per day

  • Lookup query: 2 ms × 5,000,000 = 10,000 s, about 2.8 hours per day.
  • Report query: 3 s × 50 = 150 s per day.

The 2 ms lookup costs about 67 times more server time than the report. Cutting its mean to 1 ms saves 5,000 s per day. Making the report instant saves only 150 s. Put the lookup first.

Before you open a plan, use the buffer and row counters to narrow down the likely cause:

Signal Likely cause Next check
High shared_blks_read relative to shared_blks_hit Cache misses: the working set doesn't fit in memory, or the query scans more data than it needs Look for sequential scans and missing indexes
Nonzero temp_blks_written Sorts or hashes spilling to disk Check work_mem and the sort or hash nodes in the plan
High rows / calls Over-fetching Check for missing LIMIT or filtering in application code

The counters are cumulative since the last reset. A regression from yesterday's deploy can stay hidden under months of healthy history. To see recent behaviour, take a snapshot of the view at the start of a defined window and another at the end, then compare the deltas. Alternatively, call pg_stat_statements_reset() at the start of the window. Resetting clears the history for everyone who reads the view, so snapshots are usually the safer choice.

-- Rank by total time (PostgreSQL 13+ column names).
-- For the other rankings, use ORDER BY mean_exec_time DESC or calls DESC.
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 0)            AS total_ms,
       round(mean_exec_time::numeric, 2)             AS mean_ms,
       rows / NULLIF(calls, 0)                       AS rows_per_call,  -- over-fetching
       round(100.0 * shared_blks_hit
             / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,  -- cache misses
       temp_blks_written,                                               -- disk spills
       left(query, 60)                               AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Window start: copy the cumulative counters into a snapshot table.
CREATE TABLE pgss_snap AS
SELECT now() AS taken_at, userid, dbid, queryid,
       calls, total_exec_time, shared_blks_read, temp_blks_written
FROM pg_stat_statements;

-- Window end: subtract the snapshot to get activity for this window only.
-- New statements have no snapshot row, so COALESCE treats their start as 0.
-- Do not reset counters between the two snapshots, or the deltas are meaningless.
SELECT e.queryid,
       e.calls - COALESCE(s.calls, 0)                         AS calls,
       round((e.total_exec_time
              - COALESCE(s.total_exec_time, 0))::numeric, 0) AS total_ms,
       e.shared_blks_read - COALESCE(s.shared_blks_read, 0)   AS blks_read,
       e.temp_blks_written - COALESCE(s.temp_blks_written, 0) AS temp_written,
       left(e.query, 60)                                      AS query
FROM pg_stat_statements e
LEFT JOIN pgss_snap s USING (userid, dbid, queryid)
ORDER BY total_ms DESC
LIMIT 20;

-- Alternative: clear all counters at window start and read the view at the end.
-- SELECT pg_stat_statements_reset();

Ranks statements by total execution time and shows rows per call, cache hit percentage and temp blocks written, then compares two snapshots to isolate activity within a defined window.

Capturing real plans with auto_explain

pg_stat_statements tells you which statement is expensive, but not why. auto_explain fills that gap by writing the execution plan of slow statements to the server log, using the parameters and data the statement actually ran with. Load it through shared_preload_libraries (or session_preload_libraries to test it on a single role), then tune the settings below.

The cost behaviour of log_analyze matters most. PostgreSQL only knows a statement was slow after it finishes. Every statement that might be logged therefore has to run with instrumentation, not just the ones that cross the threshold. That is why sample_rate and log_timing = off exist. Sampling limits instrumentation to a fraction of statements. Turning off per-node timing removes the clock calls that cost the most on fast, row-heavy nodes, while keeping actual row counts. Row counts are usually enough to spot the estimate-versus-actual mismatch behind a bad plan.

The example configuration that follows logs plans for statements over 500 ms, samples 10% of statements, and keeps buffer and row detail.

Setting What it adds Cost
auto_explain.log_min_duration Logs only executions slower than the threshold, in ms (-1 disables) Low on its own; set it too low and the log floods
auto_explain.log_analyze Actual rows, loops and timing per node Instruments every sampled statement, not only slow ones
auto_explain.log_buffers Shared/local hits, reads and dirtied blocks per node Small; only takes effect with log_analyze
auto_explain.log_timing Per-node timing (set off to drop it) Highest overhead on systems with slow clock reads
auto_explain.log_nested_statements Plans for statements run inside functions and procedures More log volume
auto_explain.sample_rate Fraction of statements considered for logging Reduces overhead; may miss rare slow runs 
# postgresql.conf
# Changing shared_preload_libraries requires a server restart.
shared_preload_libraries = 'pg_stat_statements,auto_explain'

# Log a plan only when an execution takes longer than 500 ms (-1 disables logging).
auto_explain.log_min_duration = 500

# Record actual rows and loops per node. Every sampled statement is instrumented,
# because the server only knows a statement was slow after it finishes.
auto_explain.log_analyze = on

# Add shared/local buffer hits, reads and dirtied blocks per node.
# This only takes effect when log_analyze is on.
auto_explain.log_buffers = on

# Turn off per-node timing to avoid the clock-call overhead.
# Actual row counts are still logged, which is enough to compare estimated and actual rows.
auto_explain.log_timing = off

# Also log plans for statements executed inside functions and procedures.
auto_explain.log_nested_statements = on

# Instrument only about 10% of statements to limit overhead.
auto_explain.sample_rate = 0.1

A postgresql.conf fragment that loads auto_explain and logs row and buffer detail for 10% of statements that run longer than 500 ms, with per-node timing turned off to reduce overhead.

From log entry to diagnosis to verified fix

Start with the gap between estimated and actual rows on each node. Actual rows are reported per loop, so multiply by the loop count before comparing. An estimate off by orders of magnitude means the planner chose joins and scan types for data that doesn't exist. Every symptom further down the plan often traces back to that one misestimate, so fix it first.

Symptom in the plan Likely cause First fix to try
Estimated rows far from actual Stale or coarse statistics, correlated columns Run ANALYZE; raise the column's statistics target
Seq Scan on a large table returning few rows No usable index for the filter Add or adjust an index matching the predicate
Nested Loop with a very high loop count Underestimated outer rows Fix statistics, or rewrite the query so a hash or merge join is viable
Sort Method: external merge or Hash Batches above 1 Sort or hash exceeds work_mem Raise work_mem for that session or transaction only, not globally

To verify a change, reset pg_stat_statements so old samples don't dilute the average. Then let normal traffic run and watch that queryid's mean time alongside shared_blks_read and shared_blks_hit. A real fix lowers block counts as well as time. If only time drops, the cache may simply be warmer.

Every fix has costs elsewhere. Each new index slows inserts and updates and takes storage. Unused indexes still cost vacuum effort. work_mem applies per sort or hash node, per backend. Raising it globally can therefore multiply across concurrent queries and exhaust memory under load.

When to avoid it and what it cannot tell you

exhaust memory under load. Both tools also have limits of their own.

Overhead and log volume

With auto_explain.log_analyze on, PostgreSQL instruments every sampled statement, because it cannot know in advance which ones will cross the duration threshold. The timing calls cost little per query. On a workload of many sub-millisecond statements, though, that cost becomes a measurable share of each query's runtime. Lower auto_explain.sample_rate, or disable log_timing and keep row counts. A low log_min_duration or enabled log_nested_statements can write a full plan for every statement inside a function. That volume has to be stored, shipped and indexed by your log pipeline.

Gaps in the statistics

pg_stat_statements.max caps the number of tracked entries. Dynamic SQL and IN-lists of varying length can each produce a distinct entry, depending on version. Once the cap is reached, the least-used entries are evicted, so the history you need may already be gone. The view reports mean, min, max and standard deviation, but no percentiles. It also stores no plans. A healthy mean can hide a slow tail where certain parameter values trigger a different plan.

Data exposure and lifetime

Normalized query text can still reveal schema and business logic. Logged plans show literal filter values. Restrict who can read the view and the logs. The counters live on each server. A reset clears them, and so does crash recovery, because they are saved only on clean shutdown. Each replica keeps its own counters, so query each node you care about.

Alternatives and impact on cost and engineering time

The workflow costs a restart, because pg_stat_statements must be listed in shared_preload_libraries. It also adds some tracking overhead and log volume, which grows with how low you set auto_explain's duration threshold and whether you enable log_analyze with timing. In return, you get a ranked list in place of anecdotes. Fixing the query that dominates total time usually helps more than fixing the one that someone reported.

Illustrative estimate (assumed numbers, not a benchmark)

The formula is: daily time saved = calls per day × (mean before − mean after).

  • Query A: 2,000,000 calls × (5 ms − 2 ms) = 6,000 s/day.
  • Query B: 50 calls × (8 s − 1 s) = 350 s/day.

Query B is the one users complain about. Query A is where the database spends its time.

Approach Restart needed Ongoing cost Better choice when
pg_stat_statements + auto_explain Yes, for preload Counter tracking, plan logging You need repeatable, ranked triage
Manual EXPLAIN (ANALYZE, BUFFERS) No Your time per query You already know the query and can reproduce it safely
log_min_duration_statement No, reload is enough Log storage You can't restart and need slow statements with parameters
Sampling pg_stat_activity No Polling load, gaps between samples You're diagnosing a live incident, locks or waits
External log or APM analysis Varies Ingestion and retention You need correlation with application traces

These approaches complement one another. Use pg_stat_statements to choose what to work on, auto_explain or manual EXPLAIN to understand the plan, and the other approaches when a restart or extra overhead isn't acceptable.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to pg_stat_statements slow queries, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.