Skip to content
Snippets Groups Projects
Select Git revision
  • master default protected
  • last-xwindows-arch
2 results

psqlrc

psqlrc 10.45 KiB
\set QUIET 1
\timing
\pset null '(null)'
\pset linestyle unicode
\setenv PSQL_PAGER 'pspg --ignore-case --no-sound --quit-if-one-screen --only-for-tables --reprint-on-exit --no-last-row-search --custom-style=warm_dark'
\setenv PAGER less
\set ON_ERROR_STOP on
\set HISTFILE ~/.psql_history-:DBNAME
\set HISTCONTROL ignoredups

-- prompts
-- %[%033[1;37m%]   -- bold lwhite
-- %m               -- hostname
-- %[%033[1;37m%]   -- bold lwhite
-- %x               -- transaction status
-- %[%033[1;31m%]   -- bold lred
-- %n               -- username
-- %[%033[1;37m%]   -- bold lwhite
-- %#               -- superuser
-- %[%033[1;31m%]   -- bold lred
-- %/               -- database name
-- %[%033[1;37m%]   -- bold lwhite
-- %R               -- session/query status
-- %[%033[0m%]      -- reset
\set PROMPT1 '%[%033[1;37m%]%m %[%033[1;37m%]%x%[%033[1;31m%]%n%[%033[1;37m%]%#%[%033[1;31m%]%/%[%033[1;37m%]%R%[%033[0m%] '
\set PROMPT2 '%[%033[1;37m%]%m %[%033[1;37m%]%x%[%033[1;31m%]%n%[%033[1;37m%]%#%[%033[1;31m%]%/%[%033[1;37m%]%R%[%033[0m%] '

-- begin the aliases!
\set conn 'select usename, count(*) from pg_stat_activity group by usename;'
\set dbsize 'SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size;'

-- table info
\set size 'SELECT rel_name, pg_size_pretty(rel_size) AS rel_size, pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size, rel_kind FROM (SELECT rel_name, rel_kind, pg_table_size(rel_name) AS rel_size, pg_indexes_size(rel_name) AS indexes_size, pg_total_relation_size(rel_name) AS total_size FROM (SELECT (\'"\' || table_schema || \'"."\' || table_name || \'"\') AS rel_name, \'table\' AS rel_kind FROM information_schema.tables WHERE table_schema NOT IN (\'information_schema\', \'pg_catalog\') UNION SELECT (\'"\' || schemaname || \'"."\' || matviewname || \'"\'), \'matview\' FROM pg_matviews) AS all_tables ORDER BY total_size DESC) AS pretty_sizes ORDER BY pg_total_relation_size(rel_name) DESC;'
\set 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 owners 'SELECT DISTINCT t.table_name, t.table_type, c.relowner, u.usename FROM information_schema.tables t JOIN pg_catalog.pg_class c ON (t.table_name = c.relname) JOIN pg_catalog.pg_user u ON (c.relowner = u.usesysid) WHERE t.table_schema NOT IN (\'information_schema\', \'pg_catalog\') ORDER BY t.table_name;'
\set last_vacuum 'select pg_class.relname, pg_namespace.nspname, pg_size_pretty(pg_relation_size(pg_class.oid)) as relsize, pg_size_pretty(pg_total_relation_size(pg_class.oid)) as totalsize, case when coalesce(last_vacuum,''1/1/1000'') > coalesce(last_autovacuum,''1/1/1000'') then pg_stat_all_tables.last_vacuum else last_autovacuum end as last_vacuumed, case when coalesce(last_analyze,''1/1/1000'') > coalesce(last_autoanalyze,''1/1/1000'') then pg_stat_all_tables.last_analyze else last_autoanalyze end as last_analyzed from pg_class join pg_namespace on pg_class.relnamespace = pg_namespace.oid join pg_stat_all_tables on (pg_class.relname = pg_stat_all_tables.relname and pg_namespace.nspname = pg_stat_all_tables.schemaname) where pg_namespace.nspname not in (''pg_catalog'', ''information_schema'', ''pg_toast'') order by pg_relation_size(pg_class.oid) desc;'

-- running query info
\set ps 'SELECT pid, usename, datname, now() - query_start as duration, state, SUBSTRING(query, 0, 100) || \' ...\' as query FROM pg_stat_activity WHERE query_start IS NOT NULL ORDER BY duration DESC;'
\set locks 'SELECT pg_stat_activity.pid, pg_class.relname, pg_locks.transactionid, pg_locks.granted, 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\' AND pg_stat_activity.pid <> pg_backend_pid() order by query_start;'
\set blocking 'select bl.pid as blocked_pid, ka.query as blocking_statement, now() - ka.query_start as blocking_duration, kl.pid as blocking_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 and bl.pid != kl.pid where not bl.granted;'
\set slow '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 ''1 seconds'' ORDER BY now() - pg_stat_activity.query_start DESC;'

-- index info
\set index_size 'select i.relname as index_name, t.relname as table_name, i.reltuples AS "entries", pg_size_pretty(i.relpages::bigint*8*1024) AS size from pg_class t inner join pg_index ix on t.oid = ix.indrelid inner join pg_class i on i.oid = ix.indexrelid where t.relkind = \'r\' order by i.relpages DESC;'
\set index_usage 'SELECT schemaname, 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 index_usage_adv 'SELECT * FROM (SELECT stat.relname AS table, stai.indexrelname AS index, CASE stai.idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * stai.idx_scan / (stat.seq_scan + stai.idx_scan))::text || ''%'' END hit_rate, CASE stat.idx_scan WHEN 0 THEN ''Insufficient data'' ELSE (100 * stat.idx_scan / (stat.seq_scan + stat.idx_scan))::text || ''%'' END all_index_hit_rate, ARRAY(SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) FROM generate_subscripts(idx.indkey, 1) AS k ORDER BY k) AS cols, stat.n_live_tup rows_in_table FROM pg_stat_user_indexes AS stai JOIN pg_stat_user_tables AS stat ON stai.relid = stat.relid JOIN pg_index AS idx ON (idx.indexrelid = stai.indexrelid)) AS sub_inner ORDER BY rows_in_table DESC, hit_rate ASC;'
\set 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 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;'
\set missing_indexes 'SELECT relname, seq_scan, idx_scan, round(seq_scan::numeric / (seq_scan + idx_scan), 5) AS seq_percent, pg_size_pretty(pg_relation_size((schemaname || ''.'' || relname)::regclass)) AS rel_size, case when seq_scan > idx_scan THEN ''Missing Index?'' ELSE ''OK'' END AS status FROM pg_stat_all_tables WHERE schemaname NOT IN (''pg_catalog'', ''pg_toast'', ''information_schema'') AND pg_relation_size((schemaname || ''.'' || relname)::regclass) > 80000 ORDER BY seq_percent DESC;'
\set seq_scans 'SELECT relname AS name, seq_scan as count FROM pg_stat_user_tables ORDER BY seq_scan DESC;'
\set 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 index_stats 'SELECT starelid::regclass AS table_name, attname AS column_name, (SELECT string_agg('''',format(E''\\''%s\\'': %s%%\n'', v,ROUND(n::numeric*100, 2))) FROM unnest(stanumbers1,stavalues1::text::text[])nvs(n,v)) pcts FROM pg_statistic JOIN pg_attribute ON attrelid=starelid AND attnum = staattnum JOIN pg_class ON attrelid = pg_class.oid JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid WHERE nspname NOT LIKE ''pg_%'' AND nspname <> ''information_schema'' ORDER BY starelid::regclass, attname \\x\\g\\x'
\set index_skewed 'SELECT starelid::regclass AS table_name, attname AS column_name, stanumbers1[1] as asdf, (SELECT string_agg('''',format(E''\\''%s\\'': %s%%\n'', v,ROUND(n::numeric*100, 2))) FROM unnest(stanumbers1,stavalues1::text::text[])nvs(n,v)) pcts FROM pg_statistic JOIN pg_attribute ON attrelid=starelid AND attnum = staattnum JOIN pg_class ON attrelid = pg_class.oid JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid WHERE nspname NOT LIKE ''pg_%'' AND nspname <> ''information_schema'' AND stanumbers1[1] > .3 ORDER BY starelid::regclass, attname \\x\\g\\x'

-- pg_stat_statements
\set stat_slow 'select userid::regrole::text, calls, min_exec_time, mean_exec_time, max_exec_time, stddev_exec_time, query from pg_stat_statements where max_exec_time > 10000 order by round(calls, -2) desc, round(mean_exec_time::numeric, -2) desc, stddev_exec_time asc limit 20;'

\unset QUIET