The Good Tech Companies - What pg_stat_statements Actually Tells You About Your Queries

Episode Date: September 2, 2026

This story was originally published on HackerNoon at: https://hackernoon.com/what-pg_stat_statements-actually-tells-you-about-your-queries. Learn how to use pg_stat_statements to find PostgreSQL queries that consume the most execution and planning time, even when they run in milliseconds. Check more stories related to undefined at: https://hackernoon.com/c/undefined. You can also check exclusive content about #postgresql-query-analysis, #slow-query-optimization, #postgresql-partition-pruning, #pg_stat_statements, #planning-time-optimization, #postgresql-query-performance, #query-fingerprint-monitoring, #good-company, and more. This story was written by: @tigerdata. Learn more about this writer by checking @tigerdata's about page, and for more stories, please visit hackernoon.com. Your slowest PostgreSQL query isn't necessarily your most expensive. This guide uses pg_stat_statements to analyze real query fingerprints by total execution time, call frequency, buffer usage, and planning time. It shows how a 0.2ms query can consume more planning time than every other statement combined, how partition pruning creates hidden costs, and where pg_stat_statements' blind spots begin.

This audio is presented by Hacker Noon, where anyone can learn anything about any technology. What pg underscore stat underscore statements actually tells you about your queries by Tiger Data, creators of timescale DB. Your slowest query is rarely your most expensive one. A four-second report that runs twice a day costs your database eight seconds. A three millisecond lookup that runs 40,000 times a minute costs at two minutes of CPU every 60 seconds. Only one of those shows up in a slow query log, and it is the wrong one, settles this argument. It keeps a running total of every top-level statement your server executes, group by structure rather than by literal text.

Each group is a fingerprint, with one row in the view representing every execution of the same query shape, with the constants stripped out. Every number in this guide is captured from a real instance, and one query on it spent more time being planned than every other statement combined. It runs in 0.2 milliseconds, what you will learn, how normalizes queries into a fingerprint, and what that collapses, which columns matter, and the buffer counters, how to read a real result set and tell an expensive query from a merely slow one. How to find planning dominated queries, which hides completely, what the extension does not capture, so you know when to stop trusting it. Before you start,

you need PostgresQL 13 or later. The column names used here landed in version 13, on 12 and earlier they are and, and planning time is not tracked at all. You need superuser access to edit and restart the server, and the role you query with needs. Without it the view still returns rows, but every query column reads. Enable PG-Stat-Underscore statements. Allocates a fixed block of shared memory at Postmaster Startup, so it has to bind. This is not a pure install. Edit first, then restart. Then, in PSQL, check the last two statements. not the first. Succeeds whether or not the library is preloaded, so it proves nothing on its own.

Should list the extension, and the count should be non-zero within seconds of normal traffic. If instead you get error, must be loaded via, the config edit or the restart did not work. Three settings change what you see, caps tracked fingerprints at 5,000 by default, and passed that post-gress evicts entries by a decaying usage score, roughly least recently used, is off by default, so reads as zero until you enable it. That one is not optional here. It is where this article's main finding comes from. And track defaults to top, recording only the outermost statement,

so if your logic lives in place, PGSQL functions every nested query is built to the wrapper. Set it to all. Reset the counters before you measure. The view accumulates from the last reset on a long-running server that can mean two years of history spanning a migration, a bad deploy, and a scheme. change you have since reverted. Ranked by aggregate cost, not per call cost. This query does most of the work. It ranks fingerprints by total execution time and puts the evidence beside each one. Here is the actual output from an example Postgrescue L-16 instance holding 5 million rows in a table across 500

daily partitions, 1.26 gigabytes on disk against 256 megabytes of, after a mixed workload of 20,000 device lookups, 20,000 metadata lookups, 200 batch inserts, 20 reporting aggregates, and three retention deletes. Query underscore fragment calls total underscore sec mean underscore MS plan underscore CcaviG underscore Roshit underscore PCT 20, 251, 912459-540,34,0004,40, 22,000, 1,337, 895, 7,200,090, 0440, 21,251,283,884 trillion 305 billion 1,0,016. 09101-1135. 4 2 columns decide the question, and they point in different directions, is what a fingerprint costs your server, while is what it costs one user.

Reap them as a pair and four cases fall out. High calls with low is an application problem. A query in a loop, an ORMN plus one, or a dashboard pulling faster than anyone reads it, and the fix is batching or caching rather than indexing. Low calls with high as a query problem. A missing index, a bad join order, or a scan wider than the result needs. High on both is where you start. Low on Bothe's the healthy majority, and leaving it alone is the correct action.

Every row below is one of those cases. Row 1 is 251, 9 seconds out of 258, 0 across every fingerprint on the server, so 98% of execution time belongs to a query called 20 times. Read next to TasiWi. It returns 489, 8 rows per call, one per day of retention, and does ITATTA 1. 5% cash hit rate. 20 calls read 2, 75 million blocks off disk to produce 9, 7,7906. rows. Row 5 is the trap from the intro. At 16 milliseconds per call the retention is the second

