pg_profile: SQL query statistics

March 8, 2024

Summary: This report section contains tables of top statements during report interval sorted by several important statistics.

Table of Contents

The query statistics data is captured from pg_stat_statements view if it was available at the time of samples. Statements can be highlighted in all SQL-related sections by a single mouse click on it. Also this click will show a query text preview just under the query statistics row. Query text preview can be hidden with a second click on a query.

Top SQL by elapsed time

This table contains top pg_profile.topn statements sorted by elapsed time total_plan_time + total_exec_time of pg_stat_statements view. Available since PostgreSQL 13

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
%Total - total time of this statement as a percentage of total time of all statements in a cluster
Time (s) - time spent in this statement (in seconds)
- Elapsed - total time, spent in this statement (total_plan_time + total_exec_time)
- Plan - time spent in planning this statement (total_plan_time field)
- Exec - time spent executing this query (total_exec_time field)
JIT time (s) - Total time spent by JIT executing this statement, in seconds (jit_generation_time + jit_inlining_time + jit_optimization_time + jit_emission_time)
I/O time (s):
- Read - time spent reading blocks (blk_read_time field)
- Write - time spent writing blocks (blk_write_time field)
CPU time (s) - time spent on CPU. Based on data provided by pg_stat_kcache extension.
- Usr - CPU time spent in user space
- Sys - CPU time spent in kernel space
Plans - number of times the statement was planned (plans field)
Executions - number of times the statement was executed (calls field)

Top SQL by planning time

Top pg_profile.topn statements sorted by total_plan_time field of pg_stat_statements view. Available since PostgreSQL 13

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
Plan elapsed (s) - time spent in planning this statement (total_plan_time field)
%Elapsed - plan time of this statement as a percentage of statement elapsed time
Plan times (ms) - detailed plan time statistics of this statement (in milliseconds)
- Mean - mean time spent planning this statement (mean_plan_time field)
- Min - minimum time spent planning this statement (min_plan_time field)
- Max - maximum time spent planning this statement (max_plan_time field)
- StdErr - population standard deviation of time spent planning this statement (stddev_plan_time field)
Plans - number of times this statement was planned (plans field)
Executions - number of times this statement was executed (calls field)

Top SQL by execution time

Top pg_profile.topn statements sorted by total_time (or total_exec_time) field of pg_stat_statements view

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
Exec (s) - time spent executing this statement (total_exec_time field)
%Elapsed - execution time of this statement as a percentage of statement elapsed time
%Total - exec time of this statement as a percentage of total elapsed time of all statements in a cluster
JIT time (s) - Total time spent by JIT executing this statement, in seconds (jit_generation_time + jit_inlining_time + jit_optimization_time + jit_emission_time)
I/O time (s):
- Read - time spent reading blocks (blk_read_time field)
- Write - time spent writing blocks (blk_write_time field)
Rows - number of rows retrieved or affected by the statement (rows field)
Execution times (ms) - detailed execution time statistics of this statement (in milliseconds)
- Mean - mean time spent executing this statement (mean_exec_time field)
- Min - minimum time spent executing this statement (min_exec_time field)
- Max - maximum time spent executing this statement (max_exec_time field)
- StdErr - population standard deviation of time spent executing this statement (stddev_exec_time field)
Executions - number of times this statement was executed (calls field)

Top SQL by executions

Top pg_profile.topn statements sorted by calls field of pg_stat_statements view

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
Executions - count of statement executions (calls field)
%Total - calls of this statement as a percentage of total calls of all statements in a cluster
Rows - number of rows retrieved or affected by the statement (rows field)
Mean(ms) - mean time spent in the statement, in milliseconds (mean_time or mean_exec_time field)
Min(ms) - minimum time spent in the statement, in milliseconds (min_time or min_exec_time field)
Max(ms) - maximum time spent in the statement, in milliseconds (max_time or max_exec_time field)
StdErr(ms) - population standard deviation of time spent in the statement, in milliseconds (stddev_time or stddev_exec_time field)
Elapsed(s) - amount of time spent executing this query, in seconds (total_time or total_exec_time field)

Top SQL by I/O wait time

