psqlrc

-- Prevent commands in psqlrc from writing to stdout
\set QUIET 1

-- \set PROMPT1 '%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%[%033[0m%]%R%# '
-- Fancy prompt
\set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '
\set PROMPT2 '[more] %R > '

-- Use table format (with headers across the top) by default, but switch to expanded table format when there's a lot of data, which makes it much easier to read.
\x auto

-- Use unicode characters to draw tables
\pset linestyle unicode

-- Use table frame for table bordering
\pset border 2

-- Show NULL values as special Unicode symbol, that is unlikely to show up in any text stored in the DB
\pset null ¤

-- Wrap long lines to fit their column widths
\pset format wrapped

-- Show how long a statement ran
\timing

-- Use a separate history file per-database.
\set HISTFILE ~/.psql_history- :DBNAME

-- If a command is run more than once in a row, only store it once in the history.
\set HISTCONTROL ignoredups

-- Autocomplete keywords (like SELECT) in upper-case, even if you started typing them in lower case.
\set COMP_KEYWORD_CASE upper

-- Shorthand to show slow queries. Run with `:show_slow_queries` (requires pg_stat_statements extension)
\set show_slow_queries 'SELECT (total_time / 1000 / 60) as total_minutes, (total_time/calls) as average_time, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100;'

-- Shorthand to show locks
-- TODO: Doesn't quite work with 9.3 yet
\set locks 'SELECT w.locktype AS waiting_locktype,w.relation::regclass AS waiting_table,w.transactionid, substr(w_stm.query,1,20) AS waiting_query,w.mode AS waiting_mode,w.pid AS waiting_pid,other.locktype AS other_locktype,other.relation::regclass AS other_table, other_stm.query AS other_query,other.mode AS other_mode,other.pid AS other_pid,other.granted AS other_granted FROM pg_catalog.pg_locks AS w JOIN pg_catalog.pg_stat_activity AS w_stm ON (w_stm.pid = w.pid) JOIN pg_catalog.pg_locks AS other ON ((w.\"database\" = other.\"database\" AND w.relation  = other.relation) OR w.transactionid = other.transactionid) JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid = other.pid) WHERE NOT w.granted AND w.pid <> other.pid;'


-- Show current backend status with queries, longest running on top
\set top 'SELECT datname, pid, query, state, waiting, application_name FROM pg_stat_activity ORDER BY backend_start;'


-- 4 helpful queries from radek http://radek.cc/2009/08/15/psqlrc-tricks-table-sizes/
\set trashindexes '( SELECT s.schemaname AS schema, s.relname AS relation, s.indexrelname AS index, s.idx_scan AS index_scans, pg_size_pretty(pg_relation_size(s.relid)) AS "table size", pg_size_pretty(pg_relation_size(s.indexrelid)) AS "index size" 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 )'

\set rtsize '(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\') ORDER BY pg_relation_size( quote_ident( table_schema ) || \'.\' || quote_ident( table_name ) ) DESC, table_schema, table_name)'

\set tsize '(SELECT table_schema, table_name, pg_size_pretty(size) AS size, pg_size_pretty(total_size) AS total_size FROM (:rtsize) x ORDER BY x.size DESC, x.total_size DESC, table_schema, table_name)'


-- Performance helpers, from http://okigiveup.net/what-postgresql-tells-you-about-its-performance/
\set cache_hit_ratio 'SELECT blks_hit::float/(blks_read + blks_hit) as cache_hit_ratio FROM pg_stat_database WHERE datname=current_database()'
\set successful_xact_ratio 'SELECT xact_commit::float/(xact_commit + xact_rollback) as successful_xact_ratio FROM pg_stat_database WHERE datname=current_database()'
\set idx_scan_ratio 'SELECT SUM(idx_scan)/(SUM(idx_scan) + SUM(seq_scan)) as idx_scan_ratio FROM pg_stat_all_tables WHERE schemaname=\'public\''
\set idx_detailed_scan_ratio 'SELECT relname, idx_scan::float/(idx_scan + seq_scan + 1) as idx_scan_ratio FROM pg_stat_all_tables WHERE schemaname=\'public\' ORDER BY idx_scan_ratio ASC'
\set db_performance 'SELECT numbackends, blks_hit::float/(blks_read + blks_hit) AS cache_hit_ratio, xact_commit::float/(xact_commit + xact_rollback) AS successful_xact_ratio FROM pg_stat_database WHERE datname=current_database()'
\set mean_query_time 'SELECT queryid, total_time, (total_time::float / calls) AS mean_time, LEFT(query, 40) AS short_query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10'


-- Alternate unused indexes query, via http://jmorano.moretrix.com/2014/02/postgresql-monitor-unused-indexes/
\set unusedindexes '(SELECT relid::regclass AS table, indexrelid::regclass AS index, pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size, idx_tup_read, idx_tup_fetch, idx_scan FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique IS FALSE)'


-- See when tables where last vacuumed
\set last_vacuum 'SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables'


-- Return to normal verbosity
\unset QUIET

-- Verbose error reports.
\set VERBOSITY verbose