.psqlrc

-- Official docs: http://www.postgresql.org/docs/9.3/static/app-psql.html
-- Unofficial docs: http://robots.thoughtbot.com/improving-the-command-line-postgres-experience

-- Don't display the "helpful" message on startup.
\set QUIET 1
\pset null '[NULL]'

-- http://www.postgresql.org/docs/9.3/static/app-psql.html#APP-PSQL-PROMPTING
\set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '
-- PROMPT2 is printed when the prompt expects more input, like when you type
-- SELECT * FROM<enter>. %R shows what type of input it expects.
\set PROMPT2 '[more] %R > '

-- Show how long each query takes to execute
\timing

-- Use best available output format
\x auto
\set VERBOSITY verbose
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper
\unset QUIET

-- psql can't check for a file's existence, so we'll provide an empty local
-- file that users can override with their custom dotfiles. To set your own
-- personal settings, place your own file in ~/.psqlrc.local
-- \i ~/.psqlrc.local

-- Following has been picked up from https://github.com/mogren/dotfiles/blob/master/.psqlrc
-- NOTE: to use variables use it like `:sc users;`, `:trashindexes`

-- Large indexes that are rarely used
\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) > 100000 AND s.idx_scan < 10000 AND c.confrelid is null ORDER BY s.idx_scan ASC, pg_relation_size(s.relid) desc)';

-- Foreign keys that should have a corresponding index
\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 > 100000 ORDER BY mfk.s_size desc, mfk.d desc )';

-- Size of tables on disk, with and without indexes
\set bigtables '(SELECT table_schema, table_name, pg_size_pretty(size) as size, pg_size_pretty(total_size) as total_size, pg_size_pretty(total_size - size) as index_sizes FROM (SELECT table_schema, table_name, pg_relation_size(quote_ident(table_schema) || \'.\' || quote_ident(table_name)) as size, pg_total_relation_size( quote_ident(table_schema) || \'.\' || quote_ident(table_name)) as total_size FROM information_schema.tables WHERE table_type = \'BASE TABLE\' AND table_schema NOT IN (\'information_schema\', \'pg_catalog\')) x ORDER BY x.size desc, x.total_size desc, table_schema, table_name LIMIT 40)';

-- List the largest indexes. Tables without any indexes are listed first.
\set bigindexes '(SELECT t.tablename, indexname, c.reltuples AS num_rows, pg_relation_size(quote_ident(t.tablename)::text) AS table_size, pg_relation_size(quote_ident(indexrelname)::text) AS index_size, CASE WHEN x.is_unique = 1 THEN ''Y'' ELSE ''N'' END as unique, idx_scan AS number_of_scans, idx_tup_read AS tuples_read, idx_tup_fetch AS tuples_fetched FROM pg_tables t LEFT JOIN pg_class c ON t.tablename=c.relname LEFT JOIN (SELECT indrelid, max(CAST(indisunique AS integer)) AS is_unique FROM pg_index GROUP BY indrelid) x ON c.oid = x.indrelid LEFT JOIN (SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname FROM pg_index x JOIN pg_class c ON c.oid = x.indrelid JOIN pg_class ipg ON ipg.oid = x.indexrelid JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid) AS idx_info ON t.tablename = idx_info.ctablename WHERE t.schemaname=''public'' ORDER BY 5 desc limit 40)';

-- Show active transactions
\set active '(SELECT datname, pid, now() - query_start AS runtime, case when waiting then \'WAIT\' else \'\' end AS wait, query FROM pg_stat_activity WHERE query <> \'<IDLE>\' AND pid <> pg_backend_pid() ORDER BY runtime DESC)';

-- List the most common full table scans.
\set seq_scans '(SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch FROM pg_stat_all_tables WHERE schemaname=\'public\' AND pg_relation_size(relname::regclass)>50000 AND seq_tup_read > 100000 AND seq_scan > 9 ORDER BY seq_scan desc)';

