/**
 * Top ten queries by shared block hits
 * NOTE: The schema where the extension pg_stat_statements is installed
 *       has to be in the search_path of the user!
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

DO $$
DECLARE
	pg_extension_installed BOOLEAN;
	view_source TEXT;
BEGIN

	SELECT count(*) AS pg_stat_statements_exists
	FROM pg_extension
	WHERE extname = 'pg_stat_statements'
	INTO pg_extension_installed
	;

	IF pg_extension_installed THEN
		-- The view is only created when pgstattuple is installed
		view_source :=
$string$
CREATE OR REPLACE VIEW statistics_top_ten_shared_block_hits_queries AS
SELECT s.userid::regrole AS rolename
	, d.datname AS database_name
	, s.shared_blks_hit AS shared_buffer_hit
	, s.shared_blks_read AS shared_buffer_read
	, s.query
FROM pg_stat_statements AS s
INNER JOIN pg_catalog.pg_database AS d
	ON s.dbid = d.oid
ORDER BY s.shared_blks_hit DESC
LIMIT 10
;
$string$
;
		EXECUTE view_source;

		COMMENT ON VIEW statistics_top_ten_shared_block_hits_queries IS 'Top ten queries by shared block hits.';
		COMMENT ON COLUMN statistics_top_ten_shared_block_hits_queries.rolename IS 'The role/user who executed the query.';
		COMMENT ON COLUMN statistics_top_ten_shared_block_hits_queries.database_name IS 'The database name in which the query has been executed.';
		COMMENT ON COLUMN statistics_top_ten_shared_block_hits_queries.shared_buffer_hit IS 'This is the count of times disk blocks were found already cached in memory (no I/O was needed).';
		COMMENT ON COLUMN statistics_top_ten_shared_block_hits_queries.shared_buffer_read IS '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.';
		COMMENT ON COLUMN statistics_top_ten_shared_block_hits_queries.query IS 'This is the SQL source of the query.';

	END IF;

END $$;