toplevel, and the sum that counts twice

A row per statement, except when a statement contains statements

The statements extension keeps one row per normalised statement per user per database, and every report built on it assumes those rows are disjoint. Total time by statement, share of total time, the top ten by mean duration: all of them are sums or ratios over rows that are supposed to describe separate pieces of work.

That assumption holds only while every tracked statement came straight from a client. It stops holding the moment the extension is set to track nested statements as well, which is the setting most fleets reach for after the first time a report says all the time went into a single function call. With nested tracking on, a function’s body is tracked and so is the call that invoked it, and the call’s recorded duration includes the body. The rows overlap, and every sum over them counts the inner work twice.

Before PostgreSQL 14 there was nothing in the view saying which rows were which. You could sometimes guess from the text. PostgreSQL 14 added a boolean saying whether the statement was executed at the top level, which turns a guess into a filter, and a small companion view reporting the extension’s own state. Both are recorded under pg_stat_statements, in the Additional Modules part of the release notes rather than anywhere near the server’s own monitoring entries, which is one reason they are missed.

The double count, made visible

The fixture is a function that inserts rows one at a time, which is a shape every application has somewhere, and a single call to it.

CREATE EXTENSION pg_stat_statements;
CREATE TABLE ledger (id integer PRIMARY KEY, amount numeric);
CREATE FUNCTION post_batch(n integer) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  FOR i IN 1..n LOOP
    INSERT INTO ledger VALUES (i, i * 1.5);
  END LOOP;
END $$;

CREATE EXTENSION
CREATE TABLE
CREATE FUNCTION

SELECT pg_stat_statements_reset();
SELECT post_batch(500);
SELECT toplevel, calls, round(total_exec_time::numeric, 1) AS total_ms, left(query, 42) AS statement
FROM pg_stat_statements
WHERE query LIKE '%ledger%' OR query LIKE '%post_batch%'
ORDER BY toplevel DESC;

 pg_stat_statements_reset 
--------------------------
 
(1 row)

 post_batch 
------------
 
(1 row)

 toplevel | calls | total_ms |               statement               
----------+-------+----------+---------------------------------------
 t        |     1 |     12.6 | SELECT post_batch($1)
 f        |   500 |      4.1 | INSERT INTO ledger VALUES (i, i * $4)
(2 rows)

Two rows, and adding their times together gives a figure larger than the wall-clock time the work took, because the five hundred inserts happened inside the one call. A report that sums this column without a filter reports a database busier than it was. The filter is the boolean, and it is the whole point of the column.

The correct treatment depends on the question. For “where did the time go”, keep only the top-level rows, because their totals already include everything beneath them. For “which statement is slow”, the nested rows are the interesting ones, because a function whose body contains one terrible query is indistinguishable from a function that is slow all over until you look inside it.

One detail in that output is not in any release note and looks like a typo until you know why. The placeholder numbering in the nested statement does not start at one. The normalisation numbers constants across the whole tracked unit, so a nested statement’s parameters carry on from wherever the enclosing context stopped, and two nested statements from the same function have different-looking text for the same shape. It does not affect the identifier or the grouping. It does mean that matching statement text between the nested rows and anything else you have recorded will fail on the numbers.

The default that makes this invisible

The behaviour above is not what a cluster does out of the box, and knowing which side yours is on takes one query.

SELECT current_setting('pg_stat_statements.track') AS track,
       count(*) FILTER (WHERE NOT toplevel) AS nested_rows,
       count(*) AS rows_total
FROM pg_stat_statements;

 track | nested_rows | rows_total 
-------+-------------+------------
 all   |           1 |          4
(1 row)

The default for that setting is top-level only, and with it there are no nested rows, every row has the boolean set, and filtering on it changes nothing. That is the situation on most clusters, and it is why this column can sit unused for years and then matter enormously the week somebody switches tracking on to debug a function.

So the practical rule is not “always filter”. It is that any query you write against this view should filter explicitly, so that the answer does not change meaning the day a setting changes underneath it. A report that was correct on a cluster tracking only top-level statements and silently becomes an over-count on one tracking everything is the exact failure this column exists to prevent, and leaving the filter out is what makes it possible.

The extension’s own health

The companion view has two columns and one of them is the one people need.

