What do pg_stat_statements and auto_explain each tell you?

pg_stat_statements answers which queries cost the most in aggregate. The extension normalizes each statement by replacing constants with placeholders, so WHERE id = 7 and WHERE id = 9 count as one query. It groups entries by query ID, user, and database, and for each entry it accumulates calls, total and mean execution time, rows returned, and shared buffer hits and reads. Because these are cumulative counters, a 5 ms query called a million times can rank above a 2-second report that runs once a day.

auto_explain answers why a single execution was slow. When a statement exceeds a duration threshold, the extension writes its execution plan to the server log. With the right settings, that plan includes actual row counts and timings. It records one execution with its real parameters, not an average.

Each tool alone leaves a gap. Counters show where the time goes but contain no plans. Logged plans explain individual executions but do not tell you which ones matter in total. Run both, and you can rank queries with the first and diagnose them with the second.

pg_stat_statements auto_explain
Unit Normalized query, aggregated Single execution
Storage Shared memory, queried with SQL Server log
Question answered Which query costs most? Why was this run slow?
Includes plan No Yes

client statement → parser/planner → executor ├─→ pg_stat_statements: update counters in shared memory, keyed by (query ID, user, database) └─→ auto_explain: if duration ≥ threshold, write the plan to the server log

Backend engineers who own the query code get the plan evidence they need to fix a specific query. Platform engineers who own database health get a ranked, database-wide view of load.

                    +--------------------+
                    |  client statement  |
                    +---------+----------+
                              |
                              v
                    +--------------------+
                    |   parser/planner   |
                    +---------+----------+
                              |
                              v
                    +--------------------+
                    |      executor      |
                    +----+----------+----+
                         |          |
              +----------+          +--------------+
              |                                    |
              v                                    v
+----------------------------+      +------------------------------+
| pg_stat_statements         |      | auto_explain                 |
| update counters keyed by   |      | if duration >= threshold:    |
| (query ID, user, database) |      | log the execution plan       |
| calls, total/mean time,    |      | (actual rows and timings     |
| rows, shared hits/reads    |      | if log_analyze is on)        |
+-------------+--------------+      +--------------+---------------+
              |                                    |
              v                                    v
+----------------------------+      +------------------------------+
| shared memory, SQL view    |      | server log                   |
| Q: which query costs most? |      | Q: why was this run slow?    |
+----------------------------+      +------------------------------+

Every executed statement updates pg_stat_statements counters in shared memory, keyed by query ID, user and database, and auto_explain also writes the plan to the server log when the statement's duration reaches the threshold.

How do pg_stat_statements and auto_explain work?

Both extensions are server-side modules that attach to hooks in PostgreSQL's query processing. Neither requires changes to application code, a proxy, or a client library. They observe every statement that passes through the server once they are loaded.

pg_stat_statements works in three steps:

  1. Fingerprinting. After the parser analyzes a statement, a query ID is computed from the structure of its parse tree, ignoring constant values. That is why statements that differ only in literals share one entry. On PostgreSQL 14 and later, query ID computation is controlled by the core compute_query_id setting, and the same ID appears in pg_stat_activity, which lets you connect a running statement to its aggregated statistics.
  2. Accumulating. When execution finishes, the extension adds that run's duration, row count and buffer usage to the matching entry. Entries live in a hash table in shared memory, sized by pg_stat_statements.max, which is why the library must be preloaded at server start. The normalized query text is kept in a file outside shared memory, so long statements do not consume the fixed memory budget.
  3. Exposing. The pg_stat_statements view reads the shared table and returns one row per (query ID, user, database). The counters keep growing until you call pg_stat_statements_reset(). By default they are also saved across a clean server shutdown.

The pg_stat_statements.track setting decides whether only top-level statements are counted or also statements nested inside functions. On versions that support it, pg_stat_statements.track_planning adds planning time as well as execution time.

