pg_stat_statements: What Most Tutorials Miss

You Enabled It. Now What?

Every “optimize your Postgres” post starts the same way: enable pg_stat_statements, run the top-20 query, done. And look, that’s not wrong — it just stops about 20% of the way through what the extension actually gives you.

You get a ranked list of slow queries and feel productive. But you haven’t looked at why they’re slow, whether “slow” is consistent or spiky, how much of your WAL they’re generating, or whether your query planner is quietly losing its mind. That’s the part nobody writes about. Let’s fix that.

This guide assumes Postgres 17 and a self-hosted setup — homelab server, VPS, whatever. The concepts apply to PG 14+ with minor differences noted inline.

Step 1: Enable It Properly

You need two things: the library preloaded at server start, and the extension created in your database.

In postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
track_io_timing = on

Restart Postgres — this is a hard requirement, not a reload:

sudo systemctl restart postgresql

Then in your database (typically postgres or wherever you run admin queries):

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify it’s alive:

SELECT count(*) FROM pg_stat_statements;

If you get a number instead of an error, you’re in.

Why track_io_timing = on Matters

Without this, blk_read_time and blk_write_time are always zero. You’ll be flying blind on whether queries are slow because of CPU or because they’re hammering disk. Enable it. The overhead is minimal on Linux with modern hardware (clock_gettime is fast). On older systems or heavy workloads, benchmark first — but for most homelab or moderate-traffic self-hosted setups, just turn it on.

Step 2: The Query Everyone Runs

SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This is fine. It tells you which queries are eating the most cumulative time. But mean_exec_time is where most people stop digging, and that’s a mistake.

Step 3: What the Tutorials Skip

3a. Mean Isn’t the Whole Story — Tail Latency

A query with a mean of 5ms sounds fine. But if 1% of executions take 2 seconds, your users are still having a bad time — just not all of them, all the time. That’s tail latency, and it’s worse than consistent slowness because it’s harder to catch.

PG 17 adds JIT deform_counter detail plus stats_since/minmax_stats_since columns (the JIT jit_functions/jit_generation_time columns landed back in PG 15), but for standard percentile analysis you want stddev_exec_time:

SELECT
  query,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  round((mean_exec_time + 3 * stddev_exec_time)::numeric, 2) AS p99_approx_ms
FROM pg_stat_statements
WHERE calls > 100
ORDER BY stddev_exec_time DESC
LIMIT 20;

High standard deviation relative to mean = your query is inconsistent. Could be lock contention, autovacuum interference, cache misses on cold data, or plan instability. Any of those deserve a look.

3b. Cache Hits vs. Cache Misses

shared_blks_hit and shared_blks_read tell you whether your query is working from shared buffer cache or going to disk:

SELECT
  query,
  calls,
  shared_blks_hit,
  shared_blks_read,
  round(
    100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0),
    1
  ) AS cache_hit_pct
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 1000
ORDER BY shared_blks_read DESC
LIMIT 20;

A query with 60% cache hit rate on a table that should be hot is a red flag. Either your shared_buffers is too small, the table is getting evicted by other queries, or you’re doing sequential scans on a massive table when an index would get you there faster.

CPU-bound vs. IO-bound is a fundamentally different debugging path. This column tells you which one you’re dealing with.

3c. Rows Per Call

SELECT
  query,
  calls,
  rows,
  round((rows::numeric / NULLIF(calls, 0)), 0) AS rows_per_call
FROM pg_stat_statements
WHERE calls > 10
ORDER BY rows_per_call DESC
LIMIT 20;

A query returning 100,000 rows per call isn’t necessarily slow — but it’s a code smell. Either the application is paginating in memory instead of in SQL (LIMIT/OFFSET exists), someone forgot a WHERE clause, or you’re exporting entire tables row by row. Any of those is worth investigating before your application layer starts thrashing.

3d. Planning Time

SELECT
  query,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_exec_ms,
  round(mean_plan_time::numeric, 2) AS mean_plan_ms,
  round(
    100.0 * mean_plan_time / NULLIF(mean_exec_time + mean_plan_time, 0),
    1
  ) AS plan_pct_of_total
FROM pg_stat_statements
WHERE calls > 50
  AND mean_plan_time > 1