SELECT dealloc, stats_reset FROM pg_stat_statements_info;

 dealloc |          stats_reset          
---------+-------------------------------
       0 | 2026-09-17 16:36:30.419811+00
(1 row)

The extension holds a fixed number of entries. When it runs out it throws away the least-used ones to make room, and before 14 that happened silently. The counter says how many times it has done so. A cluster whose applications generate a lot of distinct statement shapes, which usually means an application building SQL by string concatenation rather than with parameters, will evict constantly, and the consequence is not a missing row. It is that every report over the view is computed from a window that keeps shrinking, and the queries most likely to be evicted are the rare ones, which are often the ones you were looking for.

A counter that is zero and stays zero means the entry limit is comfortable. A counter climbing steadily means the limit is too small for the workload, or the workload is generating shapes it should be parameterising, and the two need different fixes. Raise the limit and you use more shared memory and need a restart; fix the application and you fix the cause.

This view is where you check before believing any report built on the extension, and it is a two-column read with no cost.

Eviction, and the window a report is really computed over

The eviction counter above is a symptom, and the setting behind it is the entry limit. It is fixed at server start, it is shared memory, and it is the reason this extension can be left on in production at all: the view never grows, so a workload with unbounded statement shapes costs a bounded amount of memory and loses rows instead.

Which rows it loses is the part that matters for a report. The entries chosen for eviction are the least recently used ones, which sounds fair and is exactly wrong for diagnosis. The statement that ran twice last night and took four minutes each time is precisely the entry that gets discarded, while the statement running a thousand times a second is never at risk. So a report over this view is systematically biased towards frequent work, and the bias gets worse the faster the eviction counter climbs.

Two things reduce it and neither is free. Raising the entry limit costs shared memory and a restart. Reducing the number of distinct shapes costs an application change, and it is the better fix because unbounded shapes usually mean values being concatenated into SQL rather than passed as parameters, which is a security problem before it is a monitoring one.

The third option is to sample the view on a schedule and keep the samples, which is what most fleets end up doing for other reasons. It does not stop the eviction; it means the row was recorded before it vanished. On this version that sampling is also how you get a window at all, because the view carries no per-row timestamp saying when each entry started accumulating. PostgreSQL 17 added one, which is worth knowing when you plan the upgrade, because it makes “over the last hour” answerable without keeping your own history.

What survives the upgrade

Both surfaces are unchanged on later majors, which makes this page unusual for a version leaving support: nothing here needs rewriting when the cluster moves.

SELECT pg_stat_statements_reset();
SELECT post_batch(500);
SELECT toplevel, calls, round(total_exec_time::numeric, 1) AS total_ms, left(query, 42) AS statement
FROM pg_stat_statements
WHERE query LIKE '%ledger%' OR query LIKE '%post_batch%'
ORDER BY toplevel DESC;

   pg_stat_statements_reset    
-------------------------------
 2026-09-17 16:36:31.223611+00
(1 row)

 post_batch 
------------
 
(1 row)

 toplevel | calls | total_ms |               statement               
----------+-------+----------+---------------------------------------
 t        |     1 |      8.7 | SELECT post_batch($1)
 f        |   500 |      4.4 | INSERT INTO ledger VALUES (i, i * $4)
(2 rows)

The trap is the same shape and the same size, which is worth saying because the rest of the extension did change around it. The block timing columns were renamed in 17 to separate shared buffers from local ones, so a query naming the old ones errors on any cluster newer than 16, and the identifier itself is computed differently from 18, so the key your history is stored under does not carry across. If you are rewriting statement reports for an upgrade, this is the moment to add the filter you should already have had, because you are editing the query anyway.

What it costs to collect

The boolean and the eviction counter cost nothing beyond what the extension already does. The setting that makes the boolean meaningful costs a great deal more, and the trade is worth stating: tracking nested statements multiplies the number of entries a workload occupies, which increases eviction pressure on a fixed entry limit, which is the exact thing the companion view measures.

So the two features work against each other, and the sequence to follow is to check the eviction counter first, switch nested tracking on for as long as the investigation takes, and check the counter again. The setting can be changed with a reload; the entry limit cannot, and finding out that you needed a larger one only after a restart is the annoying half of this.