pg_stat_statements

track planning and execution statistics of all SQL statements executed

pg_stat_statements : track planning and execution statistics of all SQL statements executed

Overview

ID	Extension	Package	Version	Category	License	Language
6990	pg_stat_statements	pg_stat_statements	`1.11`	STAT	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--sLd--	No	Yes	Yes	Yes	no	no

Relationships
Need By	pg_stat_kcache powa
See Also	pg_stat_plans pg_profile pg_stat_monitor pg_store_plans auto_explain pg_show_plans powa explain_ui pg_qualstats online_advisor pg_relusage pg_wait_sampling

Packages

PG18	PG17	PG16	PG15	PG14
1.11	1.11	1.11	1.11	1.11

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_stat_statements';

Create this extension with:

CREATE EXTENSION pg_stat_statements;

Usage

pg_stat_statements: track cumulative query execution statistics

pg_stat_statements tracks planning and execution statistics of all SQL statements executed by a server.

Querying Statistics

-- Top queries by total execution time
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Top queries by shared buffer reads (I/O intensive)
SELECT query, calls, shared_blks_read, shared_blks_hit,
       shared_blk_read_time
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

-- Extension status (deallocations, last reset)
SELECT * FROM pg_stat_statements_info;

Key View Columns

Column	Type	Description
`queryid`	bigint	Hash identifying normalized queries
`query`	text	Representative query text
`calls`	bigint	Execution count
`total_exec_time`	double precision	Total execution time (ms)
`mean_exec_time`	double precision	Mean execution time (ms)
`rows`	bigint	Total rows retrieved/affected
`shared_blks_hit`	bigint	Shared buffer cache hits
`shared_blks_read`	bigint	Shared blocks read from disk
`shared_blk_read_time`	double precision	Time reading shared blocks (ms)
`wal_records`	bigint	WAL records generated
`wal_bytes`	numeric	Total WAL generated (bytes)
`plans`	bigint	Times planned
`total_plan_time`	double precision	Total planning time (ms)

Functions

-- Reset all statistics
SELECT pg_stat_statements_reset();

-- Reset for a specific query
SELECT pg_stat_statements_reset(0, 0, queryid)
FROM pg_stat_statements
WHERE query LIKE '%my_table%';

-- Reset only min/max values
SELECT pg_stat_statements_reset(0, 0, 0, true);

-- Query without text (less I/O)
SELECT * FROM pg_stat_statements(showtext := false);

Configuration

Parameter	Default	Description
`pg_stat_statements.max`	5000	Maximum tracked statements (server start only)
`pg_stat_statements.track`	`top`	`top`, `all` (nested), or `none`
`pg_stat_statements.track_utility`	`on`	Track utility commands
`pg_stat_statements.track_planning`	`off`	Track planning statistics
`pg_stat_statements.save`	`on`	Persist across server restarts

Requires shared_preload_libraries = 'pg_stat_statements' and compute_query_id = on.