Top pg_profile.topn statements sorted by read and write time (blk_read_time + blk_write_time)

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
IO(s) - amount of time spent on reading and writing (I/O time) by this statement in seconds (blk_read_time + blk_write_time)
R(s) - amount of time spent on reading by this statement in seconds (blk_read_time)
W(s) - amount of time spent on writing by this statement in seconds (blk_write_time)
%Total - I/O time of this statement as a percentage of total I/O time for all statements in a cluster
Reads - number of blocks read by this statement divided in three sub-columns:
- Shr - shared reads (shared_blks_read field)
- Loc - local reads (local_blks_read field)
- Tmp - temp reads (temp_blks_read field)
Writes - number of blocks written by this statement divided in three sub-columns:
- Shr - shared writes (shared_blks_written field)
- Loc - local writes (local_blks_written field)
- Tmp - temp writes (temp_blks_written field)
Elapsed(s) - amount of time spent executing this query, in seconds (total_time or total_exec_time field)
Executions - number of executions for this statement (calls field)

Top SQL by shared blocks fetched

Top pg_profile.topn statements sorted by read and hit blocks, helping to detect the most data processing statements.

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
blks fetched - number of fetched blocks (expression: shared_blks_hit + shared_blks_read)
%Total - blocks fetched for this statement as a percentage of total blocks fetched for all statements in a cluster
Hits(%) - percentage of blocks got from buffers within all blocks got
Elapsed(s) - amount of time spent in this statement, in seconds (total_time or total_exec_time+total_plan_time field)
Rows - number of rows retrieved or affected by the statement (rows field)
Executions - number of executions for this statement (calls field)

Top SQL by shared blocks dirtied

Top pg_profile.topn statements sorted by shared dirtied buffer count, helping to detect most data changing statements.

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
Dirtied - number of shared blocks dirtied by this statement (shared_blks_dirtied field)
%Total - shared blocks dirtied by this statement as a percentage of total shared blocks dirtied by all statements in a cluster
Hits(%) - percentage of blocks got from buffers within all blocks got
WAL - amount of WAL generated by the statement (wal_bytes field)
%Total - amount of WAL generated by the statement as a percentage of total WAL generated in cluster (pg_current_wal_lsn() increment)
Elapsed(s) - amount of time spent in this statement, in seconds (total_time or total_exec_time+total_plan_time field)
Rows - number of rows retrieved or affected by the statement (rows field)
Executions - number of executions for this statement (calls field)

Top SQL by shared blocks written

Top pg_profile.topn statements, which had to perform writes sorted by written blocks count.

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
Written - number of blocks written by this statement (shared_blks_written field)
%Total - number of blocks written by this statement as a percentage of total blocks written by all statements in a cluster
%BackendW - number of blocks written by this statement as a percentage of all blocks written in a cluster by backends (buffers_backend field of pg_stat_bgwriter view)
Hits(%) - percentage of blocks got from buffers within all blocks got
Elapsed(s) - amount of time spent in this statement, in seconds (total_time or total_exec_time+total_plan_time field)
Rows - number of rows retrieved or affected by the statement (rows field)
Executions - number of executions for this statement (calls field)

Top SQL by WAL size

Top pg_profile.topn statements, sorted by WAL generated (available since pg_stat_statements v1.8)

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
WAL - amount of WAL generated by the statement (wal_bytes field)
%Total - amount of WAL generated by the statement as a percentage of total WAL generated in cluster (pg_current_wal_lsn() increment)
Dirtied - number of shared blocks dirtied by this statement (shared_blks_dirtied field)
WAL FPI - total number of WAL full page images generated by the statement (wal_fpi field)
WAL records - total amount of WAL bytes generated by the statement (wal_bytes field)

Top SQL by temp usage

Top pg_profile.topn statements sorted by temp I/O, calculated as the sum of temp_blks_read, temp_blks_written, local_blks_read and local_blks_written fields

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
Local fetched - number of retrieved local blocks (expression: local_blks_hit + local_blks_read)
Hits(%) - percentage of local blocks got from temp buffers within all local blocks got
Local (blk) - I/O statistics of blocks used in temporary tables
- Write - number of written local blocks (local_blks_written)
- %Total - local_blks_written of this statement as a percentage of total local_blks_written for all statements in a cluster
- Read - number of read local blocks (local_blks_read)
- %Total - local_blks_read of this statement as a percentage of total local_blks_read for all statements in a cluster
Temp (blk) - I/O statistics of blocks used in operations (like sorts and joins)
- Write - number of written temp blocks (temp_blks_written)
- %Total - temp_blks_written of this statement as a percentage of total temp_blks_written for all statements in a cluster
- Read - number of read local blocks (temp_blks_read)
- %Total - temp_blks_read of this statement as a percentage of total temp_blks_read for all statements in a cluster
Elapsed(s) - amount of time spent in this statement, in seconds (total_time or total_exec_time+total_plan_time field)
Rows - number of rows retrieved or affected by the statement (rows field)
Executions - number of executions for this statement (calls field)

