View statistics_top_ten_query_times

Top ten queries with statistics about shared buffer read/write/dirty.

COUTION

The schema where the extension pg_stat_statements is installed has to be in the search_path of the user!

This view needs pg_stat_statements to be installed. When the extension is not installed, the view will not be installed by the extension.

Columns

Column Name	datatype	Description
queryid	bigint	Query identifier, can be used to join the view with pg_stat_statements to get more information
query	text	This is the SQL source of the query.
total_time	numeric	This is the total execution time of the query in milliseconds.
calls	bigint	This is many times a statement has been executed.
mean_time	numeric	This is the average execution time of the query in milliseconds.
percentage_of_total_time	numeric	Percentage of execution time of all executed queries.
shared_buffer_hit	bigint	This is the count of times disk blocks were found already cached in memory (no I/O was needed).
shared_buffer_read	bigint	This is the read is the count of times disk blocks had to be read into memory, which indicates actual I/O operations. High values in shared_blks_read suggest that these queries are the most I/O intensive, which can be a starting point for performance optimization.
shared_buffer_written	bigint	This is the number of shared blocks written into shared buffer and written to disk.
shared_buffer_dirty	bigint	This is the number of shared blocks “dirtied” by the query. Dirteid means the number of blocks where at least one tuple got modified and hat ot be written to disk.

Example

SELECT *
FROM statistics_top_ten_query_times;

queryid	query	total_time	calls	mean_time	percentage_of_total_time	shared_buffer_hit	shared_buffer_read	shared_buffer_written	shared_buffer_dirty
-8275065539412921784	SELECT * FROM pg_table_bloat	251.40	1	251.40	43.37	17737	17583	0	0