/
psqlrc
17 lines (14 loc) · 1.72 KB
/
psqlrc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
\set QUIET
\pset linestyle unicode
\pset border 2
\pset null '[null]'
\set AUTOCOMMIT on
--\set PROMPT1 '%[%033[1;31m%]%n%[%033[0m%]@%[%033[1;34m%]%/%[%033[0m%]=>%[%033[1;35m%]%x%[%033[0m%] '
\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] %x%# '
\set PROMPT2 ''
SET application_name TO 'wcox/desktop';
SET bytea_output TO escape;
\timing
\set trashindexes '( 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 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 )'
\unset QUIET