pg_stat_statements: The Complete Practical Guide

What pg_stat_statements actually gives you

I should like to begin with the extension that belongs in every PostgreSQL deployment. pg_stat_statements is, if you'll permit the conviction, the single most important PostgreSQL extension for performance work. It ships with PostgreSQL — no third-party download, no compilation, no compatibility concerns.

When enabled, pg_stat_statements intercepts every SQL statement executed against your database, normalizes it by replacing literal parameter values with placeholders ($1, $2, ...), and accumulates execution statistics. The result is a view — pg_stat_statements — where each row represents one distinct query shape and its cumulative performance data.

The per-query metrics include:

• calls — total number of times this query has been executed
• total_exec_time / mean_exec_time / min_exec_time / max_exec_time — wall-clock execution time in milliseconds
• rows — total number of rows returned or affected
• shared_blks_hit — blocks found in shared_buffers (cache hits)
• shared_blks_read — blocks read from the OS (cache misses or disk reads)
• shared_blks_dirtied / shared_blks_written — blocks modified or written
• stddev_exec_time (PostgreSQL 13+) — standard deviation of execution time, revealing inconsistent performance
• total_plan_time / mean_plan_time (PostgreSQL 14+) — time the planner spent, separate from execution
• wal_bytes / wal_records (PostgreSQL 14+) — WAL generation per query, showing write amplification
• min_exec_time / max_exec_time with full precision (PostgreSQL 16+) — exact range of execution times

What pg_stat_statements does not capture: the actual parameter values, the execution plan, which indexes were used, or lock wait time. I should be forthcoming about these gaps, because pretending they do not exist would be a disservice. They are filled by other tools — auto_explain captures plans, pg_qualstats tracks predicate usage, and log_min_duration_statement logs full queries with parameter values.

One more thing worth knowing up front: every major managed PostgreSQL provider — RDS, Cloud SQL, Supabase, Neon — enables pg_stat_statements by default. If you are running on a managed service, you likely already have it. A quick check:

SELECT * FROM pg_stat_statements LIMIT 1;

If that returns a row, you are already collecting data.

Installation and configuration

Self-hosted installation

pg_stat_statements is a shared library that hooks into the PostgreSQL executor. It must be loaded at server startup — allow me to walk you through it:

Step 1: Add to shared_preload_libraries

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

If you already have entries in shared_preload_libraries, append it:

shared_preload_libraries = 'auto_explain, pg_stat_statements'

This change requires a full PostgreSQL restart — a reload is not sufficient.

Step 2: Restart PostgreSQL

sudo systemctl restart postgresql

Step 3: Create the extension

Connect to each database you want to monitor and run:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Step 4: Verify

SELECT calls, query FROM pg_stat_statements LIMIT 5;

If you see rows, the extension is active and tracking.

A word of caution: adding pg_stat_statements to shared_preload_libraries but skipping the restart is a remarkably common misstep. The CREATE EXTENSION command will succeed — the extension metadata installs fine — but the shared library is not loaded, so no queries are tracked. The view will exist but remain empty. I have seen this catch experienced engineers more than once.

Configuration settings that matter

pg_stat_statements.max (default: 5000) — the maximum number of distinct query shapes tracked. When the limit is reached, the least-executed queries are evicted. On busy systems with many query variations — ORMs are particularly prolific in this regard — 5000 fills up quickly. I would recommend raising this to 10000 on production systems. The memory cost is modest — each entry uses approximately 2KB of shared memory, so 10000 entries consume about 20MB.

pg_stat_statements.track (default: top) — controls which statements are tracked: top (only top-level statements), all (includes statements inside PL/pgSQL functions), or none (disabled). Use top unless you need visibility into stored procedures.

pg_stat_statements.track_utility (default: on) — whether DDL and utility commands are tracked. If you only care about DML performance, disable this to conserve hash table entries.

pg_stat_statements.track_planning (default: off, PostgreSQL 14+) — records planner time separately from execution time, letting you distinguish between a query that is slow to plan and one that is slow to execute.

Recommended production configuration:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = off
pg_stat_statements.track_planning = on   # PostgreSQL 14+

Managed provider notes

Amazon RDS / Aurora — pg_stat_statements is enabled by default. Configuration changes go through parameter groups. Some parameter changes require a reboot of the RDS instance.

Google Cloud SQL — enabled by default. Database flags control the configuration. Stats survive failover on high-availability instances.

Supabase — enabled by default. Accessible through the SQL editor in the dashboard or any PostgreSQL client.

Neon — enabled by default. Stats persist across compute restarts, so suspending and resuming a Neon compute does not reset your accumulated data.

The 10 queries you should run against pg_stat_statements