auto_explain hooks the start and end of execution:

  1. At executor start, it decides whether this statement is sampled, based on auto_explain.sample_rate. If it is sampled and log_analyze is on, it turns on per-node instrumentation so actual rows, loops and, optionally, timings and buffers are collected while the plan runs.
  2. At executor end, it compares the elapsed time with auto_explain.log_min_duration. If the threshold is met, it generates the same output EXPLAIN would produce, from the plan that actually ran, and writes it to the server log along with the statement text.

Because auto_explain logs the plan that was really executed, it captures what EXPLAIN run later by hand may not reproduce: the plan chosen for the actual parameter values, with the table statistics and cache state that existed at that moment.

Who should use pg_stat_statements and auto_explain?

These extensions suit anyone responsible for the performance of a PostgreSQL database that serves real traffic, where you can change server configuration and restart the server at least once.

  • Backend and application engineers who own the SQL their services issue, including SQL generated by an ORM. pg_stat_statements shows which of their queries consume the most database time, and auto_explain gives them the plan evidence to fix one.
  • Platform engineers and DBAs who own database health across many services. They get a database-wide ranking that does not depend on each team instrumenting its own code.
  • On-call engineers and SREs investigating a latency incident. A pg_stat_statements snapshot taken before and during the incident, plus plans already in the log, shortens the path from symptom to cause.
  • Teams preparing a migration, upgrade or schema change, who need a baseline of the heaviest queries to compare against afterwards.

pg_stat_statements is low-risk enough that many teams leave it enabled on every production database. auto_explain needs more thought because of the log_analyze overhead and log volume covered below. A common pattern is to run it server-wide with a conservative threshold and sampling, and to use session-level LOAD for deeper investigation.

They are a poor fit if you cannot set shared_preload_libraries or restart the server. Some managed environments restrict this, so check what your environment allows. They are also not useful for databases with negligible load, where EXPLAIN ANALYZE on the occasional slow query is enough.

How do you enable pg_stat_statements and auto_explain?

Both extensions are loaded through shared_preload_libraries in postgresql.conf. Set it to 'pg_stat_statements,auto_explain', and keep any libraries already listed there. This parameter is read only at server start, so plan a restart. Reloading the configuration is not enough. pg_stat_statements needs this step because it allocates shared memory for its statistics when the server starts. Preloading auto_explain applies it to every session, including connections from application pools that you cannot change.

After the restart, run CREATE EXTENSION pg_stat_statements in the database you will connect to for analysis. The collected statistics cover the whole cluster, but the view and its helper functions exist only in databases where you created the extension. auto_explain has no SQL objects, so it needs no CREATE EXTENSION. It only hooks into the executor.

You do not always need auto_explain running server-wide. To capture plans while you reproduce a problem, load it in your own session with LOAD 'auto_explain'. Then set its parameters with SET, for example SET auto_explain.log_min_duration = 0. Plans for that session go to the server log, and nobody else is affected. LOAD normally requires superuser privileges, so confirm you have them before an incident.

Check the view definition on your own server before you write queries against it. In psql, run \d pg_stat_statements. Column names have changed between major versions, especially the timing columns. A query copied from another source may fail or read the wrong field.

-- 1. In postgresql.conf, append to any libraries already listed, then restart the server:
--      shared_preload_libraries = 'pg_stat_statements,auto_explain'
--    A configuration reload does not apply this parameter.

-- 2. After the restart, confirm both libraries are preloaded.
SHOW shared_preload_libraries;

-- 3. Create the view and helper functions in the database you will analyze from.
--    auto_explain has no SQL objects, so it needs no CREATE EXTENSION.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 4. Check the column names on this server before you write queries against the view.
--    Timing columns differ between major versions.
\d pg_stat_statements

-- 5. Session-only plan capture while you reproduce an issue.
--    LOAD normally requires superuser privileges. Other sessions are not affected.
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;   -- log the plan of every statement in this session
SET auto_explain.log_analyze = on;       -- include actual row counts and timings
SET auto_explain.log_buffers = on;       -- include buffer usage (needs log_analyze)