-- Show how much the indexes are being used
\set index_stats '(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 > 1000 ORDER BY usage desc, cache_hit_ratio desc, index_ratio ASC, s.seq_scan desc, s.n_live_tup desc)';

-- List unused indexes
\set index_never_used '(SELECT indexrelid::regclass as index, relid::regclass as table FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique is false order by relid::regclass)';

-- Show duplicate indexes
\set index_dups '(SELECT indrelid::regclass as table, array_agg(indexrelid::regclass) as duplicates FROM pg_index group by indrelid, indkey having count(*) > 1)';

-- Show indexes that also exists in multi-key indexes
\set index_in_multi '(SELECT a.indrelid::regclass as table_name, a.indexrelid::regclass, b.indexrelid::regclass FROM (SELECT *,array_to_string(indkey,\' \') as cols FROM pg_index) a JOIN (SELECT *,array_to_string(indkey,\' \') as cols FROM pg_index) b on (a.indrelid=b.indrelid AND a.indexrelid > b.indexrelid AND not a.indisunique AND not b.indisunique AND ((a.cols LIKE b.cols||\'%\' AND coalesce(substr(a.cols,length(b.cols)+1,1),\' \')=\' \') OR (b.cols LIKE a.cols||\'%\' AND coalesce(substr(b.cols,length(a.cols)+1,1),\' \')=\' \'))) ORDER BY table_name)';

-- Show connected slaves
\set slaves '(SELECT host(client_addr) as slave, state as state, backend_start as started FROM pg_stat_replication)';

-- Show byde lag of slaves
\set byte_lag '(SELECT client_addr, sent_offset - (replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 ) AS byte_lag FROM (SELECT client_addr, (\'x\' || lpad(split_part(sent_location,   \'/\', 1), 8, \'0\'))::bit(32)::bigint AS sent_xlog, (\'x\' || lpad(split_part(replay_location, \'/\', 1), 8, \'0\'))::bit(32)::bigint AS replay_xlog, (\'x\' || lpad(split_part(sent_location, \'/\', 2), 8, \'0\'))::bit(32)::bigint AS sent_offset, (\'x\' || lpad(split_part(replay_location, \'/\', 2), 8, \'0\'))::bit(32)::bigint AS replay_offset FROM pg_stat_replication) AS s)';

-- Show cache hitrate
\set cache_hitrate '(SELECT \'index hit rate\' as name, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit + idx_blks_read) as ratio FROM pg_statio_user_indexes union all SELECT \'cache hit rate\' as name, case sum(idx_blks_hit) when 0 then \'NaN\'::numeric else to_char((sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit + idx_blks_read), \'99.99\')::numeric end as ratio FROM pg_statio_user_indexes)';

-- Bad hitrate
\set bad_hitrate '(select relname, 100 * idx_scan / (seq_scan + idx_scan) as hitrate, n_live_tup from pg_stat_user_tables where (100 * idx_scan / (seq_scan + idx_scan)) < 99 order by n_live_tup desc)';

-- Shorthand
\set sc 'SELECT COUNT(*) FROM '

-- Long Running queries
\set long_running_query '(SELECT psa.datname as database, psa.query as current_query, clock_timestamp() - psa.xact_start AS transaction_age, array_agg(distinct c.relname) AS tables_with_locks FROM pg_catalog.pg_stat_activity psa JOIN pg_catalog.pg_locks l ON (psa.pid = l.pid) JOIN pg_catalog.pg_class c ON (l.relation = c.oid) JOIN pg_catalog.pg_namespace ns ON (c.relnamespace = ns.oid) WHERE psa.pid != pg_backend_pid() AND ns.nspname != \'pg_catalog\' AND c.relkind = \'r\' AND psa.xact_start < clock_timestamp() - \'5 seconds\'::interval GROUP BY psa.datname, psa.query, psa.xact_start)';