/**
 * Top ten queries with statistics about
 * - shared buffer read/write/dirty
 * 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_query_times AS
SELECT queryid
	, query
	, round (total_exec_time::NUMERIC, 2) AS total_time
	, calls
	, round (mean_exec_time::NUMERIC, 2) AS mean_time
	, round
		(
			(
				100 * total_exec_time /
				sum(total_exec_time::numeric)
				OVER ()
			)::NUMERIC, 2
		) AS percentage_of_total_time
	, shared_blks_hit AS shared_buffer_hit
	, shared_blks_read AS shared_buffer_read
	, shared_blks_written AS shared_buffer_written
	, shared_blks_dirtied AS shared_buffer_dirty
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10
;
$string$
;
		EXECUTE view_source;

		COMMENT ON VIEW statistics_top_ten_query_times IS 'Top ten queries in execution times.';
		COMMENT ON COLUMN statistics_top_ten_query_times.queryid IS 'Query identifier, can be used to join the view with pg_stat_statements to get more information';
		COMMENT ON COLUMN statistics_top_ten_query_times.query IS 'This is the SQL source of the query.';
		COMMENT ON COLUMN statistics_top_ten_query_times.total_time IS 'This is the total execution time of the query in milliseconds.';
		COMMENT ON COLUMN statistics_top_ten_query_times.mean_time IS 'This is the average execution time of the query in milliseconds.';
		COMMENT ON COLUMN statistics_top_ten_query_times.calls IS 'This is many times a statement has been executed.';
		COMMENT ON COLUMN statistics_top_ten_query_times.percentage_of_total_time IS 'Percentage of execution time of all executed queries.';
		COMMENT ON COLUMN statistics_top_ten_query_times.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_query_times.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_query_times.shared_buffer_written IS 'This is the number of shared blocks written into shared buffer and written to disk.';
		COMMENT ON COLUMN statistics_top_ten_query_times.shared_buffer_dirty IS '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.';

	END IF;

END $$;