Using pg_stat_statements for Query profiling and performance tuning

Database performance problems are often mysterious. Queries slow down, CPU usage spikes, or users complain about latency, but pinpointing the cause requires visibility into what your database is actually doing. pg_stat_statements is PostgreSQL’s answer to this challenge.

pg_stat_statements is an extension that tracks execution statistics for every normalized (fingerprinted) SQL statement. Instead of logging millions of nearly-identical queries, it groups similar statements together (with constants replaced by placeholders), aggregating their execution metrics into a single fingerprint. This approach provides comprehensive query-level insights with minimal performance overhead and storage cost.

What problems does pg_stat_statements solve?

• Identifying resource hogs: Find the queries consuming the most CPU, memory, or I/O across your entire workload
• Discovering missing indexes: Detect queries with poor cache hit ratios or unexpected full table scans
• Spotting performance anomalies: Surface queries with inconsistent execution times that might indicate plan instability or contention
• Optimizing the high-value targets: Prioritize tuning efforts by focusing on statements that run frequently or consume significant resources
• Understanding write workloads: Analyze WAL generation, temporary file usage, and buffer churn to optimize write-heavy operations

Without pg_stat_statements, you’d rely on slow-query logging (which may miss queries below your threshold), application-level monitoring (which is incomplete), or manual EXPLAIN runs on guessed problem queries. With pg_stat_statements, you get a data-driven view of your entire query workload automatically.

Who should use this guide

This guide is for anyone managing PostgreSQL performance: DBAs tuning production systems, developers optimizing application queries, SREs troubleshooting production incidents, and architects planning capacity. You’ll need basic SQL knowledge and access to a PostgreSQL instance (version 9.1 or later; pg_stat_statements works across all modern PostgreSQL versions).

Getting started: setup and configuration

Installation

The pg_stat_statements extension comes built into PostgreSQL, but you must explicitly enable it. First, add it to the shared libraries in your postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Then restart PostgreSQL for this setting to take effect. After restarting, create the extension in each database where you want to track queries:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify the extension is active:

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_stat_statements';

Critical configuration parameters

Once installed, configure these parameters in postgresql.conf for optimal results:

# track all queries, including those in functions and procedures
pg_stat_statements.track = all

# increase from default 5000 to accommodate larger workloads
# each entry consumes ~1KB of memory
pg_stat_statements.max = 10000

# enable I/O timing analysis (essential for diagnosing slow queries)
track_io_timing = on

Why each matters:

• track = all (default is ’top’): By default, pg_stat_statements only tracks top-level statements. Setting this to ‘all’ captures statements executed inside stored procedures, functions, and triggers. For applications heavily using stored procs or ORMs that wrap queries, this is essential for complete visibility.

• pg_stat_statements.max = 10000 (default is 5000): This sets the maximum number of unique query fingerprints to track. With the default, once you reach 5000 unique queries, older entries start getting deallocated to make room. For high-traffic databases with diverse workloads, this is often insufficient. Increasing to 10000 (or higher, depending on your workload diversity) ensures you don’t lose tracking data. Each entry uses approximately 1KB of memory, so a increase from 5000 to 10000 adds roughly 5MB, which is negligible on modern systems.

• track_io_timing = on: This enables measurement of time spent waiting on I/O operations. Without this, the I/O timing columns in pg_stat_statements will always be zero, making it impossible to diagnose I/O-bound bottlenecks. The overhead is minimal on most systems but can be noticeable under very high load. If you must disable it, be aware you lose I/O diagnostics.

Monitoring extension health

PostgreSQL 14+ provides the pg_stat_statements_info view, which shows statistics about the extension itself:

SELECT
  dealloc,
  stats_reset
FROM pg_stat_statements_info;

If the dealloc column is incrementing rapidly (hundreds per day), it means you’re hitting your pg_stat_statements.max limit and entries are being deallocated before you can analyze them. Increase pg_stat_statements.max or investigate why you have so many unique query fingerprints.

Common setup issues

Issue: Extension enabled but view doesn’t exist

ERROR: relation "pg_stat_statements" does not exist

Solution: The extension may not be created in this specific database. Run CREATE EXTENSION pg_stat_statements; in the database where you want to query it. Extensions are database-specific.

Issue: I/O columns always show zero

Solution: You forgot to enable track_io_timing = on. This must be set in postgresql.conf and requires a server restart. Session-level changes don’t work for this parameter.

Issue: No data appearing after enabling the extension

