pg_stat_statements groups statement executions and accumulates their costs. It can identify a frequently executed short query whose total time is substantial, as well as an individually slow query. These are different questions. Neither ranking alone proves which change will improve application latency, and execution time is not the same as CPU consumption, storage latency, or end-to-end request duration.

Grouping is useful and bounded

PostgreSQL 18 normally replaces literal constants in representative query text with parameter symbols and groups statements with equivalent analyzed structures. The entry key includes user, database, query identifier, and whether execution is top-level. Text alone is not that identity. Different object resolution can separate identical text, and rare hash collisions can combine unrelated structures within the relevant grouping scope.

The representative text is diagnostic data, not an audit trail of every submitted statement. Some utility statements or normalization failures can retain literal values, so restrict access and avoid embedding secrets. Query identifiers are not promised stable across major versions or arbitrary catalog changes. PostgreSQL 18 also normalizes certain constant lists of differing lengths; do not assume identical behavior in another version without checking its manual.

Rank according to the question

Total execution time helps prioritize aggregate database work. Mean, maximum, and dispersion help describe individual calls, but they are not percentiles and cannot identify a cause by themselves. High variance could involve parameters, cache state, contention, or plan changes. Inspect the relevant plan and waits before declaring a bad plan or lock problem from one statistic.

Accumulated execution durations can exceed elapsed wall-clock time because concurrent sessions overlap. That is not proof of a restart or invalid measurement. Planning time is tracked separately when planning statistics are enabled. Shared-block reads mean blocks requested through PostgreSQL’s buffer manager; they can be satisfied by the operating-system cache. Multiplying by the server’s block size gives a requested-byte quantity, not physical-device traffic. I/O timing adds time measurements when enabled; the basic block counts do not depend on it.

When it breaks

The module must be loaded through shared_preload_libraries at server startup and its SQL extension created in the database used for inspection. Query identifier calculation must be available, normally through compute_query_id=auto or on; an external module can also supply identifiers. Missing configuration, restricted visibility, and disabled tracking are different failures. Read their actual errors rather than assuming every absent row means no workload occurred.

A useful delta requires comparable entry identities and collection windows. PostgreSQL 18 exposes per-entry stats_since, a separate minimum/maximum start time, and aggregate reset and deallocation information. A targeted reset or eviction can invalidate one entry without resetting the whole view. Nonnegative subtraction is not sufficient proof of continuity: enough work after a reset can hide the discontinuity. Treat new or changed windows as new baselines instead of subtracting across them.

Literal predicates merge into a parameterized query, followed by four identity fields, separate total and mean formulas, comparable-window and reset paths, and read-count and fixture-identity cautions.
Normalization is bounded by entry identity and collection continuity. The revised fixture binds all four identity fields; execution remains held, with no fresh SQL or timing result.

Check it quickly

Use a fresh, otherwise idle disposable PostgreSQL 18 cluster and database, with the module preloaded, built-in query identifiers enabled, top-level tracking, and the extension visible and installed or installable by the test role. Keep the role, search path and objects unchanged; allow no concurrent workload, resets or eviction. Run this SQL in one fresh session with errors treated as failures. Success removes its tables and view; errors can leave them, and a newly installed extension remains. No existing statistics are reset.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE TABLE pgss_demo (id integer);
INSERT INTO pgss_demo SELECT generate_series(1,1000);
CREATE TEMP TABLE pgss_key (
  userid oid NOT NULL, dbid oid NOT NULL,
  toplevel boolean NOT NULL, queryid bigint NOT NULL CHECK (queryid <> 0),
  PRIMARY KEY (userid,dbid,toplevel,queryid)
);
DO $$
DECLARE first_plan json; second_plan json; first_id bigint; second_id bigint;
BEGIN
  IF current_setting('compute_query_id') NOT IN ('auto','on')
     OR current_setting('pg_stat_statements.track') <> 'top' THEN
    RAISE EXCEPTION 'requires built-in query identifiers and top-level tracking';
  END IF;
  EXECUTE 'EXPLAIN (VERBOSE, FORMAT JSON) SELECT sum(id) FROM pgss_demo WHERE id < 100'
    INTO STRICT first_plan;
  EXECUTE 'EXPLAIN (VERBOSE, FORMAT JSON) SELECT sum(id) FROM pgss_demo WHERE id < 500'
    INTO STRICT second_plan;
  first_id := (first_plan->0->>'Query Identifier')::bigint;
  second_id := (second_plan->0->>'Query Identifier')::bigint;
  IF first_id IS NULL OR first_id = 0 OR second_id IS DISTINCT FROM first_id THEN
    RAISE EXCEPTION 'expected matching nonzero query identifiers';
  END IF;
  INSERT INTO pgss_key
  SELECT (SELECT oid FROM pg_roles WHERE rolname=current_user),
         (SELECT oid FROM pg_database WHERE datname=current_database()),
         true,first_id;
