What pg_stat_statements Actually Tells You About Your Queries

The article walks through enabling the pg_stat_statements extension on a PostgreSQL 16 instance, stressing that the module must be pre‑loaded via shared_preload_libraries and configured with pg_stat_statements.max (5 000 default), track=all and track_planning=on. After resetting the counters, the author runs a query that orders fingerprints by total_exec_time and displays calls, average execution time, planning time, rows per call, and cache‑hit percentage. In the sample deployment—a 1.26 GB device_metrics table partitioned into 500 daily slices, 256 MB of shared buffers, and a mixed workload of 20 k device lookups, 20 k metadata lookups, 200 batch inserts, 20 aggregates, and three retention deletes—the top fingerprint is a daily aggregation that ran only 20 times but consumed 252 seconds of CPU, returning roughly 490 rows per execution with a 1.5 % buffer‑hit rate. By contrast, a simple primary‑key lookup executed 20 k times, took 0.22 ms per call, and hit the buffer 96 % of the time, illustrating how high‑frequency, low‑latency queries can be invisible in a slow‑query log while still contributing modest CPU load. The article also flags planning time: the retention DELETE, called three times, spent 16 seconds planning—more than its execution—yet its total_exec_time appears near zero because vacuum work is accounted elsewhere. Buffer statistics further separate I/O‑bound scans from CPU‑bound work, showing that the aggregation fingerprint suffered massive cache misses, pulling 2.75 million 8 KB blocks from disk.

This deep dive reflects a broader shift toward observability tools that surface aggregate resource consumption rather than isolated latency spikes. As PostgreSQL adds native planning‑time metrics in version 13, DBAs can now differentiate “expensive” from merely “slow” statements, a capability previously reserved for external profilers. The emphasis on fingerprinting—grouping queries by structure and stripping literals—mirrors trends in APM platforms that de‑duplicate similar calls to avoid noise. Moreover, the need to tune pg_stat_statements.max and track=all highlights the growing complexity of managing telemetry in large, partitioned schemas where function‑wrapped queries otherwise hide their true cost.

Looking ahead, teams should monitor the planning‑time column for queries that spend disproportionate time in the optimizer, especially when using complex partition pruning or generated columns. Because DELETE and vacuum work are split across statistics, reliance on total_exec_time alone can understate the impact of data‑retention jobs; pairing it with buffer‑hit ratios and explicit vacuum metrics will give a fuller picture. Finally, as workloads scale, administrators must periodically reset pg_stat_statements or rotate its storage to prevent stale data from masking recent regressions.