The pg_stat_statements extension is one of the most valuable tools for understanding how queries perform in Postgres. It provides detailed execution statistics that help database administrators and developers analyze query behavior, identify performance bottlenecks, and monitor database workloads.
With each release of Postgres, this view is enhanced with additional metadata that offers deeper insight into query execution. This article compares the pg_stat_statements view in Postgres 17, 18, and 19, highlighting the new columns introduced in each version and explaining how they improve query monitoring and performance analysis.
The main purpose of the pg_stat_statement extension is to provide the long running query execution related details.
After creating the extension we need to specify the value for the parameter named shared_preload_libraries in postgres
Create the extension named pg_stat_statements;
create extension pg_stat_statements ;
Now check the results from the pg_stat_statements view like this.
select * from pg_stat_statements;
Result:
ERROR: pg_stat_statements must be loaded via "shared_preload_libraries"
Check the current value of the shared_preload_libraries parameter.
show shared_preload_libraries ;
Result:
shared_preload_libraries
--------------------------
(1 row)
It is empty, so now we need to set the value for the shared_preload_libraries parameter in the postgres conf file.
Check the path of the Postgres configuration file like this.
show config_file ;
Result:
config_file
-----------------------------------------
/etc/postgresql/18/main/postgresql.conf
(1 row)
Now open and edit this file.
sudo nano /etc/postgresql/18/main/postgresql.conf
Add the value for the shared_preload_libraries parameter.
shared_preload_libraries = 'pg_stat_statements'
Now restart the postgres
sudo systemctl restart postgresql
Open the psql terminal and then check again any result from the pg_stat_statements view.
select * from pg_stat_statements limit 1;
Result:
-[ RECORD 1 ]----------+----------------------------------------------------------------------------------------------------------
userid | 10
dbid | 16389
toplevel | t
queryid | 1732703784256639058
query | SELECT name FROM pg_catalog.pg_available_extensions WHERE name LIKE $1 AND installed_version IS NULL+
| LIMIT $2
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.536915
min_exec_time | 0.536915
max_exec_time | 0.536915
mean_exec_time | 0.536915
stddev_exec_time | 0
rows | 1
shared_blks_hit | 0
shared_blks_read | 1
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 | 0
wal_fpi | 0
wal_bytes | 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
stats_since | 2026-09-23 09:45:27.744657+05:30
minmax_stats_since | 2026-09-23 09:45:27.744657+05:30
This is the columns in the pg_stat_statement view of postgres version 17, and it has nearly 43 columns.
This metadata of each query includes user id, database id, query id, top level query matching, query, planning related metadata, execution related metadata, shared blocks related metadata, local blocks related metadata, wal related metadata, and jit related metadata.
Now create the same extension and set the value for the parameter named shared_preload_libraries and inspect one record from this view.
select * from pg_stat_statements limit 1;
Result:
-[ RECORD 1 ]--------------+---------------------------------------------------------------------------------------------------------------
userid | 16463
dbid | 33003
toplevel | t
queryid | -4595100093194641451
query | INSERT INTO "ir_model_fields_selection" ("field_id", "value", "name", "sequence") VALUES ($1, $2, $3, 4),(5, $6, $7, 8),(9, $10, $11, 12),(13, $14, $15, 16),(17, $18, $19, 20),(21, $22, $23, 24),(25, $26, $27, 28),(29, $30, $31, 32),(33, $34, $35, 36),(37, $38, $39, 40),(41, $42, $43, 44),(45, $46, $47, 48),(49, $50, $51, 52),(53, $54, $55, 56),(57, $58, $59, 60),(61, $62, $63, 64),(65, $66, $67, 68),(69, $70, $71, 72),(73, $74, $75, 76),(77, $78, $79, 80),(81, $82, $83, 84),(85, $86, $87, 88),(89, $90, $91, 92),(93, $94, $95, 96),(97, $98, $99, 100),(101, $102, $103, 104),(105, $106, $107, 108),(109, $110, $111, 112),(113, $114, $115, 116),(117, $118, $119, $120)+
| ON CONFLICT ("field_id", "value") DO UPDATE SET ("field_id", "value", "name", "sequence") = (EXCLUDED."field_id", EXCLUDED."value", COALESCE("ir_model_fields_selection"."name", $121::jsonb) || EXCLUDED."name", EXCLUDED."sequence") +
| RETURNING id
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.328079
min_exec_time | 0.328079
max_exec_time | 0.328079
mean_exec_time | 0.328079
stddev_exec_time | 0
rows | 30
shared_blks_hit | 540
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 | 160
wal_fpi | 0
wal_bytes | 11466
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-09-23 09:26:50.253225+05:30
minmax_stats_since | 2026-09-23 09:26:50.253225+05:30
By comparing the each query metadata of pg_stat_statements between postgres version 17 and 18, 3 new columns were added to the pg_stat_statements view in postgres version 18.
Those 3 columns are
- Wal_buffers_full - This determines how many times a query had to write WAL buffers because they became full while the query was running.
- parallel_workers_to_launch - This column indicates the number of parallel workers that can launch during the query execution.
- parallel_workers_launched - This indicates the number of parallel workers launched during this query execution.
Now check the record from pg_stat_statements in PostgreSQL version 19.
select * from pg_stat_statements limit 1;
Result:
-[ RECORD 1 ]--------------+---------------------------------------------------------------------------------------------------------------
userid | 106423
dbid | 118702
toplevel | t
queryid | -3740005557930860484
query | SELECT "res_partner"."id", "res_partner"."name", "res_partner"."complete_name", "res_partner"."date", "res_partner"."title", "res_partner"."parent_id", "res_partner"."ref", "res_partner"."lang", "res_partner"."tz", "res_partner"."user_id", "res_partner"."vat", "res_partner"."company_registry", "res_partner"."website", "res_partner"."comment", "res_partner"."active", "res_partner"."employee", "res_partner"."function", "res_partner"."type", "res_partner"."street", "res_partner"."street2", "res_partner"."zip", "res_partner"."city", "res_partner"."state_id", "res_partner"."country_id", "res_partner"."partner_latitude", "res_partner"."partner_longitude", "res_partner"."email", "res_partner"."phone", "res_partner"."mobile", "res_partner"."is_company", "res_partner"."industry_id", "res_partner"."company_id", "res_partner"."color", "res_partner"."partner_share", "res_partner"."commercial_partner_id", "res_partner"."commercial_company_name", "res_partner"."company_name", "res_partner"."create_uid", "res_partner"."create_date", "res_partner"."write_uid", "res_partner"."write_date" FROM "res_partner" WHERE ("res_partner"."id" IN ($1))
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
calls | 13
total_exec_time | 0.458503
min_exec_time | 0.022348
max_exec_time | 0.054057
mean_exec_time | 0.03526946153846154
stddev_exec_time | 0.011516051223704341
rows | 13
shared_blks_hit | 13
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 | 0
wal_fpi | 0
wal_bytes | 0
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
generic_plan_calls | 0
custom_plan_calls | 0
stats_since | 2026-09-22 15:33:48.87091+05:30
minmax_stats_since | 2026-09-22 15:33:48.87091+05:30
Now, by comparing this with PostgreSQL version 18, PostgreSQL 19 has introduced two new columns
Those 2 columns are :
- generic_plan_calls - It counts the number of times postgres executed a statement using a generic execution plan.
- custom_plan_calls - It counts the number of times postgres executed a statement using a custom execution plan.
These columns help you understand which type of execution plan PostgreSQL is using for prepared statements.
The pg_stat_statements view continues to evolve with every postgres release, providing more detailed information about query execution. PostgreSQL 18 introduced new statistics for WAL buffer usage and parallel worker activity, while PostgreSQL 19 added execution plan counters that distinguish between generic and custom plans.
Understanding these new columns makes it easier to interpret query behavior, troubleshoot performance issues, and take advantage of the additional monitoring capabilities available in newer PostgreSQL versions. By comparing the metadata across versions, we can better understand what has changed and use the latest statistics more effectively in your day-to-day database analysis.