If you'll permit me, I should like to conduct a brief inventory. These 10 queries cover the vast majority of performance investigation. The first three — total time, mean time, and call count — are the foundation. The remaining seven address specific concerns: I/O pressure, cache efficiency, planner overhead, data volume, table scans, regressions, and write amplification.

1. Top 10 queries by total time

The queries consuming the most cumulative wall-clock time across all calls. This is where your optimization effort should begin. A query called 500,000 times at 2ms each costs more total time than a query called once at 800ms — the arithmetic is unforgiving.

SELECT
    substr(query, 1, 100) AS query_preview,
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS mean_time_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Example output:

          query_preview           |  calls  | total_time_ms | mean_time_ms |  rows
---------------------------------+---------+---------------+--------------+--------
 SELECT o.id, o.total, c.name F  | 482,103 |  1,205,412.87 |         2.50 | 482,103
 UPDATE inventory SET quantity =  |  95,220 |    891,003.41 |         9.36 |  95,220
 SELECT p.name, p.price FROM pr  | 310,455 |    654,221.10 |         2.11 | 930,812
 INSERT INTO audit_log (event_t  | 720,000 |    504,000.00 |         0.70 | 720,000
 SELECT count(*) FROM orders WH  |  12,480 |    312,000.00 |        25.00 |  12,480

The first entry is a simple SELECT running at 2.5ms per call — fast individually — but it has been called nearly half a million times. That adds up to 20 minutes of total execution time. A 20% improvement on that query saves more time than eliminating the fifth query entirely.

2. Top 10 queries by mean execution time

The slowest queries on average. These are the queries that make individual requests feel slow from the application's perspective.

SELECT
    substr(query, 1, 100) AS query_preview,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_time_ms,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    rows
FROM pg_stat_statements
WHERE calls >= 10  -- exclude one-off admin queries
ORDER BY mean_exec_time DESC
LIMIT 10;

The WHERE calls >= 10 filter is important. Without it, the results fill up with one-time migration scripts, manual ad-hoc queries, and maintenance commands that ran once and took a while. Those are rarely worth optimizing.

This query complements the total-time query. Total time finds the systemic cost — the aggregate load on the database. Mean time finds the user-facing pain — the queries that make individual page loads or API responses slow.

3. Top 10 queries by call count

The most frequently executed queries. High call count is not inherently bad, but it amplifies any per-call cost and often reveals architectural issues.

SELECT
    substr(query, 1, 100) AS query_preview,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_time_ms,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    rows
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

Interpretation:

• High call count + low mean time — probably fine. A 0.1ms query called a million times is 100 seconds of total time — likely not your bottleneck.
• High call count + moderate mean time — multiplied cost. A 5ms query called a million times is 5,000 seconds. Worth investigating.
• An unexpectedly high call count — often reveals N+1 query patterns: the same query template called hundreds or thousands of times per minute because an ORM is loading related records one at a time inside a loop. I'm afraid this pattern is more common than one would hope.

4. Top 10 queries by shared blocks read (I/O-heavy)

Queries that read the most data from disk (or the OS page cache) rather than finding it in PostgreSQL's shared_buffers.

SELECT
    substr(query, 1, 100) AS query_preview,
    calls,
    shared_blks_read,
    shared_blks_hit,
    round(
        100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2
    ) AS cache_hit_pct,
    rows
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

High shared_blks_read relative to shared_blks_hit means the query's working set does not fit in the buffer cache. These queries are candidates for better indexing, larger shared_buffers, or query restructuring. For deeper investigation, see pg_buffercache analysis.

5. Cache hit ratio per query

A per-query view of cache effectiveness, more useful than the system-wide cache hit ratio.

SELECT
    substr(query, 1, 100) AS query_preview,
    calls,
    shared_blks_hit + shared_blks_read AS total_blks,
    round(
        100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2
    ) AS cache_hit_pct
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 1000  -- only queries touching meaningful data
ORDER BY cache_hit_pct ASC
LIMIT 10;

The system-wide cache hit ratio — often touted as "should be above 99%" — is, I'm afraid, something of a vanity metric. It tells you the average across all queries, which obscures the fact that a few queries may be cache-cold while the rest are cache-hot. Per-query cache hit ratio tells you exactly which queries are struggling. Queries below 90% deserve investigation. Below 80% is a clear signal of significant I/O.

6. Queries with the worst planning-to-execution ratio (PostgreSQL 14+)

Queries where the planner is spending disproportionate time relative to execution. This requires pg_stat_statements.track_planning = on.

SELECT
    substr(query, 1, 100) AS query_preview,
    calls,
    round(total_plan_time::numeric, 2) AS total_plan_ms,
    round(total_exec_time::numeric, 2) AS total_exec_ms,
    round(
        100.0 * total_plan_time / NULLIF(total_exec_time, 0), 2
    ) AS plan_pct_of_exec
FROM pg_stat_statements
WHERE total_exec_time > 1000  -- only queries with meaningful execution time
  AND total_plan_time > 0
ORDER BY total_plan_time / NULLIF(total_exec_time, 0) DESC
LIMIT 10;

If planning time exceeds 50% of execution time, the query has a planner problem — not an execution problem. Common causes: complex joins across many tables, stale or missing table statistics, or partitioned tables with hundreds of partitions. The immediate fix is often ANALYZE on the relevant tables.

7. Queries returning the most rows

Queries that return (or affect) the most rows relative to their call count.

SELECT
    substr(query, 1, 100) AS query_preview,
    calls,
    rows,
    round(rows::numeric / NULLIF(calls, 0), 0) AS rows_per_call,
    round(mean_exec_time::numeric, 2) AS mean_time_ms
FROM pg_stat_statements
WHERE calls > 0
ORDER BY rows / NULLIF(calls, 0) DESC
LIMIT 10;

A query returning 50,000 rows per call raises questions that deserve honest answers. Is your application actually using all 50,000 rows? Often the answer is no — a missing LIMIT clause, a SELECT * where only a few columns are needed, or a report query that should run asynchronously. ORMs that eagerly load relationships commonly produce these patterns.

8. Queries with high row-to-block ratio (table scan detection)

A proxy for detecting queries that touch far more data than they return.

SELECT
    substr(query, 1, 100) AS query_preview,
    calls,
    rows,
    shared_blks_hit + shared_blks_read AS total_blks,
    round(
        rows::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0), 4
    ) AS rows_per_block
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 1000
  AND calls > 10
