psqlrc

\set QUIET on
\set PROMPT1 '%[%033[30;42m%] psql %[%033[32;40m%] %[%033[39m%]%m %[%033[37m%]%/ %[%033[30;44m%]%[%033[30;44m%] %R %[%033[34;49m%] %[%033[0m%]'
\set PROMPT2 '%[%033[30;42m%] psql %[%033[32;40m%] %[%033[39m%]%m %[%033[37m%]%/ %[%033[30;44m%]%[%033[30;44m%] %R %[%033[34;49m%] %[%033[0m%]'
\set PROMPT3 '%[%033[30;42m%] psql %[%033[32;44m%] %[%033[30;44m%]copy %[%033[34;49m%] %[%033[0m%]'
\set HISTCONTROL ignoreboth
\set HISTSIZE

\set pg_bloat 'SELECT tablename as table_name, ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS table_bloat, CASE WHEN relpages < otta THEN ''0'' ELSE pg_size_pretty((bs*(sml.relpages-otta)::bigint)::bigint) END AS table_waste, iname as index_name, ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS index_bloat, CASE WHEN ipages < iotta THEN ''0'' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS index_waste FROM ( SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs, CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta, COALESCE(c2.relname,''?'') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages, COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta FROM ( SELECT ma,bs,schemaname,tablename, (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM ( SELECT schemaname, tablename, hdr, ma, bs, SUM((1-null_frac)*avg_width) AS datawidth, MAX(null_frac) AS maxfracsum, hdr+( SELECT 1+count(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename) AS nullhdr FROM pg_stats s, ( SELECT (SELECT current_setting(''block_size'')::numeric) AS bs, CASE WHEN substring(v,12,3) IN (''8.0'',''8.1'',''8.2'') THEN 27 ELSE 23 END AS hdr, CASE WHEN v ~ ''mingw32'' THEN 8 ELSE 4 END AS ma FROM (SELECT version() AS v) AS foo) AS constants GROUP BY 1,2,3,4,5) AS foo) AS rs JOIN pg_class cc ON cc.relname = rs.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> ''information_schema'' LEFT JOIN pg_index i ON indrelid = cc.oid LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid) AS sml ORDER BY CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END DESC;'
\set pg_blocking 'select kl.pid as blocking_pid, ka.query as blocking_statement, now() - ka.query_start as blocking_duration, bl.pid as blocked_pid, a.query as blocked_statement, now() - a.query_start as blocked_duration from pg_catalog.pg_locks bl join pg_catalog.pg_stat_activity a on bl.pid = a.pid join pg_catalog.pg_locks kl join pg_catalog.pg_stat_activity ka on kl.pid = ka.pid on ((bl.transactionid = kl.transactionid or (bl.relation = kl.relation and kl.fastpath is false )) and bl.pid != kl.pid) where not bl.granted;'
\set pg_buffers 'SELECT c.relname, pg_size_pretty(count(*) * current_setting(''block_size'')::bigint) as buffered, round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name=''shared_buffers'')::integer,1) AS buffers_percent, round(100.0 * count(*) * current_setting(''block_size'')::bigint / pg_relation_size(c.oid),1) AS percent_of_relation FROM pg_class c INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database()) GROUP BY c.oid, c.relname ORDER BY 3 DESC LIMIT 20;'
\set pg_buffers_ideal 'SELECT pg_size_pretty(count(*) * current_setting(''block_size'')::bigint) as ideal_shared_buffers FROM pg_buffercache b WHERE usagecount >= 3;'
\set pg_cache_hit 'SELECT ''index hit rate'' as name, (sum(idx_blks_hit)) / sum(idx_blks_hit + idx_blks_read) as ratio FROM pg_statio_user_indexes union all SELECT ''cache hit rate'' as name, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables;'
\set pg_index_size 'SELECT relname AS name, pg_size_pretty(sum(relpages::bigint*current_setting(''block_size'')::bigint)::bigint) AS size FROM pg_class WHERE reltype=0 GROUP BY relname ORDER BY sum(relpages) DESC;'
\set pg_index_usage 'SELECT relname, CASE idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * idx_scan / (seq_scan + idx_scan))::text END percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables ORDER BY n_live_tup DESC;'
\set pg_locks 'select pg_stat_activity.pid, pg_class.relname, pg_locks.transactionid, pg_locks.granted, substr(pg_stat_activity.query,1,30) as query_snippet, age(now(),pg_stat_activity.query_start) as "age" from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_stat_activity.query <> ''<insufficient privilege>'' and pg_locks.pid=pg_stat_activity.pid and pg_locks.mode = ''ExclusiveLock'' order by query_start;'
\set pg_long_running_queries 'SELECT pid, now() - pg_stat_activity.query_start AS duration, query AS query FROM pg_stat_activity WHERE pg_stat_activity.query <> ''''::text AND now() - pg_stat_activity.query_start > interval ''5 minutes'' ORDER BY now() - pg_stat_activity.query_start DESC;'
\set pg_ps 'select pid, usename as username, application_name as source, age(now(),query_start) as running_for, query as query from pg_stat_activity where query <> ''<insufficient privilege>'' AND state <> ''idle'' and pid <> pg_backend_pid() order by 3 desc;'
\set pg_seq_scans 'SELECT relname AS name, seq_scan as count FROM pg_stat_user_tables ORDER BY seq_scan DESC;'
\set pg_total_index_size 'SELECT pg_size_pretty(sum(relpages::bigint*current_setting(''block_size'')::bigint)::bigint) AS size FROM pg_class WHERE reltype=0;'
\set pg_unused_indexes 'SELECT schemaname || ''.'' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * current_setting(''block_size'')::bigint ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;'
\set pg_vacuum_stats 'WITH table_opts AS ( SELECT pg_class.oid, relname, nspname, array_to_string(reloptions, '''') AS relopts FROM pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid), vacuum_settings AS ( SELECT oid, relname, nspname, CASE WHEN relopts LIKE ''%autovacuum_vacuum_threshold%'' THEN regexp_replace(relopts, ''.*autovacuum_vacuum_threshold=([0-9.]+).*'', E''\\\\\\1'')::integer ELSE current_setting(''autovacuum_vacuum_threshold'')::integer END AS autovacuum_vacuum_threshold, CASE WHEN relopts LIKE ''%autovacuum_vacuum_scale_factor%'' THEN regexp_replace(relopts, ''.*autovacuum_vacuum_scale_factor=([0-9.]+).*'', E''\\\\\\1'')::real ELSE current_setting(''autovacuum_vacuum_scale_factor'')::real END AS autovacuum_vacuum_scale_factor FROM table_opts) SELECT vacuum_settings.nspname AS schema, vacuum_settings.relname AS table, to_char(psut.last_vacuum, ''YYYY-MM-DD HH24:MI'') AS last_vacuum, to_char(psut.last_autovacuum, ''YYYY-MM-DD HH24:MI'') AS last_autovacuum, to_char(pg_class.reltuples, ''9G999G999G999'') AS rowcount, to_char(psut.n_dead_tup, ''9G999G999G999'') AS dead_rowcount, to_char(autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples), ''9G999G999G999'') AS autovacuum_threshold, CASE WHEN autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup THEN ''yes'' END AS expect_autovacuum FROM pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid INNER JOIN vacuum_settings ON pg_class.oid = vacuum_settings.oid ORDER BY 1, 2;'

\pset border 1
\pset footer on
\pset linestyle unicode
\pset null †

\timing on
\unset QUIET