END $$;
CREATE TEMP VIEW pgss_target AS
SELECT s.* FROM pg_stat_statements AS s JOIN pgss_key AS k
USING (userid,dbid,toplevel,queryid);
CREATE TEMP TABLE pgss_window AS
SELECT dealloc,stats_reset FROM pg_stat_statements_info;
SELECT sum(id) FROM pgss_demo WHERE id < 100;
CREATE TEMP TABLE pgss_first AS
SELECT userid,dbid,toplevel,queryid,calls,stats_since FROM pgss_target;
SELECT sum(id) FROM pgss_demo WHERE id < 500;
SELECT userid,dbid,toplevel,queryid,query,calls,rows,
       round(total_exec_time::numeric,3) AS total_ms,
       round(mean_exec_time::numeric,3) AS mean_ms,stats_since
FROM pgss_target;
DO $$ BEGIN
  IF (SELECT count(*) FROM pgss_first) <> 1
     OR (SELECT count(*) FROM pgss_target) <> 1
     OR (SELECT count(*) FROM pgss_window) <> 1 THEN
    RAISE EXCEPTION 'expected one identity-bound entry and collection baseline';
  END IF;
  IF NOT EXISTS (
      SELECT 1 FROM pgss_first AS a JOIN pgss_target AS b
      USING (userid,dbid,toplevel,queryid)
      CROSS JOIN pgss_window AS w CROSS JOIN pg_stat_statements_info AS i
      WHERE a.calls=1 AND b.calls=2 AND b.calls-a.calls=1
        AND b.stats_since=a.stats_since
        AND i.dealloc=w.dealloc AND i.stats_reset=w.stats_reset) THEN
    RAISE EXCEPTION 'expected calls 1 then 2 in one unchanged collection window';
  END IF;
END $$;
SELECT userid,dbid,toplevel,queryid,calls,
       round(total_exec_time::numeric,3) AS total_ms,
       round(mean_exec_time::numeric,3) AS mean_ms
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
SELECT userid,dbid,toplevel,queryid,calls,
       round(mean_exec_time::numeric,3) AS mean_ms
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
SELECT dealloc,stats_reset FROM pg_stat_statements_info;
SELECT round((sum(shared_blks_read)::numeric *
              current_setting('block_size')::numeric)/1073741824,6) AS requested_read_gib
FROM pg_stat_statements;
DROP VIEW pgss_target;
DROP TABLE pgss_first,pgss_window,pgss_key;
DROP TABLE pgss_demo;
SELECT 'pgss fixture completed' AS completion;

The sums should be 4,950 and 124,750. Non-ANALYZE EXPLAIN obtains both query identifiers without executing the sums; they must match and be nonzero. The target view joins that identifier with the current user, database and top-level flag, never representative text. Snapshots require one call then two, with unchanged stats_since and global reset/deallocation metadata. These guards rely on the idle-cluster assumptions; they are not collision-proof attribution or concurrent monitoring. The general rankings may agree; their sort keys differ. Numeric casts permit two-argument rounding of double-precision timings.

Retained PostgreSQL 18.6 logs cover the predecessor text-filter SQL in single-user mode; they do not execute this revised identity-bound fixture. Pure source and synthetic checks add no PostgreSQL runtime evidence. Client transport, concurrent sessions, production overhead and monitoring permissions remain untested. Durations are observations, not stable benchmarks. Before acting on production rankings, collect comparable snapshots, scope the user and database, inspect the workload, and measure application outcomes after changes.

Sources