ORDER BY rows_per_block ASC
LIMIT 10;

A low rows-per-block ratio means the query reads many blocks to return few rows. This is the signature of sequential scans where an index scan would be appropriate, non-selective index scans, or queries on bloated tables. A healthy index lookup typically reads 3-5 blocks per row. A sequential scan on a large table might read thousands of blocks per row returned.

9. New queries since last reset (regression detection)

After a deployment, new query shapes that did not exist before the reset may indicate regressions introduced by the code change.

The workflow: snapshot the current data before deploying, reset with SELECT pg_stat_statements_reset();, deploy the new code, wait for representative traffic, then query for high-cost new entries:

SELECT
    substr(query, 1, 100) AS query_preview,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_time_ms,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Since the stats were reset right before deployment, everything visible is new. Sort by mean execution time to find the queries the new code introduced that are slowest.

10. WAL generation by query (PostgreSQL 14+)

Which queries generate the most WAL (Write-Ahead Log) data. High WAL generation means high write amplification, which affects replication lag, backup throughput, and storage I/O.

SELECT
    substr(query, 1, 100) AS query_preview,
    calls,
    pg_size_pretty(wal_bytes) AS total_wal,
    pg_size_pretty((wal_bytes / NULLIF(calls, 0))::bigint) AS wal_per_call,
    wal_records
FROM pg_stat_statements
WHERE wal_bytes > 0
ORDER BY wal_bytes DESC
LIMIT 10;

Common findings: bulk UPDATE/DELETE without batching, unnecessary index maintenance (each indexed column amplifies WAL cost), and TOAST updates on large text or JSONB columns. If a query generates gigabytes of WAL per day, consider batching the operation or reviewing whether all indexes on the affected table are necessary.

Interpreting results — what the numbers actually mean

Cumulative vs point-in-time

Allow me to emphasize this, because it is the single most important thing to understand: every counter in pg_stat_statements is cumulative since the last reset.

A total_exec_time of 3,600,000ms does not mean a query took an hour. It means the sum of all executions since the last reset. If the stats have been accumulating for a week, 3.6 million milliseconds across hundreds of thousands of calls might be perfectly normal. Always consider the time window. The relative ordering matters more than the absolute numbers.

Query normalization

pg_stat_statements normalizes queries by replacing literal values with parameter placeholders:

-- These two queries are the same entry in pg_stat_statements:
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 99999;

-- Both appear as:
SELECT * FROM users WHERE id = $1;

This normalization is essential — without it, the hash table would fill up instantly with thousands of unique-looking queries that are structurally identical.

The trade-off: if a query is slow only for certain parameter values (data skew — 99% of users have 1-5 orders, but one user has 50,000), pg_stat_statements will not reveal this. To investigate parameter-specific problems, use auto_explain or log_min_duration_statement.

When numbers lie