Solution: Verify the extension is loaded by checking shared_preload_libraries. If you added it without restarting PostgreSQL, changes won’t take effect. Execute SELECT * FROM pg_stat_statements LIMIT 1; to confirm data is being collected.

Understanding pg_stat_statements fields

pg_stat_statements aggregates metrics across multiple executions of similar queries. Here’s what each key field represents:

Query identification

Execution counts and volume

Timing metrics (all in milliseconds)

Planning metrics (all in milliseconds)

PostgreSQL separates planning (building the execution plan) from execution (running it). Most queries are planned once and executed many times, but complex ORMs or dynamic SQL can cause frequent replannings.

Buffer activity metrics

These metrics show how queries interact with PostgreSQL’s buffer cache—the in-memory cache of data blocks.

Temporary and local buffer metrics

I/O timing metrics (all in milliseconds)

These metrics measure actual time waiting on I/O. Only populated if track_io_timing = on.

Write workload metrics

JIT compilation metrics (PostgreSQL 11+)

PostgreSQL can compile portions of query execution plans to machine code using LLVM, potentially speeding up CPU-intensive operations.

Statistics management

Performance tuning workflow

Understanding the fields is one thing; using them systematically to improve performance is another. Follow this workflow to diagnose and optimize your database.

Step 1: Establish a baseline

Before analyzing, reset pg_stat_statements to clear old data:

SELECT pg_stat_statements_reset();

Then run your representative workload (for up to 24 hours; make sure you have captured a full cycle of all queries and don’t forget about batch, cron, or ETL jobs). Check the stats_since timestamp to verify your analysis window:

SELECT stats_since
FROM pg_stat_statements
LIMIT 1;

All metrics are cumulative from this timestamp, so you’ll know exactly how much time your data covers.

Step 2: Identify quick wins

Start with the highest-impact queries—those that consume the most resources.

Top queries by total execution time

These are your biggest resource consumers. Even modest improvements multiply across many executions.

SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS mean_time_ms,
  round(100.0 * total_exec_time::numeric / sum(total_exec_time::numeric) OVER (), 2) AS pct_of_total,
  left(query, 100) AS sample_query
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 15;

The pct_of_total column shows what percentage of database time this query consumes. Queries above 5% are prime optimization targets. Focus on those with high pct_of_total AND high call counts—small improvements here compound.

Interpretation:

• A query with calls=1000000 and mean_exec_time=0.5ms totals 500 seconds. Optimizing it to 0.4ms saves 100 seconds database-wide.
• A query with calls=10 and mean_exec_time=5000ms also totals 50 seconds. Optimizing it is lower impact (unless an SLA requires faster response times).

Top queries by mean execution time

These are consistently slow queries, important for response-time SLOs.

SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  left(query, 100) AS sample
FROM pg_stat_statements
WHERE query NOT ILIKE '%SET %'
  AND query NOT ILIKE '%pg_stat%'
ORDER BY mean_exec_time DESC
LIMIT 15;

In general, queries with mean_exec_time > 1000ms (1 second) are worth investigating, especially if they’re user-facing. The max_exec_time column reveals whether slowness is consistent or sporadic.

Step 3: Diagnose root causes

Once you’ve identified a problem query, investigate why it’s slow. The following queries help determine the root cause.

Cache hit ratio analysis

Poor cache performance often indicates missing indexes or insufficient memory.

SELECT
  queryid,
  calls,
  shared_blks_hit,
  shared_blks_read,
  CASE
    WHEN (shared_blks_hit + shared_blks_read) > 0
    THEN round(shared_blks_hit::numeric / (shared_blks_hit + shared_blks_read), 4)
    ELSE NULL
  END AS cache_hit_ratio,
  left(query, 100) AS sample
FROM pg_stat_statements
WHERE (shared_blks_hit + shared_blks_read) > 0
ORDER BY (shared_blks_hit + shared_blks_read) DESC
LIMIT 20;

Guideline for interpreting cache statistics:

• Ratio > 0.99: Excellent. Working set fits in buffer cache.
• Ratio 0.90–0.99: Good. Some disk reads, but mostly cache hits.
• Ratio 0.50–0.90: Warning. Significant disk I/O. Investigate missing indexes or insufficient shared_buffers.
• Ratio < 0.50: Poor. Consider adding indexes, increasing shared_buffers, or filtering unnecessary columns.

Common causes of low cache hit ratio:

• Missing index on WHERE clause or JOIN conditions (causing full table scans)
• Query scanning large tables when it should filter to a smaller result set
• shared_buffers undersized relative to working set (typical config is 25% of system RAM)
• Multiple queries competing for cache, causing one workload to evict another’s data

