View statistics_top_ten_shared_block_hits_queries
Top ten queries by shared block hits.
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 |
|---|---|---|
| rolename | regrole | The role/user who executed the query. |
| database_name | name | The database name in which the query has been executed. |
| 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. |
| query | text | This is the SQL source of the query. |
Example
SELECT *
FROM statistics_top_ten_time_consuming_queries;
| rolename | database_name | shared_buffer_hit | shared_buffer_read | query |
|---|---|---|---|---|
| stefanie | pgsql_tweaks_test | 56001 | 0 | SELECT * FROM pg_functions |
| stefanie | pgsql_tweaks_test | 37346 | 0 | WITH test AS &npsp;&npsp;( &npsp;&npsp;&npsp;&npsp;SELECT count(*) as key_count &npsp;&npsp;&npsp;&npsp;&npsp;&npsp;, $1 AS zero &npsp;&npsp;&npsp;&npsp;FROM pg_functions &npsp;&npsp;) SELECT CASE &npsp;&npsp;WHEN key_count >= $2 THEN &npsp;&npsp;&npsp;&npsp;$3 &npsp;&npsp;ELSE &npsp;&npsp;&npsp;&npsp;($4 / zero)::BOOLEAN &npsp;&npsp;END AS res FROM test |