I should note a subtlety that catches many teams. Mean hides bimodal distributions. A query that runs in 1ms 99% of the time and 5,000ms 1% of the time reports a mean of approximately 51ms. That mean looks mildly concerning but not alarming — yet 1% of your requests are experiencing 5-second latency.

stddev_exec_time (PostgreSQL 13+) helps detect this. A high standard deviation relative to the mean signals inconsistent performance. min_exec_time and max_exec_time give the full range. If min is 0.5ms and max is 8,000ms, the query has severe performance variance that the mean alone would never reveal.

Resetting and snapshotting stats

When to reset

SELECT pg_stat_statements_reset();

This clears all accumulated statistics. Every counter goes to zero, every query entry is removed. Reset is appropriate after major deployments (establish a clean baseline), after fixing a slow query (measure the improvement with fresh data), or on a periodic schedule (weekly is a common cadence).

A word of caution: do not reset too frequently. Daily resets mean you lose the ability to see weekly trends. If you reset every few hours, you will never accumulate enough data to identify infrequent but expensive queries.

Building a history

pg_stat_statements data is not durable — a detail worth appreciating. It lives in shared memory and survives normal operation, but a reset call or a server restart on some platforms clears it. To track trends over time, snapshot the data into a persistent table:

-- Create a history table
CREATE TABLE pgss_snapshots (
    snapshot_time timestamptz NOT NULL DEFAULT now(),
    queryid bigint NOT NULL,
    query text,
    calls bigint,
    total_exec_time double precision,
    mean_exec_time double precision,
    rows bigint,
    shared_blks_hit bigint,
    shared_blks_read bigint
);

-- Take a snapshot (run on a schedule via pg_cron or application cron)
INSERT INTO pgss_snapshots (queryid, query, calls, total_exec_time, mean_exec_time, rows, shared_blks_hit, shared_blks_read)
SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements;

With snapshots taken hourly or daily, you can answer questions impossible with point-in-time data: is this query getting slower over time? Did last Tuesday's deployment introduce a regression? Compare consecutive snapshots to derive per-interval rates rather than relying on raw cumulative counters.

Deallocation and the max setting

When pg_stat_statements.max entries are consumed, new query shapes cause the least-executed existing entries to be evicted. On PostgreSQL 14+, pg_stat_statements_info reports how often this happens:

SELECT dealloc FROM pg_stat_statements_info;

If dealloc is high (growing steadily between resets), the hash table is too small. Raise pg_stat_statements.max. High deallocation means you are losing visibility into infrequent queries — and those might include slow admin queries, rare report queries, or batch processing queries.

pg_stat_statements vs pg_stat_monitor — choosing your foundation

pg_stat_monitor is a well-engineered alternative from Percona that extends the pg_stat_statements concept with time-bucketed analysis, query plan text capture, client IP tracking, histogram buckets for execution time distribution, and top query ranking per bucket.

pg_stat_statements is the universal standard. Every monitoring tool, every managed provider, every tutorial assumes it. pg_stat_monitor adds genuinely useful features, but at the cost of broader compatibility. The two extensions cannot run simultaneously — they both hook into the same executor hook point. You must choose one.

For your particular situation, I would recommend starting with pg_stat_statements. It has universal support, minimal overhead, and covers the large majority of use cases. Move to pg_stat_monitor if you specifically need built-in time-bucketed analysis, per-client-IP breakdown, or histogram distributions, and your provider supports it.

For a detailed feature-by-feature comparison, see pg_stat_monitor vs pg_stat_statements. For the complete pg_stat_monitor guide, see pg_stat_monitor optimization.

What pg_stat_statements cannot do — and what fills the gaps

pg_stat_statements tells you what is slow. It does not tell you why. A butler who overstates his case is no butler at all, so let me be direct: that "why" requires additional tools, each filling a specific gap.

The complete monitoring stack combines pg_stat_statements (what is slow) + auto_explain (why it is slow) + pg_qualstats (what is missing). Together, they form a proper diagnostic household. For the broader diagnostic workflow, see the slow query diagnostic flowchart.

How Gold Lapel uses this data

The queries in this guide form the foundation of any serious PostgreSQL performance practice. But running them manually — weekly, after deployments, during incident response — requires discipline and time. The data is there; the question is whether someone is looking at it.

Gold Lapel observes every query in flight, continuously. It performs the same analysis these 10 queries provide — total time, mean time, cache hit ratios, I/O pressure, regression detection — but automatically, on every query, in real time. When a query's performance profile changes, Gold Lapel responds without waiting for someone to notice and run a diagnostic query.

pg_stat_statements remains an excellent tool for manual investigation, incident response, and building intuition about your workload. Gold Lapel handles the same class of observation continuously, so the diagnostic work happens whether or not someone is watching the dashboard.