Skip to content

Instantly share code, notes, and snippets.

@NikolayS
Last active January 6, 2020 12:29
Show Gist options
  • Save NikolayS/21e508cf7cb33657f002077e793f3dd4 to your computer and use it in GitHub Desktop.
Save NikolayS/21e508cf7cb33657f002077e793f3dd4 to your computer and use it in GitHub Desktop.
workload - pg_stat_statements
-- based on pg_stat_statements only
with data as (
select
lower(regexp_replace(query, '^\W*(\w+)\W+.*$', '\1')) word,
count(*) cnt,
sum(calls) calls,
sum(total_time) total_time
from pg_stat_statements
--where not query ~* '^\W*set\W+' -- uncomment this to exclude `SET ...`
group by 1
)
select
word,
calls,
round(100 * calls::numeric / (sum(calls) over())::numeric, 2) as "Calls, %",
round(total_time::numeric, 2) total_time,
round(100 * total_time::numeric / (sum(total_time) over())::numeric, 2) as "Time, %"
from data order by total_time desc;
-- pg_stat_statements + pg_stat_kcache
with data as (
select
lower(regexp_replace(s.query, '^\W*(\w+)\W+.*$', '\1')) word,
count(*) cnt,
sum(calls) calls,
sum(s.total_time) total_time,
1000 * sum(k.user_time) user_time, -- sec -> ms
1000 * sum(k.system_time) system_time, -- sec -> ms
sum(reads_blks) reads_blks,
sum(writes_blks) writes_blks
from pg_stat_statements s
left join pg_stat_kcache_detail k using (query) -- or `using (queryid)`, depending on version
--where not s.query ~* '^\W*set\W+' -- uncomment this to exclude `SET ...`
group by 1
)
select
word,
calls,
round(100 * calls::numeric / (sum(calls) over())::numeric, 2) as "Calls, %",
round(total_time::numeric, 2) total_time,
round(100 * total_time::numeric / (sum(total_time) over())::numeric, 2) as "Time, %",
reads_blks,
round(100 * reads_blks::numeric / (sum(reads_blks) over())::numeric, 2) as "Read blocks, %",
writes_blks,
round(100 * writes_blks::numeric / (sum(writes_blks) over())::numeric, 2) as "Written blocks, %"
from data order by total_time desc;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment