pg_stat_statements

What pg_stat_statements does

pg_stat_statements normalizes every SQL statement — replacing literal values with parameter placeholders — and accumulates statistics per normalized query. The result is a table where each row represents a distinct query pattern, with columns tracking total time, average time, call count, rows returned, buffer hits and misses, and more.

This is not a sampled view. Every query that runs gets counted. The extension groups queries by their structure, so SELECT * FROM users WHERE id = 1 and SELECT * FROM users WHERE id = 42 are tracked as the same pattern. This normalization is what makes it practical — you get workload-level visibility without drowning in individual executions. One row per query shape. Everything you need to know about where the time goes.

When to use pg_stat_statements

Install it on every PostgreSQL instance. There is no good reason not to. The overhead is negligible, and the diagnostic value is irreplaceable.

Specific scenarios where it shines:

• Finding your slowest queries — sort by total_exec_time to find the queries consuming the most cumulative time
• Identifying hot paths — sort by calls to find queries that run most frequently
• Spotting regressions — reset statistics before a deployment, then compare mean_exec_time after
• Measuring cache efficiency — compare shared_blks_hit vs shared_blks_read per query
• Capacity planning — track how total query volume and execution time change over weeks and months

Installation and setup

pg_stat_statements must be loaded as a shared library at server startup — it cannot be loaded on the fly. This means a one-time restart is required when enabling it for the first time.

-- 1. Add to postgresql.conf (requires restart)
shared_preload_libraries = 'pg_stat_statements'

-- 2. Create the extension
CREATE EXTENSION pg_stat_statements;

-- 3. Verify it's working
SELECT count(*) FROM pg_stat_statements;

One restart. One CREATE EXTENSION. That is the full cost of entry.

After the restart, the extension begins tracking immediately. No further configuration is needed for most workloads. The default settings — 5,000 tracked queries, execution time tracking enabled — are sensible for the vast majority of databases.

Key columns

The pg_stat_statements view exposes dozens of columns. These are the ones you will use most often.

-- Key columns in pg_stat_statements (PostgreSQL 13+)
-- Timing
total_exec_time    -- cumulative execution time (ms)
mean_exec_time     -- average execution time per call (ms)
min_exec_time      -- fastest execution (ms)
max_exec_time      -- slowest execution (ms)
stddev_exec_time   -- standard deviation of execution time

-- Call counts
calls              -- number of times the query has been executed
rows               -- total rows returned or affected

-- Block I/O
shared_blks_hit    -- buffer cache hits
shared_blks_read   -- blocks read from disk
shared_blks_written-- blocks written

-- WAL (PostgreSQL 13+)
wal_records        -- WAL records generated
wal_bytes          -- bytes of WAL generated

Prior to PostgreSQL 13, timing columns were named total_time, min_time, max_time, mean_time, and stddev_time. The _exec_ qualifier was added when planning time tracking was introduced.

Practical queries

Top queries by total time

If you run only one query against pg_stat_statements, make it this one. It shows which query patterns consume the most cumulative database time — and optimizing even a single entry near the top often has outsized impact.

-- Top 10 queries by total execution time
SELECT
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Slowest average queries

Finds queries with the highest average latency, filtered to patterns with at least 100 executions to avoid noise from one-off administrative queries.

-- Queries with the highest average latency (minimum 100 calls)
SELECT
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  round(min_exec_time::numeric, 2) AS min_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  query
FROM pg_stat_statements
WHERE calls >= 100
ORDER BY mean_exec_time DESC
LIMIT 10;

Worst cache hit ratios

Identifies queries that read the most data from disk rather than the buffer cache — candidates for missing indexes, bloated tables, or insufficient shared_buffers.

-- Queries with the worst cache hit ratio
SELECT
  calls,
  shared_blks_hit,
  shared_blks_read,
  round(
    shared_blks_hit::numeric /
    nullif(shared_blks_hit + shared_blks_read, 0) * 100, 2
  ) AS hit_pct,
  query
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 1000
ORDER BY hit_pct ASC
LIMIT 10;

Resetting statistics

After a deployment or schema change, reset to get a clean baseline. You cannot compare before and after without establishing a before.

-- Reset statistics (useful after deploying changes)
SELECT pg_stat_statements_reset();

Cloud availability

How Gold Lapel relates

I should be straightforward here: Gold Lapel performs its own query pattern analysis at the proxy level. It sees every query before it reaches PostgreSQL, normalizes it, tracks execution statistics, and uses those statistics to drive automatic optimizations — materialized views, index recommendations, and the like. So it does not require pg_stat_statements to do its work.

That said, when the extension is present, Gold Lapel reads it during startup to seed its understanding of your workload from historical data. This shortens the cold-start period from minutes to seconds. Rather than waiting to observe patterns from live traffic, Gold Lapel inherits the full history that pg_stat_statements has already gathered on your behalf.

If the extension is installed, Gold Lapel detects and uses it automatically. If it is not, Gold Lapel builds its workload model from scratch. Either path works. But if you have already done the work of installing pg_stat_statements — and you should have — the introduction is that much faster.