-- Run the slow statement. Its plan goes to the server log, not to this client.
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = 42
ORDER BY o.created_at DESC
LIMIT 20;

-- Stop capturing plans in this session.
RESET auto_explain.log_min_duration;

Shows the setup in order: preload both libraries and restart, create pg_stat_statements, inspect its columns with \d, and load auto_explain for a single session to log plans while you reproduce a slow query.

How do you find slow queries with pg_stat_statements?

The default instinct is to sort by mean time and look at the slowest statements. That points you at the wrong work. A 5-second report that runs ten times a day costs under a minute of database time. A 2 ms lookup called five million times a day costs close to three hours (illustrative estimate). Ranking by total execution time shows where the server actually spends its effort, and that is where a fix pays back most.

The query below orders by total execution time (total_exec_time, or total_time in older releases). It divides each row by the sum across all statements to get a percentage share. This share matters more than the raw number. If the top five entries account for most of the total, you have a short list. If the share is spread thin, the problem is probably systemic, such as configuration, missing connection pooling or an undersized cache, rather than one bad query.

Two derived columns help you tell I/O-bound queries from CPU-bound ones:

  • Cache hit ratio: shared_blks_hit / (shared_blks_hit + shared_blks_read). A low value means the query often finds its pages outside shared buffers. Look for a missing index or a scan over data that doesn't fit in memory. Note that a "read" may still be served from the OS page cache, so this is a signal, not a disk measurement.
  • Rows per call: rows / calls. A high figure on an endpoint that displays twenty items suggests over-fetching, such as a missing LIMIT or filtering done in application code.

Where your version provides min_exec_time, max_exec_time and stddev_exec_time, include them. Mean time is not typical latency. A query averaging 40 ms might run at 5 ms most of the time and occasionally take 3 seconds because of lock waits or parameter-dependent plans. A large standard deviation, or a large gap between max and mean, tells you to capture real plans instead of tuning for an average that rarely happens.

