Last active
May 1, 2024 14:04
-
-
Save leemour/d363fdd7d75b57c1a3e1950bd21e141f to your computer and use it in GitHub Desktop.
PostgreSQL pg_stat_statements detailed output for query performance analysis and optimization
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
t.tablename, | |
foo.indexname, | |
c.reltuples AS num_rows, | |
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size, | |
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size, | |
pg_relation_size(quote_ident(indexrelname)) as index_size_bytes, | |
CASE WHEN indisunique THEN 'Y' | |
ELSE 'N' | |
END AS UNIQUE, | |
CASE WHEN EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conindid = foo.indexrelid) THEN 'Y' | |
ELSE 'N' | |
END AS ISCONSTRAINT, | |
idx_scan AS number_of_scans, | |
idx_tup_read AS tuples_read, | |
idx_tup_fetch AS tuples_fetched, | |
i.indexdef | |
FROM pg_tables t | |
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname | |
LEFT OUTER JOIN | |
( SELECT x.indexrelid, c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x | |
JOIN pg_class c ON c.oid = x.indrelid | |
JOIN pg_class ipg ON ipg.oid = x.indexrelid | |
JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid ) | |
AS foo | |
ON t.tablename = foo.ctablename | |
JOIN pg_indexes i ON t.tablename = i.tablename and foo.indexname = i.indexname | |
WHERE t.schemaname='public' | |
ORDER BY idx_scan ASC, 6 desc; | |
select name, setting, unit, | |
case when context='postmaster' then 'Restart' | |
else 'Reload' | |
end as restart_reload | |
from pg_catalog.pg_settings where name in ('temp_buffers','checkpoint_completion_target','checkpoint_timeout','effective_cache_size','effective_io_concurrency','wal_buffers','random_page_cost', | |
'maintenance_work_mem','max_parallel_maintenance_workers','max_parallel_workers','max_parallel_workers_per_gather','min_wal_size','max_wal_size','max_worker_processes','shared_buffers','work_mem') | |
order by name; |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
with pg_stat_statements_normalized as ( | |
select *, | |
translate( | |
regexp_replace( | |
regexp_replace( | |
regexp_replace( | |
regexp_replace( | |
regexp_replace( | |
regexp_replace(query, | |
E'\\?(::[a-zA-Z_]+)?( *, *\\?(::[a-zA-Z_]+)?)+', '?', 'g'), | |
E'\\$[0-9]+(::[a-zA-Z_]+)?( *, *\\$[0-9]+(::[a-zA-Z_]+)?)*', '$N', 'g'), | |
E'--.*$', '', 'ng'), | |
E'/\\*.*?\\*/', '', 'g'), | |
E'[\\n\\r]+', ' ', 'g' ), | |
E'\s+', ' ', 'g'), | |
E'\r', '') | |
as query_normalized | |
--if current database is postgres then generate report for all databases otherwise generate for current database only | |
from pg_stat_statements where dbid in (SELECT oid from pg_database where current_database() = 'postgres' or datname=current_database()) | |
), | |
totals as ( | |
select sum(total_exec_time) AS total_exec_time, sum(blk_read_time+blk_write_time) as io_time, | |
sum(total_exec_time-blk_read_time-blk_write_time) as cpu_time, sum(calls) AS ncalls, | |
sum(rows) as total_rows FROM pg_stat_statements | |
WHERE dbid in (SELECT oid from pg_database where current_database() = 'postgres' or datname=current_database()) | |
), | |
_pg_stat_statements as ( | |
select | |
(select datname from pg_database where oid = p.dbid) as database, | |
(select rolname from pg_roles where oid = p.userid) as username, | |
--select shortest query, replace \n\n-- strings to avoid email clients format text as footer | |
substring( | |
translate( | |
replace( | |
(array_agg(query order by length(query)))[1], | |
E'-- \n', | |
E'--\n'), | |
E'\r', ''), | |
1, 8192) as query, | |
sum(total_exec_time) as total_exec_time, | |
sum(blk_read_time) as blk_read_time, sum(blk_write_time) as blk_write_time, | |
sum(calls) as calls, sum(rows) as rows | |
from pg_stat_statements_normalized p | |
where TRUE | |
group by dbid, userid, md5(query_normalized) | |
), | |
totals_readable as ( | |
select to_char(interval '1 millisecond' * total_exec_time, 'HH24:MI:SS') as total_exec_time, | |
(100*io_time/total_exec_time)::numeric(20,2) AS io_time_percent, | |
to_char(ncalls, 'FM999,999,999,990') AS total_queries, | |
(select to_char(count(distinct md5(query)), 'FM999,999,990') from _pg_stat_statements) as unique_queries | |
from totals | |
), | |
statements as ( | |
select | |
(100*total_exec_time/(select total_exec_time from totals)) AS time_percent, | |
(100*(blk_read_time+blk_write_time)/(select greatest(io_time, 1) from totals)) AS io_time_percent, | |
(100*(total_exec_time-blk_read_time-blk_write_time)/(select cpu_time from totals)) AS cpu_time_percent, | |
to_char(interval '1 millisecond' * total_exec_time, 'HH24:MI:SS') AS total_exec_time, | |
(total_exec_time::numeric/calls)::numeric(20,2) AS avg_time, | |
((total_exec_time-blk_read_time-blk_write_time)::numeric/calls)::numeric(20, 2) AS avg_cpu_time, | |
((blk_read_time+blk_write_time)::numeric/calls)::numeric(20, 2) AS avg_io_time, | |
to_char(calls, 'FM999,999,999,990') AS calls, | |
(100*calls/(select ncalls from totals))::numeric(20, 2) AS calls_percent, | |
to_char(rows, 'FM999,999,999,990') AS rows, | |
(100*rows/(select total_rows from totals))::numeric(20, 2) AS row_percent, | |
database, | |
username, | |
query | |
from _pg_stat_statements | |
where ((total_exec_time-blk_read_time-blk_write_time)/(select cpu_time from totals)>=0.01 or (blk_read_time+blk_write_time)/(select greatest(io_time, 1) from totals)>=0.01 or calls/(select ncalls from totals)>=0.02 or rows/(select total_rows from totals)>=0.02) | |
union all | |
select | |
(100*sum(total_exec_time)::numeric/(select total_exec_time from totals)) AS time_percent, | |
(100*sum(blk_read_time+blk_write_time)::numeric/(select greatest(io_time, 1) from totals)) AS io_time_percent, | |
(100*sum(total_exec_time-blk_read_time-blk_write_time)::numeric/(select cpu_time from totals)) AS cpu_time_percent, | |
to_char(interval '1 millisecond' * sum(total_exec_time), 'HH24:MI:SS') AS total_exec_time, | |
(sum(total_exec_time)::numeric/sum(calls))::numeric(20,2) AS avg_time, | |
(sum(total_exec_time-blk_read_time-blk_write_time)::numeric/sum(calls))::numeric(20, 2) AS avg_cpu_time, | |
(sum(blk_read_time+blk_write_time)::numeric/sum(calls))::numeric(20, 2) AS avg_io_time, | |
to_char(sum(calls), 'FM999,999,999,990') AS calls, | |
(100*sum(calls)/(select ncalls from totals))::numeric(20, 2) AS calls_percent, | |
to_char(sum(rows), 'FM999,999,999,990') AS rows, | |
(100*sum(rows)/(select total_rows from totals))::numeric(20, 2) AS row_percent, | |
'all' as database, | |
'all' as username, | |
'other' as query | |
from _pg_stat_statements | |
where not ((total_exec_time-blk_read_time-blk_write_time)/(select cpu_time from totals)>=0.01 or (blk_read_time+blk_write_time)/(select greatest(io_time, 1) from totals)>=0.01 or calls/(select ncalls from totals)>=0.02 or rows/(select total_rows from totals)>=0.02) | |
), | |
statements_readable as ( | |
select row_number() over (order by s.time_percent desc) as pos, | |
to_char(time_percent, 'FM990.0') || '%' AS time_percent, | |
to_char(io_time_percent, 'FM990.0') || '%' AS io_time_percent, | |
to_char(cpu_time_percent, 'FM990.0') || '%' AS cpu_time_percent, | |
to_char(avg_io_time*100/(coalesce(nullif(avg_time, 0), 1)), 'FM990.0') || '%' AS avg_io_time_percent, | |
total_exec_time, avg_time, avg_cpu_time, avg_io_time, calls, calls_percent, rows, row_percent, | |
database, username, query | |
from statements s where calls is not null | |
) | |
select E'vers. 0.9.5' || | |
' @ PostgreSQL ' || (select setting from pg_settings where name='server_version') || E' \ttracking ' || (select setting from pg_settings where name='pg_stat_statements.track') || ' ' || | |
(select setting from pg_settings where name='pg_stat_statements.max') || ' queries, utilities ' || (select setting from pg_settings where name='pg_stat_statements.track_utility') || | |
', logging ' || (select (case when setting = '0' then 'all' when setting = '-1' then 'none' when setting::int > 1000 then (setting::numeric/1000)::numeric(20, 1) || 's+' else setting || 'ms+' end) from pg_settings where name='log_min_duration_statement') || E' queries\n' || | |
(select coalesce(string_agg('WARNING: database ' || datname || ' must be vacuumed within ' || to_char(2147483647 - age(datfrozenxid), 'FM999,999,999,990') || ' transactions', E'\n' order by age(datfrozenxid) desc) || E'\n', '') | |
from pg_database where (2147483647 - age(datfrozenxid)) < 200000000) as query_text, | |
total_exec_time as total_exec_time_hours, '-' as total_exec_time, '-' as cpu_time, io_time_percent::text || '%' as io, | |
total_queries || ' (unique: ' || unique_queries || E') \t' as count_all, '-' as count_, 0 as avg_time_ms, '-' as avg_io, | |
'-' as user_, | |
(select case when current_database() = 'postgres' then 'all databases' else current_database() || ' DB' end) as db_, | |
'-' as rows_, '-' as rows_percent | |
from totals_readable | |
union all | |
(select query as query_text, total_exec_time as total_exec_time_hours, time_percent as total_exec_time, cpu_time_percent as cpu_time, io_time_percent as io, calls as count_all, | |
calls_percent::text || '%' as count_, avg_time as avg_time_ms, avg_io_time_percent as avg_io, | |
username as user_, database as db_, rows as rows_, row_percent::text || '%' as rows_percent --|| E'\n' | |
from statements_readable | |
--where query like '%token%' | |
order by 2 DESC); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment