pg_stat_statements: find expensive PostgreSQL queries

pg_stat_statements helps you find PostgreSQL query patterns that consume time across repeated executions. Start with total execution time, then read call count and average time together. A frequently executed fast query can contribute more cumulative time than an occasional slow query.

This guide enables the extension in a disposable PostgreSQL 18 database, ranks statements, and calculates changes over a measured interval without resetting shared counters. Start with the cumulative report; the later interval comparison is an advanced exercise that needs one direct database session.

Validation, November 9, 2026: nine correctness checks passed on a local PostgreSQL 18.6 server with extension version 1.12. The workload is synthetic. Docker containers, PgBouncer behavior, concurrent resets, Hivenet deployment, and production overhead or capacity were not tested. The local results do not validate the separate Compose or PgBouncer walkthroughs.

Enable collection and the database views

You need a PostgreSQL 18 test server with the extension files installed, administrator access for setup, and an existing disposable database. Connect directly with psql and use its normal autocommit mode. Run each block only after the previous one succeeds. Do not run the fixture against a production database.

For a fresh test server, add this fragment to postgresql.conf:

# For a fresh disposable server. Preserve other libraries on an existing server.
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on
pg_stat_statements.track = top
pg_stat_statements.track_planning = off

On an existing server, append the module to the existing preload list instead of replacing other entries. Loading it requires a server restart, not just a configuration reload. Managed services may expose this through provider-specific parameters and maintenance controls. Check extension availability and restart ownership when choosing PostgreSQL hosting.

The shared library settings explain the startup requirement. Here, compute_query_id is explicitly enabled, while planning-time tracking stays off. PostgreSQL also supports automatic query-ID activation; see the statistics settings before changing an existing configuration.

After restarting the test server, connect to the disposable database as its setup administrator and run:

CREATE EXTENSION pg_stat_statements;
SELECT extversion FROM pg_extension WHERE extname = 'pg_stat_statements';

CREATE EXTENSION installs the SQL objects in the current database. The preload configuration belongs to the server. Our lab verified that creating the extension objects alone did not make the statistics view usable before preloading. If the extension already exists, inspect its version and schema instead of dropping it to repeat this example. The queries below assume its views are accessible through your search path.

SELECT version(), current_database(), current_user;
SHOW shared_preload_libraries;
SHOW compute_query_id;
SHOW pg_stat_statements.track;
SHOW pg_stat_statements.track_planning;

Confirm the intended server, database, role and settings. In our lab, query IDs were on, statement tracking was top, and planning tracking was off. If you use the PostgreSQL Compose setup guide, apply preload configuration before the restart and verify it in the database afterward; the setup guide’s container-runtime limitations still apply.

Record the observation window

SELECT clock_timestamp() AS observed_at, stats_reset, dealloc
FROM pg_stat_statements_info;

Keep this output with the report. stats_reset records the last complete reset, while dealloc signals entry eviction. Individual statement rows also carry stats_since. Counters accumulated over different periods cannot support a clean before-and-after comparison. A restart alone is not a reliable new measurement boundary.

The PostgreSQL 18 pg_stat_statements reference defines these fields and the grouping keys: database, executing role, query ID, and top-level status. The examples use PostgreSQL 18 columns; check your major version and installed extension version before adapting them.

Find statements with high cumulative execution time

SELECT s.userid, s.queryid, s.calls,
       round(s.total_exec_time::numeric, 2) AS total_exec_ms,
       round(s.mean_exec_time::numeric, 2) AS mean_exec_ms,
       s.rows, s.shared_blks_hit, s.shared_blks_read,
       s.temp_blks_written, s.stats_since,
       left(s.query, 160) AS query
FROM pg_stat_statements AS s
WHERE s.dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND s.toplevel AND s.calls > 0
ORDER BY s.total_exec_time DESC
LIMIT 10;

This report limits results to the connected database and top-level statements with completed executions. It shows cumulative execution time in milliseconds. Review the observation window before deciding whether the first row deserves attention.

Illustrative query pattern	Calls	Mean per call	Total execution time
Frequent lookup	1,000	2 ms	2,000 ms
Occasional report	10	100 ms	1,000 ms

These are arithmetic examples, not measured timings. The lookup contributes more total time, while the report has worse average latency per execution. Which matters more depends on the user-facing problem. An average does not describe the slowest tail, and accumulated execution time is neither a CPU utilization reading nor end-to-end application latency.

Read the buffer and temporary-block counters as clues for follow-up. A shared-buffer read does not establish a physical disk read, because the operating system may cache the data. Temporary writes can justify investigating a spill, but they do not identify the cause by themselves. A zero planning-time value also says little when planning tracking is disabled.

Use a small workload to check query grouping

In the same disposable database, run this fixture once. Stop if diag_lab already exists; choose a fresh test database rather than overwriting unknown objects.

CREATE SCHEMA diag_lab;
CREATE TABLE diag_lab.sales (id integer PRIMARY KEY, amount integer NOT NULL);
INSERT INTO diag_lab.sales
SELECT n, n % 100 FROM generate_series(1, 10000) AS n;
ANALYZE diag_lab.sales;
SELECT amount FROM diag_lab.sales WHERE id = 101;
SELECT amount FROM diag_lab.sales WHERE id = 202;
SELECT amount FROM diag_lab.sales WHERE id = 303;
SELECT sum(amount) FROM diag_lab.sales;