-- Top statements by total execution time (PostgreSQL 13+ column names;
-- older releases use total_time, mean_time, stddev_time, etc.)
SELECT
  queryid,
  left(query, 60)                                   AS query_start,
  calls,
  round(total_exec_time::numeric, 1)                AS total_ms,
  -- Share of all execution time recorded by pg_stat_statements
  round((100 * total_exec_time
         / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total,
  round(mean_exec_time::numeric, 2)                 AS mean_ms,
  -- Spread around the mean: high stddev or max >> mean means
  -- the mean is not a typical latency
  round(min_exec_time::numeric, 2)                  AS min_ms,
  round(max_exec_time::numeric, 2)                  AS max_ms,
  round(stddev_exec_time::numeric, 2)               AS stddev_ms,
  -- Low ratio: pages often found outside shared buffers (may still
  -- come from the OS page cache, so treat as a signal)
  round(shared_blks_hit::numeric
        / NULLIF(shared_blks_hit + shared_blks_read, 0), 3) AS hit_ratio,
  -- High value on a paginated endpoint suggests over-fetching
  round(rows::numeric / NULLIF(calls, 0), 1)        AS rows_per_call
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Ranks statements by total execution time and adds each statement's percentage share, the timing spread columns, the shared-buffer hit ratio and rows per call, so you can tell where time goes and whether a query is I/O-bound or over-fetching.

How should you configure auto_explain to capture real plans?

Each auto_explain setting controls either what the logged plan contains or how much the instrumentation costs. The table shows a reasonable starting configuration.

Setting Sample value What it controls
auto_explain.log_min_duration 250ms Minimum duration a statement needs before its plan is logged. -1 disables logging and 0 logs every statement.
auto_explain.log_analyze on Logs actual row counts and timings instead of only planner estimates.
auto_explain.log_buffers on Adds shared and local buffer hits and reads. Requires log_analyze.
auto_explain.log_timing off Records per-node timing when log_analyze is on.
auto_explain.log_format json Output format, for machine parsing.
auto_explain.log_nested_statements on Includes statements run inside functions and procedures.
auto_explain.sample_rate 0.1 Fraction of statements that are considered for logging.

The cost of log_analyze

auto_explain cannot know in advance which statement will be slow. With log_analyze on, it instruments every statement it considers and only decides afterwards whether to log the plan. The fast queries pay the instrumentation cost too.

Per-node timing is the expensive part because it reads the clock repeatedly. On hosts with a slow clock source, the overhead can be significant. pg_test_timing measures how expensive clock reads are on your hardware.

You have two ways to reduce the cost:

  • Turn log_timing off. You keep actual row counts and buffer usage but lose per-node times.
  • Lower sample_rate. Fewer statements are instrumented, so overhead drops, but some slow plans are never captured.

Choosing the threshold

A threshold that is too low floods the logs. The extra writes add I/O pressure on the server you are trying to diagnose.

A threshold that is too high only catches the rare outliers. It misses the frequent, moderately slow queries that dominate total execution time in pg_stat_statements.

Use those rankings to choose the threshold. Set it just below the mean time of the top queries by total time.

shared_preload_libraries = 'pg_stat_statements,auto_explain'

auto_explain.log_min_duration = '250ms'

auto_explain.log_analyze = on

auto_explain.log_buffers = on

auto_explain.log_timing = off

auto_explain.log_format = json

auto_explain.log_nested_statements = on

auto_explain.sample_rate = 0.1

A postgresql.conf snippet that loads auto_explain and sets a 250 ms threshold, records actual rows and buffers, turns off per-node timing, and samples 10% of statements to limit overhead.

How do you read a captured plan to find the problem?

Read a captured plan from the innermost nodes outward. Each node shows the planner's estimate (rows=) next to what actually happened (actual rows=, loops=). Multiply actual rows by loops to get the real row count, because per-loop figures hide repeated work. Find the first node, from the bottom up, where the estimate and the actual count diverge by an order of magnitude or more. The planner chose every node above that point using bad numbers, so a slow join higher up is often a symptom rather than the cause.

Symptom in the plan Why it hurts Likely fix
rows= far from actual rows= The planner picks join types and orders for the wrong data size ANALYZE, raise per-column statistics targets, or create extended statistics for correlated columns
Seq Scan on a large table with a selective filter (Rows Removed by Filter is high) Reads every page to return a few rows Add an index that matches the filter, or rewrite a non-sargable predicate such as a function applied to a column
Nested Loop with a large loops= on the inner side The inner scan repeats once per outer row Fix the estimate so the planner picks a hash or merge join, or index the inner join key
Sort Method: external merge, or a hash with Batches greater than 1 The operation spilled to disk because it exceeded work_mem Raise work_mem for that session or role, or reduce the number of rows before the sort or hash

Keep in mind that EXPLAIN ANALYZE actually executes the statement. For an UPDATE, DELETE or INSERT, run it inside BEGIN and finish with ROLLBACK. Also reproduce the problem with the parameter values that auto_explain logged. Representative-looking values can produce a different and misleading plan, particularly when the data is skewed.

A repeatable workflow: rank, capture, fix, reset, compare

The loop has five steps, and each one feeds the next.

  1. Rank by total_exec_time, not mean time. A 2 ms query called millions of times usually costs more than a 5-second report that runs nightly.
  2. Capture the real plan for the top entry. Use the plan auto_explain logged, or rerun it with EXPLAIN (ANALYZE, BUFFERS) using the parameter values from that log.
  3. Fix one thing, such as an index, a rewrite or a statistics target, so that any change in the numbers has a single cause.
  4. Reset or snapshot. pg_stat_statements_reset() starts a clean window, but it clears the data for everyone using the view. Copying the view into a timestamped table and subtracting consecutive snapshots keeps the history and gives the same defined window.
  5. Compare the same window length under similar load.

Illustrative estimate (made-up numbers, not a measurement)

Assume these inputs: a one-hour window with 1,000 s of total execution time, a call count that stays the same, and no change to any other query. Query A accounts for 400 s. An index cuts its mean time from 2 ms to 0.25 ms.

Metric Before fix After fix
Calls 200,000 200,000
Mean time 2 ms 0.25 ms
Total time 400 s 50 s
Window total 1,000 s 650 s
Share of total 40% ~7.7%

The cost argument is straightforward. Ranking by total time directs engineering hours to the queries that consume the most database time. The before and after windows then show what each fix achieved, so nobody has to guess. That matters when you are deciding whether a query is worth more work, or whether the next item in the ranked list is now the better use of that time.

How can pg_stat_statements and auto_explain save engineering time?

Most of the time spent on slow-query work goes into finding the right query and reproducing its behavior, not into writing the fix. These extensions remove much of that work.

  • No hunt for the culprit. Without aggregated statistics, engineers usually start from a user complaint or a latency graph and work backwards through application logs, traces and guesses. One query against pg_stat_statements produces a ranked list across every service that talks to the database, including traffic from ORMs, background jobs and scripts nobody remembers writing.
  • Less effort on the wrong query. Ranking by total time keeps engineers from optimizing a dramatic but rare slow report while a cheap, high-frequency query does most of the damage. Fewer fixes that make no measurable difference means fewer review cycles and deployments spent on them.
  • No reproduction step. Parameter-dependent and data-dependent slowdowns are often hard to reproduce in staging, where data volume and distribution differ. auto_explain records the plan that actually ran in production, with real row counts, so diagnosis can start from evidence instead of from attempts to recreate the problem.
  • Faster incident response. With auto_explain already enabled, plans for slow statements are in the log before anyone is paged. The on-call engineer can read the plan instead of trying to catch the problem while it is happening.
  • Built-in verification. Snapshots of pg_stat_statements before and after a change show whether a fix reduced total execution time. No separate benchmark harness is needed, and decisions about further tuning rest on measured database time rather than impressions.
  • Shared evidence across teams. Platform and application engineers look at the same view and the same plans. Discussions about whether a problem lies in the query, the schema or the server configuration are shorter when everyone starts from the same data.

How much time this saves depends on your workload, your team and how slow-query work is done today, so measure it in your own environment rather than assuming a figure.

When should you avoid relying on these extensions, and what are the alternatives?

Both extensions have blind spots that affect how far you can trust them.

Entry limits. pg_stat_statements.max caps how many distinct statements are tracked. When a workload generates many query shapes, such as ORMs building variable IN lists or ad hoc reporting, the least-used entries are evicted. Their statistics are lost, and new shapes may never stay in the view long enough to rank.

Averaging hides skew. Normalization merges every parameter value into one entry. If one tenant's ID triggers a sequential scan while most IDs use an index, the slow executions get averaged in with fast ones. Check max_exec_time and stddev_exec_time, not only the mean.

No timeline. The counters are cumulative since the last reset. Without external snapshots you cannot tell when a regression began, so the view is not a replacement for monitoring.

Sensitive data. Query text and auto_explain output can include literal values. Restrict read access to the view to trusted roles, and treat the logs as sensitive.

Tool Best for Limitation
EXPLAIN (ANALYZE, BUFFERS) Reproducing one query with chosen parameters Executes the query, so it is unsafe for writes unless run in a transaction you roll back
log_min_duration_statement Every slow execution with its real parameters Log volume, no aggregation
pg_stat_activity What is running or blocked right now Shows only the current state, with no history
External monitoring Trends and alerting on snapshotted counters Extra infrastructure to run

Use these alongside the extensions rather than as replacements for them.

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.