ORDER BY mean_plan_time DESC
LIMIT 20;

If planning is taking 40% of total query time, that’s not a query problem — it’s a planner problem. Options: use prepared statements (the planner caches the plan), pg_hint_plan to force specific paths, or investigate why statistics are stale (ANALYZE).

Ad-hoc queries from ORMs that concatenate parameters into raw SQL strings are notorious for this. Every unique set of values = a fresh parse + plan. Which brings us to the biggest gotcha later.

3e. WAL Pressure

SELECT
  query,
  calls,
  wal_records,
  round(wal_bytes / 1024.0 / 1024.0, 2) AS wal_mb,
  wal_fpi
FROM pg_stat_statements
WHERE wal_bytes > 0
ORDER BY wal_bytes DESC
LIMIT 20;

wal_fpi (full-page images) are written after a checkpoint when Postgres needs to protect against partial page writes. Heavy FPI count means you’re writing a lot right after checkpoints — tune checkpoint_completion_target higher (0.9) and consider increasing max_wal_size so checkpoints happen less often.

High wal_bytes on a query you thought was read-only means it’s probably doing more than you think — temp tables, function side effects, something. Worth digging into.

Step 4: Tuning pg_stat_statements.max

The default is 5000. That’s the maximum number of distinct normalized queries tracked. Once you hit the cap, new queries evict old ones and you start losing data.

For a busy application with an ORM generating slightly different query shapes, 5000 fills up fast. The cost is shared memory — each tracked query uses roughly 5KB. At 10,000 entries that’s ~50MB. On a modern server that’s nothing. Set it higher:

pg_stat_statements.max = 20000

This requires a restart to take effect. Check your current fill rate:

SELECT dealloc FROM pg_stat_statements_info;

If dealloc is climbing, you’re evicting queries and your stats are incomplete. Bump the limit.

Step 5: Reset and Snapshot Comparisons

pg_stat_statements accumulates data from the last reset, not from a fixed time window. That means “top queries by total time” depends heavily on how long since the last reset. A query that was slow yesterday but got fixed today still shows up at the top.

Two approaches:

Manual reset after a fix: Run SELECT pg_stat_statements_reset(); after deploying a fix. Then compare the new accumulated stats to confirm the fix actually worked.

Scheduled snapshots: Capture the stats hourly into a tracking table and diff them. Here’s a minimal version:

CREATE TABLE IF NOT EXISTS pg_stat_snapshots (
  snapshot_time TIMESTAMPTZ DEFAULT now(),
  queryid BIGINT,
  query TEXT,
  calls BIGINT,
  total_exec_time DOUBLE PRECISION,
  mean_exec_time DOUBLE PRECISION,
  stddev_exec_time DOUBLE PRECISION,
  shared_blks_hit BIGINT,
  shared_blks_read BIGINT,
  rows BIGINT,
  wal_bytes BIGINT
);

INSERT INTO pg_stat_snapshots (
  queryid, query, calls, total_exec_time, mean_exec_time,
  stddev_exec_time, shared_blks_hit, shared_blks_read, rows, wal_bytes
)
SELECT
  queryid, query, calls, total_exec_time, mean_exec_time,
  stddev_exec_time, shared_blks_hit, shared_blks_read, rows, wal_bytes
FROM pg_stat_statements
WHERE calls > 10;

Run this via cron or a simple scheduler. To compare two snapshots:

SELECT
  a.queryid,
  a.query,
  b.calls - a.calls AS new_calls,
  b.total_exec_time - a.total_exec_time AS added_exec_ms
FROM pg_stat_snapshots a
JOIN pg_stat_snapshots b ON a.queryid = b.queryid
WHERE a.snapshot_time = '2026-07-09 10:00:00+00'
  AND b.snapshot_time = '2026-07-09 11:00:00+00'
ORDER BY added_exec_ms DESC
LIMIT 20;

This is how you catch a query that’s only slow during the 10am batch job but looks fine in the daily average.

QueryID Stability

One thing worth knowing: in PG 14+, queryid is stable across server restarts. Pre-PG 14 it wasn’t — the ID was hash-based on internal structure that could change. If you’re on PG 17 (you are, per this guide), don’t worry about it. Historical snapshots stay joinable across restarts.

