Toplevel versus nested statements

A toplevel statement is one the client sent. A nested statement is one the server ran inside something else: a query in the body of a function, a statement in a procedure, a statement executed by another statement. pg_stat_statements records both, distinguishes them in its toplevel column, and only counts nested ones when pg_stat_statements.track is set to all rather than its default of top. The same work can therefore appear once, twice, or not at all, depending on a setting most people never look at.

The arithmetic that breaks a dashboard

With nested tracking on, a client call to a function that runs three queries produces four rows: the call itself, carrying the total time, and one row per inner query, each carrying its own share of that same time. Summing total_exec_time across the view now counts the inner work twice. Every “top queries by total time” panel written without a filter is doing this, and the error scales with how much of the application lives in functions, which is exactly the systems where the panel matters most.

The opposite setting has the opposite flaw. Leave tracking at top and a database whose logic is in stored procedures reports one row per procedure and no visibility into which statement inside it is slow. The view is correct and useless.

So the column is not a curiosity, it is the filter that makes the view mean one thing. Sum over toplevel true for what the application asked for. Sum over toplevel false, or read individual rows, for where the time went inside. Never sum over both.

One more detail decides whether you can do this at all: the column arrived in PostgreSQL 14. On 13 and earlier, nested statements were recorded with no way to tell them apart, which is why old dashboards carried the double count without anyone noticing.

Seeing one call become two rows

Turning nested tracking on for the session is enough to show the pair.

CREATE EXTENSION pg_stat_statements;
SET pg_stat_statements.track = 'all';
CREATE FUNCTION active_backends() RETURNS bigint LANGUAGE sql
  AS $$ SELECT count(*) FROM pg_stat_activity $$;
SELECT active_backends();
SELECT toplevel, calls, query FROM pg_stat_statements
 WHERE query IN ('SELECT active_backends()', 'SELECT count(*) FROM pg_stat_activity')
 ORDER BY toplevel DESC;

CREATE EXTENSION
SET
CREATE FUNCTION
 active_backends 
-----------------
               6
(1 row)

 toplevel | calls |                 query                 
----------+-------+---------------------------------------
 t        |     1 | SELECT active_backends()
 f        |     1 | SELECT count(*) FROM pg_stat_activity
(2 rows)

One client call, two rows, one true and one false. The function body was never sent by anyone, and on a server left at the default setting the second row would not exist. track is settable per session by a superuser, as above, and per database or per role, so turning it on for an investigation without paying for it everywhere is possible.

The cost of all is real but modest: more entries competing for a fixed-size store, which means eviction, which means the least-used fingerprints disappear sooner.

Reading the view once it means something

Separating a slow call from the slow statement inside it is the first step of most plan investigations, and query plan regression carries the rest, including how to compare a statement’s behaviour across two points in time. If the rows you are summing were reset part way through the window, the totals are wrong for a different reason, which a statistics reset explains.