Top SQL by temp I/O time

Top pg_profile.topn statements sorted by temp I/O time, calculated as the sum of temp_blk_read_time and temp_blk_write_time fields

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
Temp I/O time (s) - time spent on temporary file blocks I/O
- Read - time spent reading temporary file blocks, in seconds
- Write - time spent writing temporary file blocks, in seconds
- %Total - temporary I/O time of this statement as a percentage of total temporary I/O time of all statements in a cluster
Temp (blk) - I/O statistics of blocks used in operations (like sorts and joins)
- Write - number of written temp blocks (temp_blks_written)
- %Total - temp_blks_written of this statement as a percentage of total temp_blks_written for all statements in a cluster
- Read - number of read local blocks (temp_blks_read)
- %Total - temp_blks_read of this statement as a percentage of total temp_blks_read for all statements in a cluster
Elapsed(s) - amount of time spent in this statement, in seconds (total_time or total_exec_time+total_plan_time field)
Rows - number of rows retrieved or affected by the statement (rows field)
Executions - number of executions for this statement (calls field)

Resource usage statistics

This section contains resource usage statistics provided by pg_stat_kcache extension if it was available during report interval.

Top SQL by system and user time

Top pg_profile.topn statements sorted by sum of fields user_time and system_time fields of pg_stat_kcache.

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
User Time - User CPU time used
- Plan (s) - User CPU time elapsed during planning in seconds (plan_user_time field)
- Exec (s) - User CPU time elapsed during execution in seconds (exec_user_time or user_time field)
- %Total - User CPU time of this statement as a percentage of a summary user CPU time for all statements
System Time - System CPU time used
- Plan (s) - System CPU time elapsed during planning in seconds (plan_system_time field)
- Exec (s) - System CPU time elapsed during execution in seconds (exec_system_time or system_time field)
- %Total - System CPU time of this statement as a percentage of a summary system CPU time for all statements

Top SQL by JIT elapsed time

Top pg_profile.topn statements sorted by the total JIT-related time (i.e. jit_generation_time + jit_inlining_time + jit_optimization_time + jit_emission_time). Available since PostgreSQL 15 (pg_stat_statements 1.10).

Query ID - Query identifier as provided by the pg_stat_statements extension (queryid) in hexadecimal notation. Alternative query identifier as a hash of dbid, userid and queryid is shown in square brackets, this identifier is compatible with pgcenter utility.
Database - Statement database name (derived from dbid field)
User - User name executed this statement (derived from userid field)
JIT total (s) - time spent by JIT in seconds (jit_generation_time + jit_inlining_time + jit_optimization_time + jit_emission_time)
Generation
- Count - Total number of functions JIT-compiled by the statement
- Gen. time - Total time spent by the statement on generating JIT code
Inlining
- Count - Number of times functions have been inlined
- Time - Total time spent by statement on inlining functions (in seconds)
Optimization
- Count - Number of times statement has been optimized
- Time - Total time spent by statement on optimizing (in seconds)
Emission
- Count - Number of times code has been emitted
- Time - Total time spent by statement on emitting code (in seconds)
Time (s) - time spent in this statement (in seconds)
- Plan - time spent in planning this statement (total_plan_time field)
- Exec - time spent executing this query (total_exec_time field)
I/O time (s):
- Read - time spent reading blocks (blk_read_time field)
- Write - time spent writing blocks (blk_write_time field)

Complete list of SQL texts

Query texts of all statements mentioned in report. You can use Query ID link in any statistic table to get there and see query text. Queries in this section are limited to pg_profile.max_query_length (default 20000) characters, but full query text can be obtained from extension tables. One queryid entry in this list can have several actual query texts captured in different samples. Only three latest queries texts captured will be listed.

See more

pg_profile: PostgreSQL historical workload reports