slowest statement on the box, the first thing an on-call engineer would flag. Three calls, 48 milliseconds total. Ignore it, with one caveat, under Bills any, because the dead tuples it leaves and the auto vacuum passes that clean them up are charged elsewhere. On a partition table, on the oldest partition does the same job in constant time with no vacuum debt. Use the buffer columns to set. Separate cache misses from CPU. Counts 8 kilobytes blocks served from the shared buffer cache. Counts blocks that had to come from the OS cache or disk. The column above derives from both, and Ida's worth selecting the raw counts alongside it,

because a ratio hides volume. Statements that touch no shared blocks come back blank rather than zero, which is the guard doing its job. Row 3 sits at 100.0%. It runs 20,000 times, never leaves memory, and costs zero. 9 seconds. That is what a healthy fingerprint looks like. Row 4 is the ingest floor, inserting 500 rows per call at 3. 8 milliseconds with a perfect hit rate. Neither is worth touching.

Row 1 sits at 1.5%. It scans A1, 26 gigabytes table through a 256 megabytes cache, so almost nothing it reads as resident, and every block it pulls evicts something another query wanted. That is red amplification, and no index fixes it. The query has to visit every row in the retention window to compute the average. The 213 seconds mean underscore Ms. cannot see. Now look at row 2. It executes in 0.221 milliseconds. By any per call measure I does the healthiest query in the workload.

However, it's as 213.0. That is 10. 65 milliseconds of planning for 0. 221 milliseconds of execution, 48 times more expensive to plan than to run, and 99. 7% of all planning time on the server. A slow query log would never show it, does not include it, confirms the ratio and names the cause. Run the query below twice in one session and read the second result. The first pass loads catalog entries for 500 partitions and reports planning time that includes them. 492 is the tell. The predicate is and is stable rather than constant,

Soth planner cannot prune at plan time. It builds a subplan for all. all 500 partitions, then discards 492 of them at execution. Resolving the timestamp in the application and passing a fixed value lets plan time pruning run instead. Run this in the same session, substituting a real date, and you may see the planning time drop to zero. 183 Miz is gone, same rows, same execution, 59 times less planning. To fix this, you have to swap for a genuine constant. If you use instead, nothing changes because is stable too and the planner still builds all 500 subplans. Prepared statements and generic plan caching help too. Fewer, larger partitions help more. Row two in the table was visible only because happened to sit in the ranking. Toesweep for the

pattern deliberately, sort by planning time and add the ratio. Sort by, and do not scan on its own. That ratio is a trap. A trivial index lookup often plans in more time than it executes. because both are microseconds, and on a scratch instance the same column reads 71. 8% for a query nobody should touch is the honest signal. Healthy lookups plan in tens of microseconds. Ro 2 planned in 10, 65 milliseconds, and multiplied across 20,000 calls that is the 213 seconds. Used to confirm what the absolute numbers already flagged, which for row 2 was 98. 0%. When the ranking stops changing, rows 1 and 2 are

two are both fixable. The pattern underneath them is not on a high frequency time series workload, and all climb as data volume and partition count grow, even when every query is written correctly. You fix the top fingerprint, it drops to fourth, and two quarters later it is back with the same shape. If your ranking regenerates itself after you fix what is on it, you are measuring architecture, not technique. Columnar storage changes the inputs rather than the query text. A pre-computed roll-up answers row 1 without touching 2. 75 million blocks. The optimization treadmill covers when optimizing further stops paying.

Know the blind spots. Normalization is what makes the ranking possible and also what limits it, and collapse into one fingerprint. If one value matches 4 billion rows and another matches an empty range, you get an average that describes neither. The view also gives you no percentiles, so check before trusting a healthy mean. And it stores no plans. It tells you a query got slower, never that the planner switched from an index scan to a sequential scan, fills that gap, but it needs preloading two, and it only logs statements over.

Catching a regression in a zero, two milliseconds query like row two means setting that threshold low enato hurt, so aim it at one fingerprint and turn it off afterward. One version note. Before PostgresQL 18, in lists of different lengths produced separate fingerprints, splitting. one logical query across several rows. Postgresql 18 merges them. Four more limits are covered above rather than here, because each one distorts a specific number as you read it. Bills nested place, PGSQL queries to the wrapper, evicts fingerprints once you pass its configured max, left off makes every red as zero, and under Bills any because the vacuum work it creates IS charged elsewhere. SORD by planning time next. Reset your statistics, run one peak cycle, and pull the top five 5 fingerprints by. Then run the same query ordered by, because on this instance that second list

found the cost center the first one missed. If the same fingerprints keep returning to the top after you fix them, start a Tiger Cloud trial today and run with rankings against Columnar Torridge to see which rows disappear. Thank you for listening to this Hackernoon story, read by artificial intelligence. Visit hackernoon.com to read, write, learn and publish.