Actions to take:

1. Analyze the query with EXPLAIN (ANALYZE, BUFFERS) to find full table scans
2. If a full scan is necessary, it’s working as designed; consider if the query logic is correct
3. If a full scan is unexpected, create an index on the filter column(s)
4. After creating an index, reset stats and re-run the query to verify cache hit ratio improves

I/O bound vs CPU bound analysis

Determine whether queries are bottlenecked by I/O (disk speed) or CPU (computation).

SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round((shared_blk_read_time::numeric + shared_blk_write_time::numeric) / mean_exec_time::numeric * 100, 1) AS io_pct,
  round((shared_blk_read_time::numeric + shared_blk_write_time::numeric), 2) AS io_time_ms,
  left(query, 100) AS sample
FROM pg_stat_statements
WHERE mean_exec_time > 0
ORDER BY (shared_blk_read_time + shared_blk_write_time) DESC
LIMIT 15;

Interpretation:

• io_pct > 50%: I/O bound. Bottleneck is disk speed. Consider: indexes, larger buffer cache, query optimization, or NVMe storage.
• io_pct < 10%: CPU bound. Bottleneck is CPU cycles. Consider: reducing computation, using indexes to reduce rows processed, or improving query plan.

Inefficient row fetching

Queries returning massive row volumes relative to execution frequency are candidates for optimization.

SELECT
  queryid,
  calls,
  rows,
  round(rows::numeric / NULLIF(calls, 0), 1) AS avg_rows_per_call,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  left(query, 100) AS sample
FROM pg_stat_statements
WHERE calls > 100
ORDER BY (rows::numeric / NULLIF(calls, 0)) DESC
LIMIT 20;

Interpretation:

• avg_rows_per_call > 10000: Query returns huge result sets on average. Consider: adding LIMIT, filtering rows earlier, or pagination.
• avg_rows_per_call varies widely: Use stddev_exec_time to check for variability. If high, the query might be inefficient on some parameters but not others (parameter skew).

Execution plan instability

High variability in execution time suggests inconsistent query plans or contention.

SELECT
  queryid,
  calls,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round((stddev_exec_time::numeric / NULLIF(mean_exec_time, 0)::numeric), 2) AS stddev_ratio,
  round(min_exec_time::numeric, 2) AS min_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  left(query, 100) AS sample
FROM pg_stat_statements
WHERE mean_exec_time > 0 AND calls > 100
ORDER BY stddev_exec_time DESC
LIMIT 15;

Interpretation:

• stddev_ratio > 2.0: Very high variability. Execution time varies by more than 2x the mean. Causes: plan changes due to parameter-dependent estimation, lock contention, cache cold-starts, or cache line competition.
• stddev_ratio > 0.5 but < 2.0: Moderate variability. Watch for trends; if getting worse, investigate.

Debugging steps:

1. Check plans column: if high relative to calls, the plan is being regenerated frequently (enable prepared statements)
2. Look at min vs max: if min is reasonable but max is huge, suspect lock contention or cache-sensitive performance
3. Run EXPLAIN (ANALYZE) multiple times; if plan changes between runs, suspect parameter-dependent estimates (use generic plans or parameterization)

Temporary file spillover

Queries spilling to disk indicate insufficient work_mem or missing indexes.

SELECT
  queryid,
  calls,
  round(temp_blk_read_time::numeric + temp_blk_write_time::numeric, 2) AS temp_io_ms,
  temp_blks_read,
  temp_blks_written,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  left(query, 100) AS sample
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY (temp_blks_written + temp_blks_read) DESC
LIMIT 10;

Interpretation:

• High temporary file I/O indicates sorts or hash aggregations that didn’t fit in work_mem (default 4MB).

Actions:

1. Increase work_mem (session or global) and re-test
2. Look for missing indexes on ORDER BY or GROUP BY columns
3. Consider if query logic can be simplified

Planning overhead

High planning time suggests complex queries or lack of prepared statement usage.

SELECT
  queryid,
  plans,
  calls,
  round(total_plan_time::numeric, 2) AS total_plan_ms,
  round(mean_plan_time::numeric, 2) AS mean_plan_ms,
  round(total_exec_time::numeric, 2) AS exec_ms,
  round(100.0 * total_plan_time::numeric / (total_plan_time::numeric + total_exec_time::numeric), 1) AS plan_pct,
  left(query, 100) AS sample
FROM pg_stat_statements
WHERE (total_plan_time + total_exec_time) > 0
ORDER BY total_plan_time DESC
LIMIT 10;

