workload - pg_stat_statements

queries_by_keyword.sql

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