ObservabilitybeginnerFree in full

Find your slowest queries with pg_stat_statements

pg_stat_statements aggregates normalized query shapes. In the PG16.14 lab, 25 status scans used 89.817 ms total while 25 primary-key lookups used 0.234 ms, after a proven full restart loaded the extension.

Problem

What you're actually looking at

The symptom as it shows up on a real server.

You cannot fix what you cannot see. Without aggregated statistics you are guessing which queries hurt. pg_stat_statements records calls, total and mean execution time, and rows for each normalized query, so the real cost centers are obvious.

Meridian wants to know where the database actually spends its time. Ranking by total execution time immediately separates the expensive status scan from the cheap primary-key lookup.

Simple terms

A query that takes 5 ms sounds harmless, until it runs a million times. The pg_stat_statements extension groups every query by its shape (ignoring the specific values you passed in) and adds up the total time each shape has spent. That total is the number that matters: a "cheap" query fired constantly can cost the database more than a slow one that runs rarely. Ranking by total time shows you the real hot spots instead of guessing from a single slow example.

Before you start

• shared_preload_libraries includes pg_stat_statements and PostgreSQL has been fully restarted (a reload is insufficient).
• CREATE EXTENSION pg_stat_statements has run in the target database.
• pg_read_all_stats (or superuser) if you need query text/queryid for other users.
• Run this in psql: the Lab setup uses \gexec so both query shapes are recorded as top-level statements.

How to identify it

›The extension pg_stat_statements is loaded (shared_preload_libraries) and the view is queryable.
›You need to rank queries by total time spent, not just the worst single call.
›Similar queries with different literals should be grouped as one normalized shape.
›You want calls, mean time, and rows alongside total time.

Pitfalls to avoid

✕Do not rank by mean time alone, a fast query run millions of times can outweigh a slow one run twice; total time tells the truth.
✕Do not forget the stats are cumulative; use pg_stat_statements_reset() to measure a specific window.
✕Do not ignore that literals are normalized to $1, that is what lets you group a query shape.
✕Do not draw conclusions from a tiny sample right after a reset.
✕Do not use pg_reload_conf() after changing shared_preload_libraries, its context is postmaster, so activation requires a full PostgreSQL restart.
✕Do not assume queryid is stable across PostgreSQL major versions; use it within one server/version, not as a permanent cross-version identifier.

Trace it

Run these against the affected server. To build a copy of this scenario instead, see “Reproduce it in a lab” below.

Rank normalized queries by total time

The captured PG16.14 run ranked the 25-call status scan at 89.817 ms total (3.593 ms mean), versus 0.234 ms total (0.009 ms mean) for the indexed lookup. The exact timings are host-specific; the ranking is the finding.

SQL

SELECT queryid,
       calls,
       round(total_plan_time::numeric, 3) AS total_plan_ms,
       round(total_exec_time::numeric, 3) AS total_exec_ms,
       round(mean_exec_time::numeric, 3) AS mean_exec_ms,
       rows,
       shared_blks_hit,
       shared_blks_read,
       temp_blks_written,
       wal_bytes,
       left(query, 90) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND query LIKE '%parcel_events%'
  AND query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 5;

Captured in Docker · PostgreSQL 16.14

       queryid        | calls | total_plan_ms | total_exec_ms | mean_exec_ms | shared_blks_hit | shared_blks_read | temp_blks_written | wal_bytes |                         query
----------------------+-------+---------------+---------------+--------------+-----------------+------------------+-------------------+-----------+-------------------------------------------------------
 -5099675763383157722 |    25 |         1.927 |        89.817 |        3.593 |            7024 |                1 |                 0 |      7292 | SELECT count(*) FROM parcel_events WHERE status = $1
  5057429002865461204 |    25 |         1.167 |         0.234 |        0.009 |              86 |                0 |                 0 |         0 | SELECT waybill FROM parcel_events WHERE event_id = $1
(2 rows)

Resolution approach

1.Enable pg_stat_statements and query it ordered by total_exec_time DESC.
2.Investigate the top shapes with EXPLAIN (ANALYZE, BUFFERS).
3.Reset the stats to measure the effect of a change over a clean window.
4.Track the top-N over time so regressions surface early.

Stop it recurring

01
Focus effort where the time goes
With the ranking in hand you tune the 89.817 ms status scan first, the 0.234 ms primary-key lookup is not worth touching. Re-run the same ranking after each change; never reuse these Docker timings as a production target.

Reproduce it in a lab

Builds the scenario above on a throwaway database so you can practise the fix. Skip this if you are working a live incident.

Lab setup (run this first)

Create the extension and generate two top-level query shapes. The server must already preload pg_stat_statements and be restarted.

SQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
DROP TABLE IF EXISTS parcel_events;
CREATE TABLE parcel_events (
  event_id int PRIMARY KEY,
  status text NOT NULL,
  waybill text NOT NULL
);
INSERT INTO parcel_events
SELECT g, CASE WHEN g % 5 = 0 THEN 'exception' ELSE 'ok' END, 'W-' || g
FROM generate_series(1, 50000) g;
SELECT pg_stat_statements_reset();
SELECT 'SELECT count(*) FROM parcel_events WHERE status = ''exception'';'
FROM generate_series(1, 25) gexec
SELECT 'SELECT waybill FROM parcel_events WHERE event_id = 42;'
FROM generate_series(1, 25) gexec

Verify you're done

SELECT queryid, calls,
       round(total_plan_time::numeric, 3) AS total_plan_ms,
       round(total_exec_time::numeric, 3) AS total_exec_ms,
       temp_blks_written, wal_bytes, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Last verified 2026-08-16 · PostgreSQL 16.14 (docker/labs/ha-perf/perf)

Related errors57014

Related errors

SQLSTATEs this runbook resolves

The error pages that send an on-call engineer here.

57014Query canceled

Other Observability runbooks

Neighbouring incidents that share the same diagnostic surface.

All Observability runbooks →

Connected

How this connects to the rest of the library

A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.

Open in the interactive map →