<% \pset footer on
\pset tableattr 'id="tabInfo" class="thidden"'
SELECT c.relname || CASE WHEN inh.inhrelid IS NOT NULL THEN ' (part)' WHEN c.relkind != 'r' THEN ' ('||c.relkind||')' ELSE '' END "Name" ,
concat(r.relid,',',r.n_tup_ins,',',r.n_tup_upd,',',r.n_tup_del,',',r.n_tup_hot_upd,',',isum.totind,',',isum.ind0scan,',',isum.pk,',',isum.uk,',',inhp.relname,',',inhp.relkind,',',c.relfilenode,',',c.reltablespace,',',c.reloptions),r.relnamespace "NS", CASE WHEN r.blks > 999 AND r.blks > tb.est_pages THEN (r.blks-tb.est_pages)*100/r.blks ELSE NULL END "Bloat%",
r.n_live_tup "Live",r.n_dead_tup "Dead", CASE WHEN r.n_live_tup <> 0 THEN ROUND((r.n_dead_tup::real/r.n_live_tup::real)::numeric,1) END "D/L",
r.rel_size "Rel size",r.tot_tab_size "Tot.Tab size",r.tab_ind_size "Tab+Ind size",r.rel_age "Rel. Age",to_char(r.last_vac,'YYYY-MM-DD HH24:MI:SS') "Last vacuum",to_char(r.last_anlyze,'YYYY-MM-DD HH24:MI:SS') "Last analyze",r.vac_nos "Vaccs",
ct.relname "Toast name",rt.tab_ind_size "Toast + Ind" ,rt.rel_age "Toast Age",GREATEST(r.rel_age,rt.rel_age) "Max age",
c.blocks_fetched "Fetch",c.blocks_hit*100/nullif(c.blocks_fetched,0) "C.Hit%",to_char(r.lastuse,'YYYY-MM-DD HH24:MI:SS') "Last Use"
FROM pg_get_rel r
JOIN pg_get_class c ON r.relid = c.reloid AND c.relkind NOT IN ('t','p')
LEFT JOIN pg_get_toast t ON r.relid = t.relid
LEFT JOIN pg_get_class ct ON t.toastid = ct.reloid
LEFT JOIN pg_get_rel rt ON rt.relid = t.toastid
LEFT JOIN pg_tab_bloat tb ON r.relid = tb.table_oid
LEFT JOIN pg_get_inherits inh ON r.relid = inh.inhrelid
LEFT JOIN pg_get_class inhp ON inh.inhparent = inhp.reloid
LEFT JOIN (SELECT count(indexrelid) totind,count(indexrelid)FILTER( WHERE numscans=0 ) ind0scan, count(indexrelid) FILTER (WHERE indisprimary) pk,
count(indexrelid) FILTER (WHERE indisunique) uk, indrelid FROM pg_get_index GROUP BY indrelid ) AS isum ON isum.indrelid = r.relid
ORDER BY r.tab_ind_size DESC LIMIT 10000;
\pset tableattr 'id="tabPart" class="thidden"'
WITH ptables AS ( SELECT p.relname , p.relkind, i.inhparent, i.inhrelid
FROM pg_get_class p LEFT JOIN pg_get_inherits i ON i.inhparent = p.reloid
WHERE p.relkind in ('p','r'))
SELECT p.relname "Partitioned Table", CONCAT(any_value(c.relname) FILTER (WHERE dpart = 't'),',',any_value(r.n_live_tup) FILTER (WHERE dpart='t')) "Default Partition Name, Count",
'Native-Declarative' "Partitioning Type", count(r.relid) "Partitions", sum(r.tot_tab_size) "tot_tab_size" , sum(r.tab_ind_size) "tab_ind_size",
round(max(c.blocks_fetched)/sum(NULLIF(c.blocks_fetched,0))*100 ,1) "Fetch Prune %"
FROM ptables p LEFT JOIN pg_get_rel r ON p.inhrelid = r.relid
LEFT JOIN pg_get_class c ON p.inhrelid = c.reloid
WHERE p.relkind = 'p' GROUP BY 1
UNION ALL
SELECT p.relname ,',', 'Inheritance' , count(r.relid) "Partitions", sum(r.tot_tab_size) ,
sum(r.tab_ind_size), max(c.blocks_fetched)/sum(NULLIF(c.blocks_fetched,0))*100
FROM ptables p JOIN pg_get_rel r ON p.inhrelid = r.relid
JOIN pg_get_class c ON p.inhrelid = c.reloid
WHERE p.relkind = 'r' GROUP BY 1;
\pset tableattr 'id="IndInfo"'
SELECT n.nsname "Schema",ct.relname AS "Table", ci.relname as "Index",indisunique as "UK?",indisprimary as "PK?",numscans as "Scans",size,ci.blocks_fetched "Fetch",ci.blocks_hit*100/nullif(ci.blocks_fetched,0) "C.Hit%", to_char(i.lastuse,'YYYY-MM-DD HH24:MI:SS') "Last Use"
FROM pg_get_index i
JOIN pg_get_class ct on i.indrelid = ct.reloid and ct.relkind != 't'
JOIN pg_get_class ci ON i.indexrelid = ci.reloid
LEFT JOIN pg_get_ns n ON n.nsoid = ci.relnamespace
ORDER BY size DESC LIMIT 10000;
\pset tableattr 'id="params"'
WITH dset AS (
SELECT string_agg(setting,chr(10)) setting,a.name FROM
(SELECT btrim(CASE WHEN rolname IS NULL THEN '' ELSE 'User: '|| rolname ||' , ' END || CASE WHEN datname IS NULL THEN '' ELSE 'DB: '|| datname END ,' ,') || ' ==> ' ||setting AS setting
,split_part(setting,'=',1) AS name
FROM pg_get_db_role_confs drc
LEFT JOIN LATERAL unnest(config) AS setting ON TRUE
LEFT JOIN pg_get_db db ON drc.db = db.datid
LEFT JOIN pg_get_roles rol ON rol.oid = drc.setrole
ORDER BY 1,2 NULLS LAST
) AS a GROUP BY 2 ),
fset AS (SELECT coalesce(s.name,f.name) AS name
,s.setting,s.unit,s.source
,string_agg(f.sourcefile ||' - '|| f.setting || CASE WHEN f.applied = true THEN ' (applicable)' ELSE '' END ,chr(10)) FILTER (WHERE s.source != f.sourcefile OR s.source IS NULL ) AS loc
FROM pg_get_confs s FULL OUTER JOIN pg_get_file_confs f ON lower(s.name) = lower(f.name)
GROUP BY 1,2,3,4 ORDER BY 1)
SELECT fset.name "Name",fset.setting "Setting",fset.unit "Unit",fset.source "Current Source",
CASE WHEN dset.setting IS NULL THEN '' ELSE dset.setting ||chr(10) END || CASE WHEN fset.loc IS NULL THEN '' ELSE fset.loc END AS "Other Locations & Values"
FROM fset LEFT JOIN dset ON fset.name = dset.name;
\pset footer off
\pset tableattr 'id="tblextn"'
SELECT ext.oid,extname "Extension",rolname "Owner",nsname "Schema", extrelocatable "Relocatable?",extversion "Version"
FROM pg_get_extension ext LEFT JOIN pg_get_roles ON extowner=pg_get_roles.oid
LEFT JOIN pg_get_ns ON extnamespace = nsoid;
\pset tableattr 'id="tblhba"'
WITH rules AS (SELECT * FROM pg_get_hba_rules WHERE mask IS NOT NULL AND addr NOT IN ('all','samehost','samenet')),
cidr AS (SELECT seq, COALESCE(sum((length(mask) - length(replace(mask, ip4mask.col1, ''))) / length(ip4mask.col1) * ip4mask.col2) ,
sum((length(mask) - length(replace(mask, ip6mask.col1, ''))) / length(ip6mask.col1) * ip6mask.col2)) cidr_mask
FROM rules
LEFT JOIN (VALUES ('255',8),('254',7),('252',6),('248',5),('240',4),('224',3),('192',2),('128',1)) AS ip4mask (col1,col2)
ON family(addr::inet) = 4
LEFT JOIN (VALUES ('8',1),('c',2),('e',3),('f',4)) AS ip6mask (col1,col2) ON family(addr::inet) = 6
GROUP BY 1),
rule_data AS (SELECT hba.seq ,typ ,db ,usr ,addr , cidr_mask , mask,
CASE WHEN addr IN ('all','samehost','samenet') OR ( mask IS NULL AND addr IS NOT NULL) THEN 'IPv4,IPv6'
ELSE 'IPv'||family(addr::inet)
END "IP" ,method , err, (addr||'/'||cidr_mask)::inet network_block
FROM pg_get_hba_rules hba LEFT JOIN cidr ON cidr.seq = hba.seq)
SELECT victim.seq "Line",victim.typ "Type",victim.db "Database",victim.usr "User",victim.addr "Address", victim.cidr_mask "CIDR Mask",victim.mask "DDN/Binary Mask"
,victim."IP" "IP Ver.",victim.Method,victim.err,victim.network_block "Network Block", string_agg(shadower.seq::text,',') "In shadow of"
FROM rule_data AS victim
LEFT JOIN rule_data AS shadower
ON shadower.seq < victim.seq
AND (
(victim.typ = 'local' AND shadower.typ = 'local')
OR (victim.typ = 'host' AND shadower.typ = 'host')
OR (victim.typ = 'hostssl' AND shadower.typ IN ('host', 'hostssl'))
OR (victim.typ = 'hostnossl' AND shadower.typ IN ('host', 'hostnossl'))
)
AND ( victim.typ = 'local'
OR ( victim.network_block IS NOT NULL AND shadower.network_block IS NOT NULL AND shadower.network_block >>= victim.network_block )
OR shadower.addr = 'all'
)
AND (('replication' = ANY(victim.db) AND 'replication' = ANY(shadower.db) AND victim.db <@ shadower.db) OR
(NOT ('replication' = ANY(victim.db)) AND ( shadower.db = '{all}' OR victim.db <@ shadower.db ) ))
AND ( shadower.usr = '{all}' OR victim.usr <@ shadower.usr)
GROUP BY 1,2,3,4,5,6,7,8,9,10,11
ORDER BY 1;
\pset tableattr 'id="tblcs" class="lineblk thidden"'
WITH db_role AS (SELECT
pg_get_activity.datid,rolname,count(*) FILTER (WHERE state='active') as active,
count(*) FILTER (WHERE state='idle in transaction') as idle_in_transaction,
count(*) FILTER (WHERE state='idle') as idle,
count(*) as totalcons,
count (*) FILTER (WHERE ssl = true) as sslcons,
count (*) FILTER (WHERE ssl = false) as nonsslcons
FROM pg_get_activity
LEFT JOIN pg_get_roles on usesysid=pg_get_roles.oid
LEFT JOIN pg_get_db on pg_get_activity.datid = pg_get_db.datid
GROUP BY 1,2
ORDER BY 1,2),
db AS (SELECT datid,sum(active) "Active",sum(idle_in_transaction) "IdleInTrans",sum(idle) "Idle",sum(totalcons) "Total",sum(sslcons) "SSL",sum(nonsslcons) "NonSSL"
FROM db_role GROUP BY 1)
SELECT pg_get_db.datname "Database",
(SELECT json_agg(ROW(rolname,active,idle_in_transaction,idle,totalcons,sslcons,nonsslcons)) FROM db_role WHERE db_role.datid = pg_get_db.datid),
"Active","IdleInTrans","Idle","Total","SSL","NonSSL"
FROM pg_get_db LEFT JOIN db ON pg_get_db.datid = db.datid;
\pset tableattr 'id="tblusr" class="thidden"'
WITH rol_db AS (SELECT
rolname,datname,count(*) FILTER (WHERE state='active') as active,
count(*) FILTER (WHERE state='idle in transaction') as idle_in_transaction,
count(*) FILTER (WHERE state='idle') as idle,
count(*) as totalcons,
count (*) FILTER (WHERE ssl = true) as sslcons,
count (*) FILTER (WHERE ssl = false) as nonsslcons
FROM pg_get_activity
join pg_get_roles on usesysid=pg_get_roles.oid
join pg_get_db on pg_get_activity.datid = pg_get_db.datid
GROUP BY 1,2
ORDER BY 1,2),
rol AS (SELECT rolname,sum(active) "Active",sum(idle_in_transaction) "IdleInTrans",sum(idle) "Idle",sum(totalcons) "Total",sum(sslcons) "SSL",sum(nonsslcons) "NonSSL"
FROM rol_db GROUP BY 1)
SELECT pg_get_roles.rolname "User",
(SELECT json_agg(ROW(datname,active,idle_in_transaction,idle,totalcons,sslcons,nonsslcons)) FROM rol_db WHERE rol_db.rolname = pg_get_roles.rolname),
rolsuper "Super?",rolreplication "Repl?", CASE WHEN rolconnlimit > -1 THEN rolconnlimit ELSE NULL END "Limit",
CASE enc_method WHEN 'm' THEN 'MD5' WHEN 'S' THEN 'SCRAM' END "Enc",
"Active","IdleInTrans","Idle","Total","SSL","NonSSL"
FROM pg_get_roles LEFT JOIN rol ON pg_get_roles.rolname = rol.rolname;
\pset tableattr 'id="tableConten" name="waits" style="clear: left"'
\C 'WaitEvents'
SELECT COALESCE(wait_event,'CPU') "Event", count(*)::text "Event Count" FROM pg_pid_wait
WHERE wait_event IS NULL OR wait_event NOT IN ('ArchiverMain','AutoVacuumMain','BgWriterHibernate','BgWriterMain','CheckpointerMain','LogicalApplyMain','LogicalLauncherMain','RecoveryWalStream','SysLoggerMain','WalReceiverMain','WalSenderMain',
'WalWriterMain','CheckpointWriteDelay','PgSleep','VacuumDelay','IoWorkerMain','AutovacuumMain','BgwriterHibernate','BgwriterMain')
GROUP BY 1 ORDER BY count(*) DESC;
\pset tableattr 'id="tblsess" class="thidden"'
\C 'Sessions'
SELECT * FROM (
WITH w AS (SELECT pid, string_agg( wait_event ||': '|| cnt*100::float/2000 ||'%',', ') waits, sum(cnt) pidwcnt, max(max) itr_max, min(min) itr_min FROM
(SELECT pid,COALESCE(wait_event,'CPU') wait_event,count(*) cnt, max(itr),min(itr) FROM pg_pid_wait GROUP BY 1,2 ORDER BY cnt DESC) pw GROUP BY 1),
g AS (SELECT max(ts) ts,max(mx_xid) mx_xid FROM
(SELECT MAX(state_change) as ts,MAX(GREATEST(backend_xid::text::bigint,backend_xmin::text::bigint)) mx_xid FROM pg_get_activity
UNION
SELECT NULL, pg_snapshot_xmax(snapshot)::xid::text::bigint mx_xid FROM pg_gather) a),
wrk AS (select leader_pid, count(*) from pg_get_activity where leader_pid is not null group by 1),
itr AS (SELECT max(itr_max) gitr_max FROM w)
SELECT a.pid,to_jsonb(ROW(d.datname,application_name,client_hostname,sslversion,wrk.count)), a.state,r.rolname "User"
, CASE WHEN a.leader_pid IS NULL THEN host(client_addr) ELSE 'Worker of ' || a.leader_pid END "client"
, CASE query WHEN '' THEN '**'||backend_type||' process**' ELSE query END "Last statement"
, g.ts - backend_start "Connection Since", g.ts - xact_start "Transaction Since", g.mx_xid - backend_xmin::text::bigint "xmin age",
g.ts - query_start "Statement since",g.ts - state_change "State since", w.waits ||
CASE WHEN (itr_max - itr_min)::float/itr.gitr_max*2000 - pidwcnt > 0 THEN
', Net/Delay*: ' || round(((itr_max - itr_min)::float/itr.gitr_max*2000 - pidwcnt)::numeric*100/2000,2) || '%'
ELSE '' END waits
FROM pg_get_activity a
LEFT JOIN w ON a.pid = w.pid
LEFT JOIN itr ON true
LEFT JOIN g ON true
LEFT JOIN wrk ON wrk.leader_pid = a.pid
LEFT JOIN pg_get_roles r ON a.usesysid = r.oid
LEFT JOIN pg_get_db d on a.datid = d.datid
ORDER BY "xmin age" DESC NULLS LAST) AS sess
WHERE waits IS NOT NULL OR state != 'idle';
\pset tableattr 'id="tblstmnt"'
\C 'Top Statements'
SELECT DENSE_RANK() OVER (ORDER BY ranksum) "Rank", "Statement",time_pct "DB.time%", calls "Execs",total_time::bigint/calls "Avg.ExecTime","Avg.Reads","C.Hit%"
,"Avg.Dirty","Avg.Write","Avg.Temp(r)","Avg.Temp(w)" FROM
(select query "Statement",total_time::bigint
, round((100*total_time/sum(total_time) OVER ())::numeric,2) AS time_pct, DENSE_RANK() OVER (ORDER BY total_time DESC) AS tottrank,calls
,total_time::bigint/calls, DENSE_RANK() OVER (ORDER BY total_time::bigint/calls DESC) as avgtrank
,DENSE_RANK() OVER (ORDER BY total_time DESC)+DENSE_RANK() OVER (ORDER BY total_time::bigint/calls DESC) ranksum
,shared_blks_read/calls "Avg.Reads",
shared_blks_dirtied/calls "Avg.Dirty",
shared_blks_written/calls "Avg.Write",
temp_blks_read/calls "Avg.Temp(r)",
temp_blks_written/calls "Avg.Temp(w)"
,100 * shared_blks_hit / nullif((shared_blks_read + shared_blks_hit),0) as "C.Hit%"
from pg_get_statements) AS stmnts
WHERE tottrank < 15 OR avgtrank < 15 ;
\pset tableattr 'id="tblreplstat"'
WITH M AS (SELECT GREATEST((SELECT(current_wal) FROM pg_gather),(SELECT MAX(sent_lsn) FROM pg_replication_stat))),
g AS (SELECT max(mx_xid) mx_xid FROM
(SELECT MAX(GREATEST(backend_xid::text::bigint,backend_xmin::text::bigint)) mx_xid FROM pg_get_activity
UNION
SELECT pg_snapshot_xmax(snapshot)::xid::text::bigint mx_xid FROM pg_gather) a)
SELECT usename AS "Replication User",client_addr AS "Replica Address",pid,state,
pg_wal_lsn_diff(M.greatest, sent_lsn) "Transmission Lag (Bytes)",pg_wal_lsn_diff(sent_lsn,write_lsn) "Replica Write lag(Bytes)",
pg_wal_lsn_diff(write_lsn,flush_lsn) "Replica Flush lag(Bytes)",pg_wal_lsn_diff(write_lsn,replay_lsn) "Replay at Replica lag(Bytes)",
slot_name "Slot",plugin,slot_type "Type",datname "DB name",temporary,active,GREATEST(g.mx_xid-old_xmin::text::bigint,0) as "xmin age",
GREATEST(g.mx_xid-catalog_xmin::text::bigint,0) as "catalog xmin age", GREATEST(pg_wal_lsn_diff(M.greatest,restart_lsn),0) as "Restart LSN lag(Bytes)",
GREATEST(pg_wal_lsn_diff(M.greatest,confirmed_flush_lsn),0) as "Confirmed LSN lag(Bytes)"
FROM pg_replication_stat JOIN M ON TRUE
FULL OUTER JOIN pg_get_slots s ON pid = active_pid
LEFT JOIN g ON TRUE
LEFT JOIN pg_get_db ON s.datoid = datid;
\pset tableattr 'id="tblchkpnt"'
SELECT round(checkpoints_req*100/tot_cp,1) "Forced Checkpoint %" ,
round(min_since_reset/tot_cp,2) "avg mins between CP",
round(checkpoint_write_time::numeric/(tot_cp*1000),4) "Avg CP write time (s)",
round(checkpoint_sync_time::numeric/(tot_cp*1000),4) "Avg CP sync time (s)",
round(total_buffers::numeric*8192/(1024*1024),2) "Tot MB Written",
round((buffers_checkpoint::numeric/tot_cp)*8192/(1024*1024),4) "MB per CP",
round(buffers_checkpoint::numeric*8192/(min_since_reset*60*1024*1024),4) "Checkpoint MBps",
round(buffers_clean::numeric*8192/(min_since_reset*60*1024*1024),4) "Bgwriter MBps",
round(bg.buffers_backend::numeric*8192/(min_since_reset*60*1024*1024),4) "Backend MBps",
round(total_buffers::numeric*8192/(min_since_reset*60*1024*1024),4) "Total MBps",
round(buffers_alloc::numeric/total_buffers,3) "New buffers ratio",
round(100.0*buffers_checkpoint/total_buffers,1) "Clean by checkpoints (%)",
round(100.0*buffers_clean/total_buffers,1) "Clean by bgwriter (%)",
round(100.0*bg.buffers_backend/total_buffers,1) "Clean by backends (%)",
round(100.0*maxwritten_clean/(min_since_reset*60000 / delay.setting::numeric),2) "Bgwriter halts (%) per runs",
coalesce(round(100.0*maxwritten_clean/(nullif(buffers_clean,0)/ lru.setting::numeric),2),0) "Bgwriter halt (%) due to LRU hit",
round(min_since_reset/(60*24),1) "Reset days"
FROM pg_get_bgwriter
CROSS JOIN
(WITH client AS (SELECT sum(evictions) buffers_backend FROM pg_get_io WHERE btype='c')
SELECT
NULLIF(round(extract('epoch' from (select collect_ts from pg_gather) - stats_reset)/60)::numeric,0) min_since_reset,
GREATEST(buffers_checkpoint + buffers_clean + COALESCE(client.buffers_backend,pg_get_bgwriter.buffers_backend),1) total_buffers,
NULLIF(checkpoints_timed+checkpoints_req,0) tot_cp,
COALESCE(client.buffers_backend,pg_get_bgwriter.buffers_backend) buffers_backend
FROM pg_get_bgwriter,client) AS bg
LEFT JOIN pg_get_confs delay ON delay.name = 'bgwriter_delay'
LEFT JOIN pg_get_confs lru ON lru.name = 'bgwriter_lru_maxpages';
\pset tableattr 'id="tbliostat"'
\if :pg18
WITH cts AS ( SELECT COALESCE(collect_ts, (SELECT max(state_change) FROM pg_stat_activity)) AS c_ts FROM pg_gather),
rst AS ( SELECT max(stats_reset) AS max_reset FROM pg_get_io),
d AS (SELECT cts.c_ts, rst.max_reset,cts.c_ts - rst.max_reset, EXTRACT(EPOCH FROM (cts.c_ts - COALESCE(rst.max_reset,:'reset_ts')))/86400 AS dys FROM cts, rst),
blk AS (SELECT COALESCE((SELECT setting::INT FROM pg_get_confs WHERE name = 'block_size'),8192) AS blksize)
SELECT
CASE btype WHEN 'a' THEN 'Autovacuum' WHEN 'C' THEN 'Client Backend' WHEN 'G' THEN 'BG writer' WHEN 'b' THEN 'Background Parallel workers' WHEN 'c' THEN 'Client Backends' WHEN 'i' THEN 'I/O Worker'
WHEN 'k' THEN 'Checkpointer' WHEN 'w' THEN 'WAL Sender' WHEN 'W' THEN 'WAL Writer' WHEN 'r' THEN 'WAL Receiver' WHEN 'l' THEN 'Slot Sync' ELSE btype END As "Backend",
(sum(reads)/any_value(d.dys))::bigint "Reads/day",(sum(read_bytes)/any_value(d.dys))::bigint "Read Bytes/day",(sum(writes)/any_value(d.dys))::bigint "Writes/day",(sum(write_bytes)/any_value(d.dys))::bigint "Write Bytes/day",(sum(writebacks)*any_value(blksize)/any_value(d.dys))::bigint "Writebacks/day",(sum(extends)/any_value(d.dys))::bigint "Extends/day",(sum(extend_bytes)/any_value(d.dys))::bigint "Extend Bytes/day",
(sum(hits)/any_value(d.dys))::bigint "Hits/day",(sum(evictions)/any_value(d.dys))::bigint "Evictions/day", (sum(reuses)/any_value(d.dys))::bigint "Reuse/day", (sum(fsyncs)/any_value(d.dys))::bigint "FSyncs/day"
FROM pg_get_io,d,blk
-- WHERE reads > 0 OR writes > 0 OR writebacks > 0 or extends > 0 OR hits > 0 OR evictions > 0 OR reuses > 0 OR fsyncs > 0
GROUP BY 1;
\else
WITH cts AS ( SELECT COALESCE(collect_ts, (SELECT max(state_change) FROM pg_stat_activity)) AS c_ts FROM pg_gather),
rst AS ( SELECT max(stats_reset) AS max_reset FROM pg_get_io),
d AS (SELECT cts.c_ts, rst.max_reset,cts.c_ts - rst.max_reset, EXTRACT(EPOCH FROM (cts.c_ts - COALESCE(rst.max_reset,:'reset_ts')))/86400 AS dys FROM cts, rst),
blk AS (SELECT COALESCE((SELECT setting::INT FROM pg_get_confs WHERE name = 'block_size'),8192) AS blksize)
SELECT
CASE btype WHEN 'a' THEN 'Autovacuum' WHEN 'C' THEN 'Client Backend' WHEN 'G' THEN 'BG writer' WHEN 'b' THEN 'background Parallel workers' WHEN 'c' THEN 'Client Backends'
WHEN 'k' THEN 'Checkpointer' WHEN 'w' THEN 'WALSender' ELSE btype END As "Backend",
(sum(reads)*any_value(blksize)/any_value(d.dys))::bigint "Read bytes/day",(sum(writes)*any_value(blksize)/any_value(d.dys))::bigint "Write bytes/day",(sum(writebacks)*any_value(blksize)/any_value(d.dys))::bigint "Writeback bytes/day", (sum(extends)*any_value(blksize)/any_value(d.dys))::bigint "Extend bytes/day",
(sum(hits)*any_value(blksize)/any_value(d.dys))::bigint "Avg. Cache Hit bytes/day",(sum(evictions)*any_value(blksize)/any_value(d.dys))::bigint "Evictions bytes/day", (sum(reuses)/any_value(d.dys))::bigint "Avg. Reuse", (sum(fsyncs)/any_value(d.dys))::bigint "Avg. FSyncs"
FROM pg_get_io, blk, d
WHERE reads > 0 OR writes > 0 OR writebacks > 0 or extends > 0 OR hits > 0 OR evictions > 0 OR reuses > 0 OR fsyncs > 0
GROUP BY 1;
\endif
%>
Findings: