-

\set QUIET ON
\set PROMPT1 '%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%[%033[0m%]%R%# '
\set PAGER OFF
\pset format wrapped
\set show_slow_queries
\pset linestyle unicode
\x auto
\pset null 'NULL'
\set HISTFILE ~/.psql_history- :HOST - :DBNAME
\set HISTSIZE 2000
\timing
\encoding unicode
\set QUIET OFF

\set menu '\\i ~/.psqlrc'
\set clear '\\! clear;'

-- https://github.com/AF83/dotfiles/blob/master/.psqlrc
\set rm_index '( select s.schemaname as sch, s.relname as rel, s.indexrelname as idx, s.idx_scan as scans, pg_size_pretty(pg_relation_size(s.relid)) as ts, pg_size_pretty(pg_relation_size(s.indexrelid)) as "is" from pg_stat_user_indexes s join pg_index i on i.indexrelid=s.indexrelid left join pg_constraint c on i.indrelid=c.conrelid and array_to_string(i.indkey, '' '') = array_to_string(c.conkey, '' '') where i.indisunique is false and pg_relation_size(s.relid) > 1000000 and s.idx_scan < 100000 and c.confrelid is null order by s.idx_scan asc, pg_relation_size(s.relid) desc );'
\set df 'SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size;'
\set du 'SELECT nspname || ''.'' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (''pg_catalog'', ''information_schema'') ORDER BY pg_relation_size(C.oid) DESC LIMIT 20;'
\set top '(SELECT datname, pid, now() - query_start AS runtime, case when waiting then \'WAIT\' else \'\' end AS wait, query FROM pg_stat_activity WHERE state <> \'idle\' AND pid <> pg_backend_pid() ORDER BY runtime DESC);'
\set ps 'select pid, application_name as source, age(now(),query_start) as running_for, waiting, query as query from pg_stat_activity where query <> ''<insufficient privilege>'' AND state <> ''idle'' and pid <> pg_backend_pid() order by 3 desc;'
\set ls 'SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN (''information_schema'',''pg_catalog'');'
\set missing_ids 'select * from( WITH seq_max AS ( SELECT max(id) FROM :table), seq_min AS ( SELECT min(id) FROM :table) SELECT * FROM generate_series((SELECT min FROM seq_min),(SELECT max FROM seq_max)) EXCEPT SELECT id FROM :table) as T order by generate_series'
\set count_missing_ids 'select count(*) from (:missing_ids) as R'

\set seq_scans 'SELECT relname AS name, seq_scan as count FROM pg_stat_user_tables ORDER BY seq_scan DESC;'
\set index_usage '(select s.relname, s.seq_tup_read + s.idx_tup_fetch usage, (s.idx_tup_fetch/s.seq_tup_read::float)::decimal(18,4) index_ratio, s.seq_tup_read, s.idx_tup_fetch, s.seq_scan, s.n_live_tup, ((io.heap_blks_hit + io.idx_blks_hit)/((io.heap_blks_read + io.idx_blks_read)::float))::decimal(18,4) cache_hit_ratio FROM pg_stat_user_tables s INNER JOIN pg_statio_user_tables io ON s.relid = io.relid WHERE s.seq_tup_read + s.idx_tup_fetch > 0 AND s.n_live_tup > 5000 ORDER BY usage desc, cache_hit_ratio desc, index_ratio asc, s.seq_scan desc, s.n_live_tup 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 missingindexes '( select src_table, dst_table, fk_name, pg_size_pretty(s_size) as s_size, pg_size_pretty(d_size) as d_size, d from ( select distinct on (1,2,3,4,5) textin(regclassout(c.conrelid)) as src_table, textin(regclassout(c.confrelid)) as dst_table, c.conname as fk_name, pg_relation_size(c.conrelid) as s_size, pg_relation_size(c.confrelid) as d_size, array_upper(di.indkey::int[], 1) + 1 - array_upper(c.conkey::int[], 1) as d from pg_constraint c left join pg_index di on di.indrelid = c.conrelid and array_to_string(di.indkey, '' '') ~ (''^'' || array_to_string(c.conkey, '' '') || ''( |$)'') join pg_stat_user_tables st on st.relid = c.conrelid where c.contype = ''f'' order by 1,2,3,4,5,6 asc) mfk where mfk.d is distinct from 0 and mfk.s_size > 1000000 order by mfk.s_size desc, mfk.d desc );'
\set locks  'SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement 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 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;'

\set subnets 'select network(inet(ip_address||''/24'')) as mask, count(*) from ( select ip_address from trackings where state = ''tracked'' group by ip_address) as g group by mask having count(*) > 10; '
\set site_fraud 'select site_id,count(*) from trackings where campaign_id = :campaign_id and state = ''tracked'' and exists ( select * from blacklisted_ip_addresses where inet(trackings.ip_address) between start_ip and end_ip) group by site_id order by count(*) desc; '
\set blacklisted_ips 'select ip_address from trackings where campaign_id = :campaign_id and state = ''tracked'' and exists ( select * from blacklisted_ip_addresses where inet(trackings.ip_address) between start_ip and end_ip) ; '
\set traffic 'select ip_address,site_id,application_user_id from trackings where campaign_id in (:campaign_id) and state = ''tracked'');'
\set blacklisted_sites 'select A.site_id, blacklisted, tracked, 100*blacklisted/tracked as p from ( select site_id, count(*) as blacklisted from trackings where state = ''rejected'' and reason =''blacklistedip'' group by site_id order by count(id) desc) as A inner join ( select site_id, count(*) as tracked from trackings where state = ''tracked'' group by site_id order by count(id) desc) as B on A.site_id = B.site_id inner join ( select site_id, count(*) as rejected from trackings where state = ''rejected'' group by site_id order by count(id) desc) as C on A.site_id = C.site_id where blacklisted > 100 order by blacklisted desc;'
\set blacklisted_campaigns 'select A.campaign_id, blacklisted, tracked, 100*blacklisted/tracked as p from ( select campaign_id, count(*) as blacklisted from trackings where state = ''rejected'' and reason =''blacklistedip'' group by campaign_id order by count(id) desc) as A inner join ( select campaign_id, count(*) as tracked from trackings where state = ''tracked'' group by campaign_id order by count(id) desc) as B on A.campaign_id = B.campaign_id inner join ( select campaign_id, count(*) as rejected from trackings where state = ''rejected'' group by campaign_id order by count(id) desc) as C on A.campaign_id = C.campaign_id where blacklisted > 100 order by blacklisted desc;'