Interpretation:

• plan_pct > 5%: Planning consumes a non-trivial fraction of total time.
• plans = calls: Plan is never cached. If query is called frequently, use prepared statements or parameterized queries to cache the plan.

Step 4: Optimize and validate

After identifying root causes, optimize the query (add an index, rewrite the query, increase work_mem, etc.). Then validate improvements:

1. Reset pg_stat_statements: SELECT pg_stat_statements_reset();
2. Re-run the same workload
3. Re-run your diagnostic queries to compare metrics

For example, if you added an index to improve cache hit ratio:

-- Before optimization
SELECT mean_exec_time FROM pg_stat_statements WHERE queryid = 123456;
-- Result: 150.5 ms

-- After index and reset/retest
SELECT mean_exec_time FROM pg_stat_statements WHERE queryid = 123456;
-- Result: 45.2 ms -- 70% improvement!

Common performance investigation patterns

The slow dashboard

A user-facing dashboard query has mean_exec_time of 2500ms (2.5 seconds), exceeding your SLA.

Investigation:

-- Find the dashboard query
SELECT
  queryid, calls, mean_exec_time, max_exec_time, rows,
  shared_blks_hit, shared_blks_read,
  left(query, 150)
FROM pg_stat_statements
WHERE query ILIKE '%dashboard%'
ORDER BY mean_exec_time DESC;

-- Check cache hit ratio
SELECT
  (shared_blks_hit::numeric / (shared_blks_hit + shared_blks_read)) AS hit_ratio,
  shared_blks_read,
  shared_blks_hit
FROM pg_stat_statements
WHERE queryid = 123456; -- from the query above

If cache hit ratio is low (< 80%), the query is missing an index or scanning too much data. Run EXPLAIN (ANALYZE, BUFFERS) on the actual query to identify full table scans, then add appropriate indexes.

If the cache hit ratio is good but the query is still slow, check CPU-bound metrics:

SELECT
  mean_exec_time, rows, mean_exec_time / NULLIF(rows, 0) AS ms_per_row
FROM pg_stat_statements
WHERE queryid = 123456;

If ms_per_row is high, the query is doing expensive computation per row. Consider materialized views, denormalization, or query rewriting.

The resource hog

A nightly batch process is consuming 30% of database resources. Identify it:

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  round(100.0 * total_exec_time::numeric / sum(total_exec_time::numeric) OVER (), 2) AS pct_of_total,
  left(query, 150)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 1;

Then optimize using the diagnosis queries above. If multiple related queries are slow, consider batching them or using temporary tables to avoid redundant scans.

High write volume affecting replication lag

Replication is falling behind. Check WAL generation:

SELECT
  queryid, calls, wal_bytes, wal_records,
  round(wal_bytes / NULLIF(calls, 0), 0) AS bytes_per_call,
  left(query, 150)
FROM pg_stat_statements
WHERE wal_bytes > 0
ORDER BY wal_bytes DESC
LIMIT 10;

High WAL bytes per call indicate the queries are write-heavy. Consider:

• Batching writes (fewer, larger operations instead of many small ones)
• Reducing write frequency if safe (e.g., batch logging instead of per-request)
• Improving index efficiency on writes (too many indexes = more WAL)

Advanced analysis techniques

Combining pg_stat_statements with table statistics

Correlate slow queries with table statistics to identify missing indexes:

SELECT
  pss.queryid,
  pss.calls,
  pss.mean_exec_time,
  pss.shared_blks_read + pss.shared_blks_hit AS total_blocks,
  pst.n_live_tup,
  pst.last_vacuum,
  left(pss.query, 100) AS query
FROM pg_stat_statements pss
LEFT JOIN pg_stat_user_tables pst ON pss.query ILIKE '%' || pst.relname || '%'
WHERE pss.mean_exec_time > 100
ORDER BY pss.total_exec_time DESC
LIMIT 15;

If a query accessing a large table (n_live_tup is high) has poor cache hit ratio, suspect missing indexes.

Detecting parameter skew

Some parameters cause very different execution plans. Find them:

SELECT
  queryid,
  plans,
  calls,
  round(100.0 * plans::numeric / NULLIF(calls, 0)::numeric, 1) AS plan_ratio,
  round(stddev_exec_time::numeric / NULLIF(mean_exec_time, 0)::numeric, 2) AS stddev_ratio,
  left(query, 100) AS sample
FROM pg_stat_statements
WHERE calls > 100 AND plans > 1
ORDER BY plan_ratio DESC
LIMIT 15;

High plan_ratio combined with high stddev_ratio suggests parameter-dependent planning. Enable prepared statements or use generic plans to avoid replannings.

JIT (Just-In-Time) compilation

PostgreSQL 11+ can compile query execution plans to machine code using LLVM, potentially accelerating CPU-intensive workloads. However, JIT has overhead—for short-running queries, compilation time may exceed execution benefits.

Enabling JIT

Add to postgresql.conf or set at session level:

SET jit = on;
SET jit_above_cost = 100000;        -- cost threshold for JIT eligibility
SET jit_inline_above_cost = 500000;  -- threshold for inlining functions
SET jit_optimize_above_cost = 500000; -- threshold for optimization

Analyzing JIT impact

Find queries where JIT is active and measure its effectiveness:

SELECT
  queryid,
  jit_functions,
  jit_generation_time + jit_inlining_time + jit_optimization_time + jit_emission_time AS total_jit_time_ms,
  round(mean_exec_time::numeric, 2) AS mean_exec_ms,
  round(100.0 * (
    (jit_generation_time::numeric + jit_inlining_time::numeric + jit_optimization_time::numeric + jit_emission_time::numeric)
    / NULLIF(mean_exec_time, 0)::numeric
  ), 1) AS jit_overhead_pct,
  left(query, 100) AS sample
FROM pg_stat_statements
WHERE jit_functions > 0
ORDER BY total_jit_time_ms DESC
LIMIT 10;

Interpretation:

• jit_overhead_pct > 20%: JIT overhead may outweigh benefits. Consider disabling JIT for this query or lowering jit_above_cost.
• jit_overhead_pct < 5%: JIT is efficient; keep enabled.

For queries where JIT overhead is high, try disabling JIT for that session:

SET jit = off;
-- run query
RESET jit;

Then compare execution times. If performance improves with JIT off, adjust cost thresholds to exclude this query.

Troubleshooting and pitfalls

Issue: All I/O timing columns show zero

Cause: track_io_timing = on is not enabled.

Solution: Add to postgresql.conf and restart:

track_io_timing = on

Issue: Query data missing or incomplete

Cause: pg_stat_statements.max reached; old entries are deallocated.

Solution: Check the info view and increase the limit:

SELECT dealloc FROM pg_stat_statements_info;

If deallocations are frequent, increase pg_stat_statements.max in postgresql.conf and restart.

Issue: Statistics reset unexpectedly

Cause: PostgreSQL was restarted or someone manually ran pg_stat_statements_reset().

Solution: Set stats_since baseline before analysis and document the window. Use SELECT pg_stat_statements_reset(); intentionally before benchmarking.

Issue: Variance metrics (stddev, min, max) not available

Cause: PostgreSQL < 13, where these were not tracked.

Solution: Use cumulative metrics (total_exec_time) to estimate variance, or upgrade PostgreSQL.

Common pitfall: Ignoring low-volume queries

Even though a query runs only 10 times, if each execution takes 30 seconds, that’s 5 minutes total. Don’t dismiss it just because calls is low. Look at your queries from various angles. For example, look at total_exec_time, not just mean_exec_time.

Common pitfall: Over-optimizing after one anomalous execution

A query ran 100 seconds once (max_exec_time) but averages 5ms. The anomaly was likely a one-time contention event or cache-miss scenario. Focus on the mean, not the max, unless SLAs require tail-latency guarantees.

Common pitfall: Not accounting for stats collection window

When comparing two time periods, ensure you’re comparing equivalent analysis windows. If you collected stats for 1 hour vs. 24 hours, ratios won’t be meaningful. Always check stats_since and reset before new analysis.

Conclusion

pg_stat_statements is your window into query-level database performance. By systematically analyzing its metrics, from total execution time to buffer activity to WAL generation, you can identify optimization opportunities, track performance trends, and maintain healthy database performance over time.

The key to effective use is making it a routine practice:

1. Establish baselines by resetting statistics before analysis windows
2. Prioritize by impact: Focus on queries consuming the most resources overall, not just those that are individually slow
3. Diagnose systematically: Use cache hit ratios, I/O analysis, and execution variance to understand root causes
4. Validate improvements: Always re-measure after optimization to confirm the change helped
5. Monitor continuously: Weekly reviews keep you ahead of performance degradation

Combined with EXPLAIN ANALYZE for deep query-plan analysis and pg_stat_user_tables for table-level statistics, pg_stat_statements completes your PostgreSQL performance diagnostic toolkit.