Useful queries for postgresql for monitoring queries and disk usage

postgresql_cheat_sheet.sql

-- 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;