Run the top-query report again and find the lookups against diag_lab.sales. In our test, the three different ID values produced one entry with calls = 3, rows = 3, and representative text ending in id = $1. The aggregate query remained separate.

The representative text describes a grouped query, not every original execution. Keep the full grouping key when collecting results; query text alone is a poor join key. Do not assume a query ID is a permanent identity across major upgrades or unrelated servers. Monitoring queries can appear in the statistics too.

Advanced: measure new work without clearing counters

Keep this direct psql session open. Create a temporary baseline table after the initial workload:

CREATE TEMP TABLE pgss_before AS
SELECT statement_timestamp() AS captured_at,
       s.dbid, s.userid, s.queryid, s.toplevel, s.stats_since,
       s.calls, s.total_exec_time, i.stats_reset, i.dealloc
FROM pg_stat_statements AS s
CROSS JOIN pg_stat_statements_info AS i
WHERE s.dbid = (SELECT oid FROM pg_database WHERE datname = current_database());

The temporary table belongs to this session. A new connection will not have it, and transaction pooling may move statements between backend sessions. Use a direct connection for this exercise; our PgBouncer guide covers those session assumptions.

Now execute three more lookups:

SELECT amount FROM diag_lab.sales WHERE id = 404;
SELECT amount FROM diag_lab.sales WHERE id = 505;
SELECT amount FROM diag_lab.sales WHERE id = 606;

Compare the current counters with the baseline:

SELECT s.userid, s.queryid,
       s.calls - b.calls AS interval_calls,
       round((s.total_exec_time - b.total_exec_time)::numeric, 3) AS interval_exec_ms,
       round(((s.total_exec_time - b.total_exec_time) /
              NULLIF(s.calls - b.calls, 0))::numeric, 3) AS interval_mean_ms,
       b.captured_at, statement_timestamp() AS observed_at,
       left(s.query, 160) AS query
FROM pg_stat_statements AS s
JOIN pgss_before AS b
  ON (s.dbid, s.userid, s.queryid, s.toplevel) =
     (b.dbid, b.userid, b.queryid, b.toplevel)
CROSS JOIN pg_stat_statements_info AS i
WHERE s.stats_since = b.stats_since
  AND i.stats_reset = b.stats_reset
  AND i.dealloc = b.dealloc
  AND s.calls > b.calls
  AND s.total_exec_time >= b.total_exec_time
ORDER BY interval_exec_ms DESC;

Our result reported three additional lookup executions, rather than the cumulative six. The interval mean is the change in total time divided by the change in calls. Subtracting two cumulative means would not calculate that value.

The query joins all four identity fields, checks that the row’s collection start is unchanged, rejects a full reset or changed eviction counter, and excludes decreasing counters. In the isolated tests, both a per-entry reset and a global reset invalidated the old baseline, including after enough new executions made the call count exceed its earlier value. The article procedure itself performs no reset.

This is a conservative comparison of entries present in both samples. New or evicted entries are not reconstructed; any change in dealloc rejects the entire interval. An empty result can mean an invalid baseline, not zero activity. Capture a new baseline after investigating the reason. The lab did not test concurrent resets, and the sample does not create an atomic server-wide snapshot. Use a monitoring system with defined reset and sampling behavior for continuous collection.

Keep monitoring access deliberate

Our permission check confirmed that an ordinary role could not inspect another role’s query IDs. A test role with pg_read_all_stats could inspect the other role’s query text and IDs. PostgreSQL’s predefined-role documentation describes the broader visibility this grants.

Ask the database administrator to approve the monitoring role and scope. Do not grant broad monitoring access to an application just to make this report convenient. Treat captured query text as potentially sensitive: grouping literals is not a general anonymization guarantee. The lab used synthetic values and a temporary role; it did not change access on an existing server.

Choose the next investigation

For high call counts, inspect the application’s repetition and request patterns. For high mean time, reproduce representative parameters on a safe test dataset and inspect the plan. EXPLAIN ANALYZE executes the statement, so plan inspection needs its own scope and safeguards. This guide stops at selecting a query to investigate.

For a worked example, read a PostgreSQL EXPLAIN ANALYZE plan and compare estimated rows, actual rows, loops, and buffers. The walkthrough uses a disposable dataset and checks that an index change preserves the query’s results.

If the problem is happening now, consult current activity and wait information as well. PostgreSQL’s pg_stat_activity documentation covers that view. Historical statement totals cannot by themselves identify the session currently blocking a request.

Common setup failures are distinct: a missing relation suggests the extension objects or schema path; a preload error suggests startup configuration; hidden query text suggests permissions. For no useful rows, check the connected database, tracking mode, executed workload, and time window before changing settings.

Clean up only the lab objects

DROP TABLE pgss_before;
DROP TABLE diag_lab.sales;
DROP SCHEMA diag_lab;

Run this in the session and disposable database used above. The named table and schema are the fixture you created. These commands do not drop the extension or reset shared statistics. We also stopped the separate local PostgreSQL test server after validation. No production database, Docker container, cloud deployment, or realistic performance benchmark was part of that test.