Step 6: Pairing With auto_explain

pg_stat_statements tells you that a query is slow. auto_explain tells you why by logging the actual query plan for slow queries. They’re complementary.

In postgresql.conf:

shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = 1000
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_format = text

This logs the full EXPLAIN ANALYZE for any query taking over 1 second. Check postgresql.log or journalctl -u postgresql for the output:

sudo journalctl -u postgresql --since "1 hour ago" | grep -A 30 "duration:"

The workflow: pg_stat_statements shows you the top offenders by total time. You grab the normalized query text, reproduce it with real parameters, run EXPLAIN (ANALYZE, BUFFERS) manually — or let auto_explain catch it in the wild automatically.

Step 7: Dashboards and Integration

Manually querying pg_stat_statements is fine for debugging. For ongoing visibility, wire it up to something visual.

PgHero is the easiest option for self-hosters. It’s a Rails-based dashboard that reads pg_stat_statements and presents it as a clean UI. Docker setup:

services:
  pghero:
    image: ankane/pghero
    environment:
      DATABASE_URL: postgres://user:pass@db:5432/mydb
    ports:
      - "8080:8080"

It shows slow queries, index health, bloat estimates, and connection counts — all out of the box.

postgres_exporter + Grafana is the homelab-stack-native option. Add the pg_stat_statements collector to your exporter config and you get Prometheus metrics for query stats, which you can dashboard with the community Postgres Grafana dashboards. It integrates cleanly if you’re already running Prometheus.

Datadog and similar commercial APM tools have native pg_stat_statements integration if you’re running a more serious production setup. They handle the snapshot-and-diff automatically and surface tail latency percentiles without you having to write the SQL yourself.

The Biggest Gotcha: Query Normalization and ORM Abuse

pg_stat_statements normalizes queries — it replaces literal values with $1, $2, etc. so that SELECT * FROM users WHERE id = 1 and SELECT * FROM users WHERE id = 2 count as the same query. That’s the whole point.

But here’s where teams shoot themselves: if your application or ORM concatenates values directly into SQL strings instead of using bind parameters, normalization breaks. Every unique combination of values becomes a distinct tracked query:

-- This is tracked as one query (parameterized — good):
SELECT * FROM orders WHERE customer_id = $1

-- These each count as a separate query (concatenated — bad):
SELECT * FROM orders WHERE customer_id = 42
SELECT * FROM orders WHERE customer_id = 43
SELECT * FROM orders WHERE customer_id = 44

With enough unique values, you’ll fill pg_stat_statements.max entirely with variants of the same query, evict everything else, and wonder why your stats look wrong. You’ll also get no plan caching benefit and hammer the planner on every execution.

Check if this is happening to you:

SELECT count(*) AS distinct_queries
FROM pg_stat_statements;

If that number is at or near your pg_stat_statements.max and the queries all look like variants of the same thing with different literal values, you have an ORM concatenation problem. Fix it at the query layer — use prepared statements or parameterized queries. Every major ORM supports this; it’s usually a config option.

Should You Bother?

Honestly, yes — and it takes about five minutes to set up. The basic enable-and-query workflow gives you immediate value for zero ongoing cost. The deeper stuff — snapshot comparisons, track_io_timing, WAL pressure analysis — is worth it the first time a query blows up in production and you need to understand what changed.

For homelab setups, even a modest Postgres instance running Nextcloud, Gitea, or a personal project benefits from this. You’ll catch table scans you didn’t know were happening, notice when your ORM is making 400 queries per page load, and generally stop guessing about what Postgres is doing.

The extension is shipped with every standard Postgres install. There’s no extra package to install, no external service, no cost. The only thing stopping you is not having configured shared_preload_libraries yet.

The default tutorial gets you to “find the slow query.” This guide gets you to “understand why it’s slow and what it’s doing to the rest of your system.” That’s the part worth knowing.

Related Reading

• Adding NOT NULL on a Big Table Without Downtime
• ClickHouse vs DuckDB vs StarRocks: Light OLAP
• Dragonfly vs Redis: Single-Binary Performance
• DuckDB for Local Analytics: Pandas on Steroids
• KeyDB: A Redis Fork With Multi-Threading