When your application runs expensive or data-heavy queries, postgres helps you find them with the pg_stat_statements extension. This extension comes with every postgres release as part of the contrib modules. It collects execution statistics for sql statements and provides two views that show detailed query performance data. It also includes five configuration settings that control its operation. Before you use it, check if the extension is available in your PostgreSQL installation by running the following query.
select * from pg_available_extensions where name = 'pg_stat_statements';
Result :
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+------------------------------------------------------------------------
pg_stat_statements | 1.12 | | track planning and execution statistics of all SQL statements executed
(1 row)
Now, create the extension.
create extension pg_stat_statements;
Now, you can see the installed version of this extension like this.
select * from pg_available_extensions where name = 'pg_stat_statements';
Result :
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+------------------------------------------------------------------------
pg_stat_statements | 1.12 | 1.12 | track planning and execution statistics of all SQL statements executed
(1 row)
Check all the functionalities provided by this extension.
\dx+ pg_stat_statements
Result :
Objects in extension "pg_stat_statements"
Object description
-----------------------------------------------------------
function pg_stat_statements(boolean)
function pg_stat_statements_info()
function pg_stat_statements_reset(oid,oid,bigint,boolean)
type pg_stat_statements
type pg_stat_statements[]
type pg_stat_statements_info
type pg_stat_statements_info[]
view pg_stat_statements
view pg_stat_statements_info
(9 rows)
In postgres configuration, there is a parameter named shared_preload_libraries.
You can see the value of this parameter like this.
show shared_preload_libraries ;
Result :
shared_preload_libraries
--------------------------
(1 row)
Check the metadata from pg_settings.
select * from pg_settings where name = 'shared_preload_libraries';
Result :
-[ RECORD 1 ]---+-------------------------------------------------------
name | shared_preload_libraries
setting | pg_stat_statements
unit |
category | Client Connection Defaults / Shared Library Preloading
short_desc | Lists shared libraries to preload into server.
extra_desc |
context | postmaster
vartype | string
source | configuration file
min_val |
max_val |
enumvals |
boot_val |
reset_val | pg_stat_statements
sourcefile | /etc/postgresql/18/main/postgresql.conf
sourceline | 813
pending_restart | f
The purpose of shared_preload_libraries is to tell postgres to load specific shared libraries when the server starts. This allows extensions that need early initialization, like allocating shared memory, registering internal hooks, or starting background workers, to work properly. Since these actions happen only during startup, changing this parameter requires a postgres restart.
Now, change the value of the shared_preload_libraries like this.
show config_file ;
Result :
config_file
-----------------------------------------
/etc/postgresql/18/main/postgresql.conf
(1 row)
Open the conf file and set the value like this.
sudo nano /etc/postgresql/18/main/postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
Now, restart the postgresql.
sudo systemctl restart postgresql
Check that the changed value is reflected or not.
show shared_preload_libraries ;
Result:
shared_preload_libraries
--------------------------
pg_stat_statements
(1 row)
Parameters related to this extension.
pg_stat_statements.max pg_stat_statements.save pg_stat_statements.track pg_stat_statements.track_planning pg_stat_statements.track_utility
Check the default value.
show pg_stat_statements.max ;
Result :
pg_stat_statements.max
------------------------
5000
(1 row)
Check the metadata from pg_settings.
select * from pg_settings where name = 'pg_stat_statements.max';
Result :
-[ RECORD 1 ]---+---------------------------------------------------------------------
name | pg_stat_statements.max
setting | 5000
unit |
category | Customized Options
short_desc | Sets the maximum number of statements tracked by pg_stat_statements.
extra_desc |
context | postmaster
vartype | integer
source | default
min_val | 100
max_val | 1073741823
enumvals |
boot_val | 5000
reset_val | 5000
sourcefile |
sourceline |
pending_restart | f
Check the default value.
show pg_stat_statements.save;
Result :
-[ RECORD 1 ]-----------+---
pg_stat_statements.save | on
Check the metadata from pg_settings.
select * from pg_settings where name = 'pg_stat_statements.save';
Result :
-[ RECORD 1 ]---+------------------------------------------------------------
name | pg_stat_statements.save
setting | on
unit |
category | Customized Options
short_desc | Save pg_stat_statements statistics across server shutdowns.
extra_desc |
context | sighup
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f
Check the default value.
show pg_stat_statements.track;
Result :
-[ RECORD 1 ]------------+----
pg_stat_statements.track | top
Check the metadata from pg_settings.
select * from pg_settings where name = 'pg_stat_statements.track';
Result :
-[ RECORD 1 ]---+------------------------------------------------------------
name | pg_stat_statements.track
setting | top
unit |
category | Customized Options
short_desc | Selects which statements are tracked by pg_stat_statements.
extra_desc |
context | superuser
vartype | enum
source | default
min_val |
max_val |
enumvals | {none,top,all}
boot_val | top
reset_val | top
sourcefile |
sourceline |
pending_restart | f
Check the default value.
show pg_stat_statements.track_planning;
Result :
-[ RECORD 1 ]---------------------+----
pg_stat_statements.track_planning | off
Check the metadata from pg_settings.
select * from pg_settings where name = 'pg_stat_statements.track_planning';
Result :
-[ RECORD 1 ]---+--------------------------------------------------------------------
name | pg_stat_statements.track_planning
setting | off
unit |
category | Customized Options
short_desc | Selects whether planning duration is tracked by pg_stat_statements.
extra_desc |
context | superuser
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
Check the default value.
show pg_stat_statements.track_utility;
Result :
-[ RECORD 1 ]--------------------+---
pg_stat_statements.track_utility | on
Check the metadata from pg_settings.
select * from pg_settings where name = 'pg_stat_statements.track_utility';
Result :
-[ RECORD 1 ]---+--------------------------------------------------------------------
name | pg_stat_statements.track_utility
setting | on
unit |
category | Customized Options
short_desc | Selects whether utility commands are tracked by pg_stat_statements.
extra_desc |
context | superuser
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f
There are nearly two views provided by the pg_stat_statements extension.
- view pg_stat_statements
- view pg_stat_statements_info
Let’s start with the first view.
select * from pg_stat_statements limit 1;
Result :
-[ RECORD 1 ]--------------+------------------------------------------------------
userid | 41515
dbid | 102071
toplevel | t
queryid | 1916052192376627818
query | COMMENT ON COLUMN "crm_team"."color" IS 'Color Index'
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
calls | 1
total_exec_time | 0.006285
min_exec_time | 0.006285
max_exec_time | 0.006285
mean_exec_time | 0.006285
stddev_exec_time | 0
rows | 0
shared_blks_hit | 8
shared_blks_read | 0
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
shared_blk_read_time | 0
shared_blk_write_time | 0
local_blk_read_time | 0
local_blk_write_time | 0
temp_blk_read_time | 0
temp_blk_write_time | 0
wal_records | 2
wal_fpi | 0
wal_bytes | 151
wal_buffers_full | 0
jit_functions | 0
jit_generation_time | 0
jit_inlining_count | 0
jit_inlining_time | 0
jit_optimization_count | 0
jit_optimization_time | 0
jit_emission_count | 0
jit_emission_time | 0
jit_deform_count | 0
jit_deform_time | 0
parallel_workers_to_launch | 0
parallel_workers_launched | 0
stats_since | 2026-07-22 18:44:47.140327+05:30
minmax_stats_since | 2026-07-22 18:44:47.140327+05:30
Purpose :
From this view, we can see each tracked query's metadata, like its ID, total execution time, total planning time, number of calls, blocks read from cache, blocks read from disk, and also the parallel workers based on this query execution, and also the JIT-related metadata.
select * from pg_stat_statements_info limit 1;
Result :
-[ RECORD 1 ]---------------------------------
dealloc | 17
stats_reset | 2026-07-22 18:40:44.995031+05:30
This view is mainly used to show two types of data. One is the total number of deallocations, and the second is when the statistics are reset.
This extension provides 3 functions.
- function pg_stat_statements(boolean)
- function pg_stat_statements_info()
- function pg_stat_statements_reset(oid,oid,bigint,boolean)
function pg_stat_statements(boolean)
You can pass a boolean value to the pg_stat_statements() function to control whether the query text appears in the output. When you pass false, the query column is empty. Passing true shows the actual sql statements along with the query statistics.
SELECT *
FROM pg_stat_statements(true) limit 1;
Result :
-[ RECORD 1 ]--------------+------------------------------------------------------
userid | 41515
dbid | 102071
toplevel | t
queryid | 1916052192376627818
query | COMMENT ON COLUMN "crm_team"."color" IS 'Color Index'
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
calls | 1
total_exec_time | 0.006285
min_exec_time | 0.006285
max_exec_time | 0.006285
mean_exec_time | 0.006285
stddev_exec_time | 0
rows | 0
shared_blks_hit | 8
shared_blks_read | 0
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
shared_blk_read_time | 0
shared_blk_write_time | 0
local_blk_read_time | 0
local_blk_write_time | 0
temp_blk_read_time | 0
temp_blk_write_time | 0
wal_records | 2
wal_fpi | 0
wal_bytes | 151
wal_buffers_full | 0
jit_functions | 0
jit_generation_time | 0
jit_inlining_count | 0
jit_inlining_time | 0
jit_optimization_count | 0
jit_optimization_time | 0
jit_emission_count | 0
jit_emission_time | 0
jit_deform_count | 0
jit_deform_time | 0
parallel_workers_to_launch | 0
parallel_workers_launched | 0
stats_since | 2026-07-22 18:44:47.140327+05:30
minmax_stats_since | 2026-07-22 18:44:47.140327+05:30
Now pass the false to this function and check the result.
SELECT *
FROM pg_stat_statements(false) limit 1;
Result :
-[ RECORD 1 ]--------------+---------------------------------
userid | 41515
dbid | 102071
toplevel | t
queryid | 1916052192376627818
query |
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
calls | 1
total_exec_time | 0.006285
min_exec_time | 0.006285
max_exec_time | 0.006285
mean_exec_time | 0.006285
stddev_exec_time | 0
rows | 0
shared_blks_hit | 8
shared_blks_read | 0
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
shared_blk_read_time | 0
shared_blk_write_time | 0
local_blk_read_time | 0
local_blk_write_time | 0
temp_blk_read_time | 0
temp_blk_write_time | 0
wal_records | 2
wal_fpi | 0
wal_bytes | 151
wal_buffers_full | 0
jit_functions | 0
jit_generation_time | 0
jit_inlining_count | 0
jit_inlining_time | 0
jit_optimization_count | 0
jit_optimization_time | 0
jit_emission_count | 0
jit_emission_time | 0
jit_deform_count | 0
jit_deform_time | 0
parallel_workers_to_launch | 0
parallel_workers_launched | 0
stats_since | 2026-07-22 18:44:47.140327+05:30
minmax_stats_since | 2026-07-22 18:44:47.140327+05:30
Here, we can see that the value of the query column is empty.
function pg_stat_statements_info()
select * from pg_stat_statements_info();
Result :
dealloc | stats_reset
---------+----------------------------------
17 | 2026-07-22 18:40:44.995031+05:30
(1 row)
Purpose :
This displays metadata about the statistics collector, such as the number of deallocated entries and the last reset time.
function pg_stat_statements_reset(oid,oid,bigint,boolean)
select * from pg_stat_statements_reset();
Result :
pg_stat_statements_reset
----------------------------------
2026-07-22 18:52:58.561212+05:30
(1 row)
Purpose :
This function clears all collected query statistics and restarts data collection.
By using the pg_stat_statements extension, we can get some useful information by using the queries below
1. Top 5 queries by total execution time (most expensive overall)
SELECT
query,
calls,
round(total_exec_time::numeric, 2) AS total_exec_time_ms,
round(mean_exec_time::numeric, 2) AS avg_exec_time_ms,
rows
FROM pg_stat_statements(true)
ORDER BY total_exec_time DESC
LIMIT 5;
Result :
query | calls | total_exec_time_ms | avg_exec_time_ms | rows
---------------------------------------------------------------------------
DISCARD ALL | 41 | 1.73 | 0.04 | 0
select * from pg_stat_statements_reset() | 1 | 1.69 | 1.69 | 1
select datname from pg_database where datdba=(select usesysid from pg_user where usename=current_user) and not datistemplate and datallowconn and datname not in ($1 /*, ... */) order by datname | 26 | 1.18 | 0.05 | 26
INSERT INTO "bus_bus" ("channel", "create_date", "create_uid", "message", "write_date", "write_uid") VALUES ($1, $2::timestamp, $3, $4, $5::timestamp, $6) RETURNING "id" | 4 | 0.88 | 0.22 | 4
SELECT * +| 26 | 0.72 | 0.03 | 0
FROM ir_cron +| | | |
WHERE +| | | |
active IS TRUE +| | | |
AND (nextcall <= $1::timestamp +| | | |
OR id IN ( +| | | |
SELECT cron_id +| | | |
FROM ir_cron_trigger +| | | |
WHERE call_at <= $2::timestamp +| | | |
) +| | | |
) +| | | |
+| | | |
ORDER BY failure_count, priority, id | | | |
(5 rows)
(END)
2. Top 5 slowest queries (highest average execution time)
SELECT
query,
calls,
round(mean_exec_time::numeric, 2) AS avg_exec_time_ms,
round(max_exec_time::numeric, 2) AS max_exec_time_ms,
round(total_exec_time::numeric, 2) AS total_exec_time_ms
FROM pg_stat_statements(true)
WHERE calls > 1
ORDER BY mean_exec_time DESC
LIMIT 5;
Result :
query | calls | avg_exec_time_ms | max_exec_time_ms | total_exec_time_ms
---------------------------------------------------------------------------
INSERT INTO "bus_bus" ("channel", "create_date", "create_uid", "message", "write_date", "write_uid") VALUES ($1, $2::timestamp, $3, $4, $5::timestamp, $6) RETURNING "id" | 4 | 0.22 | 0.34 | 0.88
SELECT +| 2 | 0.15 | 0.17 | 0.30
query, +| | | |
calls, +| | | |
round(total_exec_time::numeric, $1) AS total_exec_time_ms, +| | | |
round(mean_exec_time::numeric, $2) AS avg_exec_time_ms, +| | | |
rows +| | | |
FROM pg_stat_statements($3) +| | | |
ORDER BY total_exec_time DESC +| | | |
LIMIT $4 | | | |
select datname from pg_database where datdba=(select usesysid from pg_user where usename=current_user) and not datistemplate and datallowconn and datname not in ($1 /*, ... */) order by datname | 27 | 0.05 | 0.08 | 1.22
SELECT ( SELECT max(id) FROM "orm_signaling_registry"), ( SELECT max(id) FROM "orm_signaling_default"), ( SELECT max(id) FROM "orm_signaling_assets"), ( SELECT max(id) FROM "orm_signaling_stable"), ( SELECT max(id) FROM "orm_signaling_templates"), ( SELECT max(id) FROM "orm_signaling_routing"), ( SELECT max(id) FROM "orm_signaling_groups") | 7 | 0.04 | 0.06 | 0.31
DISCARD ALL | 42 | 0.04 | 0.07 | 1.78
(5 rows)
(END)
3. Top 5 most frequently executed queries
SELECT
query,
calls,
round(total_exec_time::numeric, 2) AS total_exec_time_ms
FROM pg_stat_statements(true)
ORDER BY calls DESC
LIMIT 5;
Result :
query | calls | total_exec_time_ms
--------------------------------------------------+-------+--------------------
BEGIN ISOLATION LEVEL REPEATABLE READ READ WRITE | 46 | 0.28
DISCARD ALL | 43 | 1.84
COMMIT | 39 | 0.08
BEGIN ISOLATION LEVEL REPEATABLE READ READ WRITE | 32 | 0.36
SELECT (now() AT TIME ZONE $1) | 32 | 0.24
(5 rows)
4. Top 5 queries reading the most blocks
SELECT
query,
shared_blks_read,
shared_blks_hit,
calls
FROM pg_stat_statements(true)
ORDER BY shared_blks_read DESC
LIMIT 5;
Result :
query | shared_blks_read | shared_blks_hit | calls
---------------------------------------------------------------------------
SELECT "bus_bus"."id", "bus_bus"."message" FROM "bus_bus" WHERE ("bus_bus"."channel" IN ($1 /*, ... */) AND "bus_bus"."id" > $2) ORDER BY "bus_bus"."id" | 0 | 2 | 1
BEGIN ISOLATION LEVEL REPEATABLE READ READ WRITE | 0 | 0 | 46
SELECT "discuss_channel_member"."partner_id", "discuss_channel_member"."channel_id" FROM "discuss_channel" JOIN "discuss_channel_member" ON ("discuss_channel_member"."channel_id" = "discuss_channel"."id") WHERE "discuss_channel_member"."partner_id" IN ($1) ORDER BY "discuss_channel"."id" | 0 | 3 | 1
select datname from pg_database where datdba=(select usesysid from pg_user where usename=current_user) and not datistemplate and datallowconn and datname not in ($1 /*, ... */) order by datname | 0 | 140 | 28
SELECT "mail_presence"."id", "mail_presence"."guest_id", "mail_presence"."last_poll", "mail_presence"."last_presence", "mail_presence"."status" FROM "mail_presence" WHERE "mail_presence"."id" IN ($1) | 0 | 10 | 5
(5 rows)
(END)
5. Top 5 queries generating the most WAL
SELECT
query,
wal_bytes,
wal_records,
calls
FROM pg_stat_statements(true)
ORDER BY wal_bytes DESC
LIMIT 5;
Result :
query | wal_bytes | wal_records | calls
-------------------------------------------------------------------------------------------------------------------------------
INSERT INTO "bus_bus" ("channel", "create_date", "create_uid", "message", "write_date", "write_uid") VALUES ($1, $2::timestamp, $3, $4, $5::timestamp, $6) RETURNING "id" | 12808 | 14 | 4
SELECT "discuss_channel_member"."id", "discuss_channel_member"."partner_id", "discuss_channel_member"."guest_id", "discuss_channel_member"."channel_id", "discuss_channel_member"."custom_channel_name", "discuss_channel_member"."fetched_message_id", "discuss_channel_member"."seen_message_id", "discuss_channel_member"."new_message_separator", "discuss_channel_member"."custom_notifications", "discuss_channel_member"."mute_until_dt", "discuss_channel_member"."unpin_dt", "discuss_channel_member"."last_interest_dt", "discuss_channel_member"."last_seen_dt", "discuss_channel_member"."rtc_inviting_session_id", "discuss_channel_member"."create_uid", "discuss_channel_member"."create_date", "discuss_channel_member"."write_uid", "discuss_channel_member"."write_date" FROM "discuss_channel_member" WHERE "discuss_channel_member"."id" IN ($1) | 389 | 1 | 2
SELECT "mail_presence"."id", "mail_presence"."user_id" FROM "mail_presence" WHERE "mail_presence"."user_id" IN ($1) ORDER BY "mail_presence"."id" | 373 | 1 | 5
SELECT "discuss_channel_member"."id" FROM "discuss_channel_member" WHERE ("discuss_channel_member"."channel_id" IN ($1) AND "discuss_channel_member"."partner_id" IN ($2)) ORDER BY "discuss_channel_member"."id" | 369 | 1 | 2
+| 269 | 1 | 1
+| | |
+| | |
| | |
(5 rows)
(END)
The pg_stat_statements extension is really helpful for seeing how a postgres database is being used. We do not have to guess which queries are consuming the most time or resources. The pg_stat_statements extension gives us the information we need to know for sure.
When we learn how to turn on the pg_stat_statements extension, set its parameters, and understand what its views and functions are telling us, we can find the queries that are using a lot of resources. We can see how the queries are being run and make decisions, about how to make them better.
The pg_stat_statements extension is useful whether we are taking care of a database that people are using or trying to fix a problem that is happening while we are developing something. The pg_stat_statements extension gives us the information we need to make our database run better and to focus on the things that need attention.