PostgreSQL in Depth Module 13 – Monitoring, tuning and connection pooling
Finding slow queries: pg_stat_statements
How pg_stat_statements keeps one row per query shape, which of its counters name the cause, and how auto_explain logs the plan of a slow run.
What you will learn
- Install pg_stat_statements and read why one row stands for every run of a query shape
- Rank statements by total time, by mean time and by calls, and say what each ranking answers
- Diagnose chatty code, missing indexes and spills from rows, blocks and temporary blocks
- Log the plans of slow statements with auto_explain at a cost a busy server can carry
- Measure one change with a repeatable workload and the counters cleared before each run
Before you start
What you’ll learn · Before you start
What you will learn
- Install pg_stat_statements and read why one row stands for every run of a query shape
- Rank statements by total time, by mean time and by calls, and say what each ranking answers
- Diagnose chatty code, missing indexes and spills from rows, blocks and temporary blocks
- Log the plans of slow statements with auto_explain at a cost a busy server can carry
- Measure one change with a repeatable workload and the counters cleared before each run
Before you start
On this page
A page that answered in 40 milliseconds now takes two seconds, the server looks busy, and no single statement stands out anywhere. pg_stat_statements answers what the log cannot: it keeps one row per shape of statement, with the calls, the time, the rows and the blocks of every run of that shape added up. Three lookups that differ only in their customer number land on one row, so a flood of one-off statements becomes a list worth ranking.
auto_explain covers the other half. It writes the plan of a statement into the server log once that run passes a duration you choose, so the shape that ranks first can be read as the planner built it. Below you load both on PostgreSQL 18.6, watch five statements become two rows, read three rankings off one run, and look at a logged plan.
One row for every shape of query, not for every run
Both modules need shared memory, so they go into shared_preload_libraries and the server is restarted; the manual
is blunt about it, a restart “is needed to add or remove the module”. The view is an
extension: A package of SQL objects, such as functions, types, operators and often a C library, that CREATE EXTENSION installs into one database at a chosen version. Each object is recorded as a member, so the whole set is updated or dropped together. More in “Extensions and foreign data wrappers”, created per database with CREATE EXTENSION pg_stat_statements, and it needs
query identifiers, which compute_query_id computes at its default of auto. pg_stat_statements counts for the
whole server; the extension only gives you the view.
What makes the view readable is normalised query: The text pg_stat_statements keeps for an entry, in which constants that did not affect the match appear as parameter symbols such as $1, and a list of constants is squashed to one element.. The identifier is a hash of the
query tree after parse analysis, so constants that do not change the meaning are ignored, and the text kept for the
row shows them as $1. Follow one statement through that:
The drawing shows the whole scene; its 6 steps are listed below.
Every step as text
- The statement arrives. A client sends one SELECT with the number 7 in it. The server parses it and works out what it means, which gives a query tree with the table, the columns and the constant in their places.
- The tree becomes a number. With compute_query_id at its default the server hashes that tree into one 64-bit number, the query identifier. Two statements whose trees differ only in their constants hash to the same number.
- Constants become parameters. Because the constant was ignored while matching, the text that is kept shows it as a parameter symbol. The row therefore reads route = $1, and the rest of the text is that of the first statement with this identifier.
- The counters go up. The server finds the row for this identifier, or makes it, and adds this run to it: one more call, the time it took, the rows it returned and the blocks it read or found in memory.
- The next of that shape. A second client sends the same SELECT with 42 instead of 7. Parse analysis gives the same tree shape, so the same identifier, so the same row: calls 2, and the two times are added together.
- One row, every run. A third constant makes it calls 3. The view now answers questions about the shape rather than about one run of it, which is what makes its numbers worth ranking.
The run below loads the module, creates 200,000 deliveries, clears the counters and sends five SELECTs.
normalise_session.py
"""Loading pg_stat_statements on a throwaway PostgreSQL 18.6 cluster and watching five queries become two rows.
The cluster is started with pg_stat_statements and auto_explain in shared_preload_libraries, because both need shared
memory and are loaded when the postmaster starts. normalise.sql then creates the view with CREATE EXTENSION, loads a
deliveries table, resets the counters and runs five SELECTs that differ only in their constants.
Nothing in the output changes between runs: no time, duration or identifier is printed.
"""
from pg_session import Server
SETTINGS = {
# pg_stat_statements and auto_explain need shared memory, so the postmaster loads them at start.
"shared_preload_libraries": "'pg_stat_statements,auto_explain'",
# The settings this track's examples share, so that two runs print the same bytes.
"autovacuum": "off",
"max_parallel_workers_per_gather": "0",
"jit": "off",
"timezone": "'UTC'",
"log_line_prefix": "''",
"default_statistics_target": "1000",
}
with Server(settings=SETTINGS) as pg:
pg.up()
pg.psql("normalise.sql", note="loading the view, then five queries that differ only in their constants")Output
-- loading the view, then five queries that differ only in their constants
$ psql -X -a -f normalise.sql
-- Loading pg_stat_statements, then five queries that differ only in their constants.
SHOW shared_preload_libraries;
shared_preload_libraries
---------------------------------
pg_stat_statements,auto_explain
(1 row)
SHOW compute_query_id;
compute_query_id
------------------
auto
(1 row)
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
CREATE TABLE deliveries (id bigint PRIMARY KEY, route int, parcels int);
CREATE TABLE
INSERT INTO deliveries SELECT i, i % 500, 1 + i % 9 FROM generate_series(1, 200000) AS i;
INSERT 0 200000
ANALYZE deliveries;
ANALYZE
SELECT pg_stat_statements_reset() IS NOT NULL AS counters_cleared;
counters_cleared
------------------
t
(1 row)
SELECT count(*) FROM deliveries WHERE route = 7;
count
-------
400
(1 row)
SELECT count(*) FROM deliveries WHERE route = 42;
count
-------
400
(1 row)
SELECT count(*) FROM deliveries WHERE route = 311;
count
-------
400
(1 row)
SELECT count(*) FROM deliveries WHERE route IN (1, 2, 3);
count
-------
1200
(1 row)
SELECT count(*) FROM deliveries WHERE route IN (1, 2, 3, 4, 5);
count
-------
2000
(1 row)
SELECT calls, rows, query
FROM pg_stat_statements
WHERE query LIKE 'SELECT count(*) FROM deliveries%'
ORDER BY query;
calls | rows | query
-------+------+----------------------------------------------------------------
3 | 3 | SELECT count(*) FROM deliveries WHERE route = $1
2 | 2 | SELECT count(*) FROM deliveries WHERE route IN ($1 /*, ... */)
(2 rows)
SELECT count(*) AS entries, count(DISTINCT queryid) AS query_ids
FROM pg_stat_statements
WHERE query LIKE 'SELECT count(*) FROM deliveries WHERE route = %';
entries | query_ids
---------+-----------
1 | 1
(1 row)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 normalise_session.py
normalise.sql
-- Loading pg_stat_statements, then five queries that differ only in their constants.
SHOW shared_preload_libraries;
SHOW compute_query_id;
CREATE EXTENSION pg_stat_statements;
CREATE TABLE deliveries (id bigint PRIMARY KEY, route int, parcels int);
INSERT INTO deliveries SELECT i, i % 500, 1 + i % 9 FROM generate_series(1, 200000) AS i;
ANALYZE deliveries;
SELECT pg_stat_statements_reset() IS NOT NULL AS counters_cleared;
SELECT count(*) FROM deliveries WHERE route = 7;
SELECT count(*) FROM deliveries WHERE route = 42;
SELECT count(*) FROM deliveries WHERE route = 311;
SELECT count(*) FROM deliveries WHERE route IN (1, 2, 3);
SELECT count(*) FROM deliveries WHERE route IN (1, 2, 3, 4, 5);
SELECT calls, rows, query
FROM pg_stat_statements
WHERE query LIKE 'SELECT count(*) FROM deliveries%'
ORDER BY query;
SELECT count(*) AS entries, count(DISTINCT queryid) AS query_ids
FROM pg_stat_statements
WHERE query LIKE 'SELECT count(*) FROM deliveries WHERE route = %';Five statements left two rows. The three route counts share the row route = $1, with calls 3 and one query
identifier, and the two list queries share route IN ($1 /*, ... */): a list of constants is squashed to one element
with that comment as the sign of it. A long normalised text reads better reformatted, which the
SQL formatter does without a server.
Key idea
One row per query shape, not per run, which is why a flood of cheap statements can outrank one slow report.
Checkpoint
Check: One row per query shape
Ranking by total time, by mean time and by calls
Three rankings answer three questions, and one view gives all of them. Total execution time asks which shape costs the server most work; mean execution time asks which shape a user waits longest for; calls asks which shape the application sends most often. The run puts three workloads on one cluster: 300 lookups and 30 whole-table sums through pgbench, then one large sort.
ranking_session.py
"""Three workloads on one cluster, then the three rankings pg_stat_statements answers: total time, mean time, calls.
pgbench runs the two repeated workloads with a fixed number of transactions (-t), one client and a fixed random seed,
so the call counts repeat exactly; psql runs the single large sort once. ranking.sql then reads the view and prints
each entry's calls, rows, blocks and its RANK by total time, by mean time and by calls, never a time: the three
workloads are built to differ by a factor of ten or more, so the ranks are the same on every run, while the
milliseconds of a loaded machine are not. pgbench's own timings are replaced with fixed text for the same reason.
"""
from pg_session import Server
SETTINGS = {
"shared_preload_libraries": "'pg_stat_statements,auto_explain'",
"autovacuum": "off",
"max_parallel_workers_per_gather": "0",
"jit": "off",
"timezone": "'UTC'",
"log_line_prefix": "''",
"default_statistics_target": "1000",
}
# pgbench measures the run, and a measurement is never the same twice: its numbers are shown as fixed text.
PGBENCH = [
(r"latency average = [0-9.]+ ms", "latency average = <ms> ms"),
(r"initial connection time = [0-9.]+ ms", "initial connection time = <ms> ms"),
(r"tps = [0-9.]+ \(without initial connection time\)", "tps = <tps> (without initial connection time)"),
]
with Server(settings=SETTINGS) as pg:
pg.up()
pg.psql("ranking_setup.sql", note="the table and the counters, cleared")
pg.shell("pgbench", "-n", "-t", "300", "-c", "1", "--random-seed=7", "-f", "lookup.sql", "postgres",
mask=PGBENCH, note="300 single-row lookups by primary key")
pg.shell("pgbench", "-n", "-t", "30", "-c", "1", "--random-seed=7", "-f", "parcels_total.sql", "postgres",
mask=PGBENCH, note="30 whole-table sums")
pg.psql("ranking.sql", note="one large sort, then the three rankings")Output
-- the table and the counters, cleared
$ psql -X -a -f ranking_setup.sql
-- One table of 200,000 deliveries, then the counters cleared so that only the workloads below are counted.
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
CREATE TABLE deliveries (id bigint PRIMARY KEY, route int, customer int, parcels int);
CREATE TABLE
INSERT INTO deliveries SELECT i, i % 500, i % 50000, 1 + i % 9 FROM generate_series(1, 200000) AS i;
INSERT 0 200000
ANALYZE deliveries;
ANALYZE
SELECT pg_stat_statements_reset() IS NOT NULL AS counters_cleared;
counters_cleared
------------------
t
(1 row)
-- 300 single-row lookups by primary key
$ pgbench -n -t 300 -c 1 --random-seed=7 -f lookup.sql postgres
pgbench: setting random seed to 7
pgbench (18.6)
transaction type: lookup.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 300
number of transactions actually processed: 300/300
number of failed transactions: 0 (0.000%)
latency average = <ms> ms
initial connection time = <ms> ms
tps = <tps> (without initial connection time)
-- 30 whole-table sums
$ pgbench -n -t 30 -c 1 --random-seed=7 -f parcels_total.sql postgres
pgbench: setting random seed to 7
pgbench (18.6)
transaction type: parcels_total.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 30
number of transactions actually processed: 30/30
number of failed transactions: 0 (0.000%)
latency average = <ms> ms
initial connection time = <ms> ms
tps = <tps> (without initial connection time)
-- one large sort, then the three rankings
$ psql -X -a -f ranking.sql
-- One large sort, once, and then the view read three ways.
SELECT count(*) FROM (SELECT id, route, parcels FROM deliveries ORDER BY parcels, route, id) AS sorted;
count
--------
200000
(1 row)
SELECT calls,
rows,
shared_blks_hit + shared_blks_read AS blocks,
rank() OVER (ORDER BY total_exec_time DESC) AS by_total_time,
rank() OVER (ORDER BY mean_exec_time DESC) AS by_mean_time,
rank() OVER (ORDER BY calls DESC) AS by_calls,
left(query, 44) AS query
FROM pg_stat_statements
WHERE query LIKE '%deliveries%'
ORDER BY by_total_time;
calls | rows | blocks | by_total_time | by_mean_time | by_calls | query
-------+------+--------+---------------+--------------+----------+----------------------------------------------
30 | 30 | 38220 | 1 | 2 | 2 | SELECT sum(parcels) FROM deliveries WHERE pa
1 | 1 | 1280 | 2 | 1 | 3 | SELECT count(*) FROM (SELECT id, route, parc
300 | 300 | 1203 | 3 | 3 | 1 | SELECT parcels FROM deliveries WHERE id = $1
(3 rows)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 ranking_session.py
ranking_setup.sql
-- One table of 200,000 deliveries, then the counters cleared so that only the workloads below are counted.
CREATE EXTENSION pg_stat_statements;
CREATE TABLE deliveries (id bigint PRIMARY KEY, route int, customer int, parcels int);
INSERT INTO deliveries SELECT i, i % 500, i % 50000, 1 + i % 9 FROM generate_series(1, 200000) AS i;
ANALYZE deliveries;
SELECT pg_stat_statements_reset() IS NOT NULL AS counters_cleared;lookup.sql
-- pgbench script: one row by primary key, the shape an application repeats all day.
\set id random(1, 200000)
SELECT parcels FROM deliveries WHERE id = :id;parcels_total.sql
-- pgbench script: a sum over every row, the shape a dashboard repeats every few seconds.
SELECT sum(parcels) FROM deliveries WHERE parcels > 2;ranking.sql
-- One large sort, once, and then the view read three ways.
SELECT count(*) FROM (SELECT id, route, parcels FROM deliveries ORDER BY parcels, route, id) AS sorted;
SELECT calls,
rows,
shared_blks_hit + shared_blks_read AS blocks,
rank() OVER (ORDER BY total_exec_time DESC) AS by_total_time,
rank() OVER (ORDER BY mean_exec_time DESC) AS by_mean_time,
rank() OVER (ORDER BY calls DESC) AS by_calls,
left(query, 44) AS query
FROM pg_stat_statements
WHERE query LIKE '%deliveries%'
ORDER BY by_total_time;Each ranking names a different statement. The dashboard sum ranks first by total time: 30 calls that touched 38,220 blocks between them. The sort ranks first by mean time, on 1 call and 1,280 blocks: nobody runs it often, but whoever does waits. The lookup ranks first by calls, 300 of them for 1,203 blocks, the shape to read when an application feels slow although every statement in it is quick. Blocks are printed instead of milliseconds on purpose: they repeat exactly on every run, while times on a shared machine do not.
Checkpoint
Check: The three rankings
Plans for the slow runs, logged as they happen
A ranking names a shape; it does not say which plan ran. auto_explain closes that gap: loaded with the server, it
writes the plan of any run that takes longer than auto_explain.log_min_duration into the server log, with the
statement’s own text above it. Its default is -1, which logs nothing, so the module does no work until somebody sets
a threshold; 0 logs every statement. This run sets 0 for one session and shows the entry that appeared.
autoexplain_session.py
"""auto_explain writing the plan of a statement into the server log, on a throwaway PostgreSQL 18.6 cluster.
auto_explain is loaded with the postmaster, and does nothing until a duration is set, so autoexplain.sql sets
auto_explain.log_min_duration to 0 for its own session: every statement after that has its plan logged. One query
then runs, and the session prints the log entry it produced. The cluster writes its log with an empty
log_line_prefix, so the entry carries no time or process number, and the duration auto_explain reports is replaced
with fixed text, because a measured time is never the same twice.
"""
import re
from pg_session import Server
SETTINGS = {
"shared_preload_libraries": "'pg_stat_statements,auto_explain'",
"autovacuum": "off",
"max_parallel_workers_per_gather": "0",
"jit": "off",
"timezone": "'UTC'",
"log_line_prefix": "''",
"default_statistics_target": "1000",
}
TIMES = [(re.compile(r"duration: [0-9.]+ ms").pattern, "duration: <ms> ms")]
with Server(settings=SETTINGS) as pg:
pg.up()
pg.psql("ranking_setup.sql", note="the same 200,000 deliveries, and the counters cleared")
pg.psql("autoexplain.sql", note="plans logged from this session on")
pg.run("grep", "-A", "7", "plan:", "data.log", mask=TIMES, note="the entry auto_explain wrote")Output
-- the same 200,000 deliveries, and the counters cleared $ psql -X -a -f ranking_setup.sql -- One table of 200,000 deliveries, then the counters cleared so that only the workloads below are counted. CREATE EXTENSION pg_stat_statements; CREATE EXTENSION CREATE TABLE deliveries (id bigint PRIMARY KEY, route int, customer int, parcels int); CREATE TABLE INSERT INTO deliveries SELECT i, i % 500, i % 50000, 1 + i % 9 FROM generate_series(1, 200000) AS i; INSERT 0 200000 ANALYZE deliveries; ANALYZE SELECT pg_stat_statements_reset() IS NOT NULL AS counters_cleared; counters_cleared ------------------ t (1 row) -- plans logged from this session on $ psql -X -a -f autoexplain.sql -- auto_explain does nothing until a duration is set; 0 means every statement of this session. SHOW auto_explain.log_min_duration; auto_explain.log_min_duration ------------------------------- -1 (1 row) SET auto_explain.log_min_duration = 0; SET SELECT sum(parcels) FROM deliveries WHERE route = 11; sum ------ 2004 (1 row) RESET auto_explain.log_min_duration; RESET -- the entry auto_explain wrote $ grep -A 7 plan: data.log LOG: duration: <ms> ms plan: Query Text: SELECT sum(parcels) FROM deliveries WHERE route = 11; Aggregate (cost=3775.00..3775.01 rows=1 width=8) -> Seq Scan on deliveries (cost=0.00..3774.00 rows=400 width=4) Filter: (route = 11)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 autoexplain_session.py
autoexplain.sql
-- auto_explain does nothing until a duration is set; 0 means every statement of this session.
SHOW auto_explain.log_min_duration;
SET auto_explain.log_min_duration = 0;
SELECT sum(parcels) FROM deliveries WHERE route = 11;
RESET auto_explain.log_min_duration;ranking_setup.sql
-- One table of 200,000 deliveries, then the counters cleared so that only the workloads below are counted.
CREATE EXTENSION pg_stat_statements;
CREATE TABLE deliveries (id bigint PRIMARY KEY, route int, customer int, parcels int);
INSERT INTO deliveries SELECT i, i % 500, i % 50000, 1 + i % 9 FROM generate_series(1, 200000) AS i;
ANALYZE deliveries;
SELECT pg_stat_statements_reset() IS NOT NULL AS counters_cleared;The entry holds the query text with its real constant, 11, and the plan as the planner built it: an aggregate over a sequential scan that expected 400 rows. That is enough to recognise a missing index. The three parts below measure the rest: which counters point at chatty code, a missing index or a spill; which auto_explain settings a busy server can afford, and how an entry is matched to its row; and the loop that proves a change helped. Grouping entries by shape is what the log pattern analyzer is for.
Checkpoint
Check: What a logged plan holds
Practise: reading a snapshot of the view
Work these out on the readings above, with a pencil rather than a server.
Practice that adapts to you
Practise this lesson
9 questions about 10 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
Reading the counters that point at the cause Pro: opens with a Pro, Premium or Ultimate pass
A ranking tells you where to look, and the rest of the row tells you what is wrong. Rows against calls, blocks against calls, temporary blocks and the WAL columns each point at a different cause, and PostgreSQL 18 added four things worth knowing about.
In this part:
- Rows per call and blocks per call name the shape of the waste
- Spills, writes and what the WAL columns add
- What PostgreSQL 18 added, and when entries get dropped
3 runnable examples (counters.sql, counters_session.py, limits_count.sql, limits_generate.sql, limits_session.py, limits_setup.sql, one_row.sql, pg18.sql, pg18_session.py) · about 9 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
Logging slow plans on a busy server, and matching them back Pro: opens with a Pro, Premium or Ultimate pass
auto_explain can tell you almost everything EXPLAIN ANALYZE would, and charge you for it on every statement the server runs. The settings below choose how much evidence each entry carries, and the last one links that entry back to the row that made you look.
In this part:
- Actual rows without the cost of reading the clock
- Sampling, nested statements and the link back to the view
1 runnable example (busy.sql, busy_link.sql, busy_session.py) · about 8 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
The loop that proves a change helped, with numbers Pro: opens with a Pro, Premium or Ultimate pass
Every change made on a busy server is a claim, and the view can settle it. The loop is always the same: clear the counters, run a workload you can repeat, read the row, change one thing, and do it again.
In this part:
- Clearing the counters and running a workload you can repeat
- The same workload after one index, compared by blocks
- What to keep from these three parts
1 diagram · 1 runnable example (by_customer.sql, loop_after.sql, loop_before.sql, loop_session.py) · about 7 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
Interview questions
Three situations that start from this view: a report nobody can find, a server where plan logging became the problem, and a change somebody must defend. Decide your own opening move, then read the first step on its page.
-
Mid to senior
A checkout page takes four seconds. One entry shows 48,000 calls, 48,300 rows and nine blocks a call; another shows 12 calls, a mean of two seconds and the whole table read on every call. Which do you work on first, and how do you decide?
-
Senior to staff
A team switched plan logging on with row counts to catch a slow report. Every statement on a server that runs 3,000 a second became slower, including statements that were never logged. Explain the cause and give the settings you would run instead, with what each costs.
-
Mid to senior
A colleague says a new index made a report twice as quick, and offers a reading of the statistics view taken an hour after the index was created. What is wrong with that evidence, and how would you produce evidence that holds on a server other people are using?
Flashcards · 4 cards
Recall it, then check
Say the answer to yourself before you look: recalling it is what makes it stick.
-
What is pg_stat_statements, and what does it need?
An extension created per database that exposes a view, while the counting itself is server-wide. It needs compute_query_id (auto by default), and adding or removing the module needs a restart because it loads from shared_preload_libraries.
-
How does pg_stat_statements group statements?
By normalisation: one row per query shape, identified by a hash of the query tree after parse analysis, with constants shown as $1 and a list of constants squashed to one element. So a flood of cheap statements can outrank one slow report.
-
What three questions do the rankings of pg_stat_statements answer?
Total execution time (which shape costs the server most work), mean execution time (which shape a user waits longest for), and calls (which shape the application sends most often). Blocks are shown instead of milliseconds because they repeat exactly between runs.
-
What does auto_explain do?
Loaded with the server, it writes the plan of any run slower than auto_explain.log_min_duration into the server log, with the statement text above it. The default -1 logs nothing; 0 logs every statement.
Key takeaways
- pg_stat_statements loads with a server restart and keeps one row per shape of statement, with constants as
$1. - Total time ranks the load, mean time one user’s wait, calls the chatty code: the run put a different statement first in each ranking.
- Block counts repeat exactly from run to run while times on a shared machine do not, so a ranking read in blocks can be compared between runs.
- auto_explain writes the plan of any run slower than
auto_explain.log_min_durationinto the log, under the statement’s own text; at its default of -1 it logs nothing.
Terms in this lesson
- N+1 query
- The pattern where an application fetches a list and then sends one more statement for each row of it, so one page turns into hundreds of small statements instead of one.
- normalised query
- The text pg_stat_statements keeps for an entry, in which constants that did not affect the match appear as parameter symbols such as $1, and a list of constants is squashed to one element.
- query identifier
- A 64-bit hash of a statement's query tree after parse analysis, computed when compute_query_id is on or auto. pg_stat_statements uses it as the identity of an entry, so statements that differ only in their constants share one row.
- shared_preload_libraries
- The server setting that names the libraries the postmaster loads when it starts. Modules that need shared memory, such as pg_stat_statements, are listed there, and adding or removing one needs a server restart.
References
- PostgreSQL 18 documentation: F.32 pg_stat_statements (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: F.3 auto_explain (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 19.9 Run-time Statistics (compute_query_id) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 19.8 Error Reporting and Logging (log_line_prefix) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 19.4 Resource Consumption (work_mem) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: pgbench (The PostgreSQL Global Development Group)
- PostgreSQL 18 release notes (The PostgreSQL Global Development Group)
- PostgreSQL 19 release notes (in preparation) (The PostgreSQL Global Development Group)
- PostgreSQL 19 documentation (in preparation): F.32 pg_stat_statements (The PostgreSQL Global Development Group)
Related tools
Report a problem with this lesson
Kept only in this browser. Your Learn progress