-- Temporary file usage by database SELECT datname AS "database", temp_files AS "Temporary files", temp_bytes AS "Size of temporary files" FROM pg_stat_database; -- Cache Hit Ratio. Anything greater than 90% is always good SELECT sum(blks_hit)*100/sum(blks_hit+blks_read) AS hit_ratio FROM pg_stat_database; -- Top Queries SELECT substr(query, 0, 250), calls, to_char(total_time/(60*60), '999,999,9999,999') AS "Cumulative Time (hrs)", rows, to_char(total_time/calls, '999.999') AS per_call_ms FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10; -- Top 20 cpu heavy queries - postgres12 and below SELECT substring(query, 1, 50) AS short_query, round(total_time::numeric, 2) AS total_time, calls, rows, round(total_time::numeric / calls, 2) AS avg_time, round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu FROM pg_stat_statements ORDER BY percentage_cpu DESC LIMIT 20; -- Top 20 cpu heavy queries - postgres13 and higher SELECT query AS short_query, round((total_plan_time + total_exec_time)::numeric, 2), calls, rows, round((total_plan_time + total_exec_time)::numeric / calls, 2) AS avg_time, round((100 * (total_plan_time + total_exec_time) / sum((total_plan_time + total_exec_time)::numeric) OVER ())::numeric, 2) AS percentage_cpu FROM pg_stat_statements ORDER BY percentage_cpu DESC LIMIT 20; -- Top 20 temporary file usage SELECT interval '1 millisecond' * total_time AS total_exec_time, to_char(calls, 'FM999G999G999G990') AS ncalls, total_time / calls AS avg_exec_time_ms, interval '1 millisecond' * (blk_read_time + blk_write_time) AS sync_io_time, temp_blks_written, query AS query FROM pg_stat_statements WHERE temp_blks_written > 0 ORDER BY temp_blks_written DESC LIMIT 20; -- The following example displays the statistics for the ten tables that -- have the greatest heap_blks_hit activity SELECT * FROM pg_statio_all_tables ORDER BY heap_blks_hit DESC LIMIT 10; -- The following example displays the statistics for the ten indexes that -- have the greatest idx_blks_hit activity: SELECT * FROM pg_statio_all_indexes ORDER BY idx_blks_hit DESC LIMIT 10; -- Unused Indexes. Anything that comes back 0 are areas to look at. SELECT * FROM pg_stat_all_indexes WHERE idx_scan = 0; -- % for index used. Greater the value the better. SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables WHERE (seq_scan + idx_scan) > 0 ORDER BY n_live_tup DESC;