Skip to content

Instantly share code, notes, and snippets.

@leemour
Last active January 12, 2022 16:31
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save leemour/d363fdd7d75b57c1a3e1950bd21e141f to your computer and use it in GitHub Desktop.
Save leemour/d363fdd7d75b57c1a3e1950bd21e141f to your computer and use it in GitHub Desktop.
PostgreSQL pg_stat_statements detailed output for query performance analysis and optimization
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;
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