pg_stat_statements, Query Fingerprints, Plan Variance, and Workload Ranking

Learning outcomes

Table counters tell us where work accumulated, but ServiceHub needs to know which statement shapes consumed database time, I/O, temporary blocks, and Write-Ahead Log (WAL). The PostgreSQL-supplied pg_stat_statements module aggregates normalized statement fingerprints across executions.

1. Preload is a server-start decision

SELECT name, setting, context, source, pending_restartFROM pg_settingsWHERE name IN (  'shared_preload_libraries',  'compute_query_id')ORDER BY name;SELECT name, default_version, installed_versionFROM pg_available_extensionsWHERE name = 'pg_stat_statements';

pg_stat_statements allocates shared memory and hooks statement planning/execution across the server, so adding/removing it requires shared_preload_libraries and a server restart. CREATE EXTENSION alone only creates the SQL-facing views/functions in one database; it cannot retrofit the server preload.

2. Configure without clobbering existing preload libraries

# Merge with existing required libraries; do not overwrite blindly.shared_preload_libraries = 'pg_stat_statements'compute_query_id = auto# Start conservatively:pg_stat_statements.track = top# Planning timing is OFF by default and adds overhead:pg_stat_statements.track_planning = off

If other preload modules already exist, keep them in the comma-separated list. Restart PostgreSQL, then verify pending_restart=false. compute_query_id=auto lets modules such as pg_stat_statements enable query IDs.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;SELECT extname, extversionFROM pg_extensionWHERE extname = 'pg_stat_statements';SELECT *FROM pg_stat_statements_info;

3. Generate one normalized query shape with different constants

Use the Chapter 21 workload table. Different literal tenant/status values should aggregate into a common parsed query shape when their analyzed structure is identical.

SELECT format(  'SELECT count(*) FROM app.ch21_workload WHERE tenant_id = %s AND status = %L;',  (g % 10) + 1,  CASE WHEN g % 2 = 0 THEN 'open' ELSE 'queued' END)FROM generate_series(1,40) AS g\gexec

SELECT r.rolname AS executed_by,       d.datname AS database_name,       s.toplevel,       s.queryid,       s.calls,       s.rows,       round(s.total_exec_time::numeric,2) AS total_exec_ms,       round(s.mean_exec_time::numeric,3) AS mean_exec_ms,       round(s.stddev_exec_time::numeric,3) AS stddev_exec_ms,       s.stats_since,       s.queryFROM pg_stat_statements AS sJOIN pg_database AS d ON d.oid = s.dbidLEFT JOIN pg_roles AS r ON r.oid = s.useridWHERE s.query LIKE 'SELECT count(*) FROM app.ch21_workload%'ORDER BY s.total_exec_time DESC;

Representative text replaces ignored constants with $1, $2, etc. The entry key also includes database/user/top-level dimensions. A query ID is a hash of the analyzed query structure, not a cryptographic identity and not guaranteed stable across PostgreSQL major versions.

4. Rank by the question you are asking

SELECT queryid,       calls,       round(total_exec_time::numeric,1) AS total_exec_ms,       round(mean_exec_time::numeric,3) AS mean_exec_ms,       rows,       shared_blks_hit,       shared_blks_read,       temp_blks_written,       pg_size_pretty(wal_bytes::bigint) AS wal_generated,       left(query,120) AS representative_queryFROM pg_stat_statementsWHERE dbid = (SELECT oid FROM pg_database WHERE datname=current_database())ORDER BY total_exec_time DESCLIMIT 10;

Total time finds workload-wide consumers. Mean/max time finds per-call latency problems. Calls finds chatty SQL. shared_blks_read/temp_blks_written suggests I/O or spill pressure. wal_bytes identifies write-heavy statement shapes. No single ordering answers every operational question.

SELECT queryid, calls,       round(mean_exec_time::numeric,3) AS mean_ms,       round(max_exec_time::numeric,3) AS max_ms,       temp_blks_read, temp_blks_written,       wal_records, wal_fpi, wal_bytesFROM pg_stat_statementsWHERE calls >= 5ORDER BY mean_exec_time DESCLIMIT 10;

5. Planning metrics are optional and have their own cost

SHOW pg_stat_statements.track_planning;SELECT queryid, plans, calls,       total_plan_time, mean_plan_time,       total_exec_time, mean_exec_timeFROM pg_stat_statementsWHERE query LIKE '%app.ch21_workload%'ORDER BY total_exec_time DESC;

If track_planning=off, plan counters/times are zero. Enabling planning tracking can add noticeable contention/overhead on highly concurrent repeated statement shapes. Enable it deliberately when plan-time diagnosis is worth that cost.

6. “Plan variance” must be measured, not inferred from timing spread alone

min_exec_time, max_exec_time, and standard deviation show execution-time variability, but they do not prove the optimizer chose multiple plan shapes. Cache state, lock waits, parameter selectivity, parallel-worker availability, and I/O can change execution time without changing the plan.

7. Connect a fingerprint to a representative current plan

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, VERBOSE)SELECT count(*)FROM app.ch21_workloadWHERE tenant_id = 7  AND status = 'open';

With query IDs enabled, EXPLAIN can display a Query Identifier. Compare it with the fingerprint entry, then inspect the current plan. This plan is evidence for this representative execution now; it is not the historical plan that produced every cumulative sample.

8. Resets and deallocation change the baseline

SELECT dealloc, stats_resetFROM pg_stat_statements_info;SELECT min(stats_since) AS oldest_entry_stats_since,       max(stats_since) AS newest_entry_stats_since,       count(*) AS tracked_entriesFROM pg_stat_statements;

If distinct statement shapes exceed pg_stat_statements.max, least-used entries are deallocated; dealloc shows how often this occurred. High deallocation means workload ranking can lose low-frequency fingerprints and representative query text behavior may become less stable. Reset events also break continuity with old dashboard rates.

9. Do not reset global history casually

-- Identify one exact entry first:SELECT userid, dbid, queryid, queryFROM pg_stat_statementsWHERE query LIKE 'SELECT count(*) FROM app.ch21_workload%'ORDER BY calls DESCLIMIT 1;-- Then, only if the lab/change procedure intentionally needs it:-- SELECT pg_stat_statements_reset(userid, dbid, queryid, false);

A global reset discards the evidence every database on the server has accumulated through the module. Production dashboards normally keep the counters and compute deltas externally.

10. Query text and security boundaries

Only superusers and roles with pg_read_all_stats can see other users' query text/query IDs in full. Normalization reduces literal variability but is not a secret-sanitization guarantee. Never put passwords/tokens into SQL text; utility commands and application-generated strings can still create sensitive observability data.

Authoritative references

Statistics and logging fields evolve across PostgreSQL majors. These PostgreSQL 18 primary sources define the mechanisms used in this lesson.

• PostgreSQL 18 — pg_stat_statements
• PostgreSQL 18 — Run-time Statistics / compute_query_id
• PostgreSQL 18 — EXPLAIN
• PostgreSQL 18 — Predefined Roles / pg_read_all_stats