\set QUIET 1
\pset null '(null)'
\pset linestyle unicode
\pset border 2
\timing
\set ON_ERROR_ROLLBACK interactive
\set HISTFILE ~/.psql_history- :HOST - :DBNAME
\set HISTSIZE 2000
\set PROMPT1 '%n@%/[%M:%>] # '
\set PROMPT2 '> '
\set VERBOSITY verbose
\set COMP_KEYWORD_CASE upper
\unset QUIET

\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;'

\set waits 'SELECT pg_stat_activity.procpid, pg_stat_activity.current_query, pg_stat_activity.waiting, now() - pg_stat_activity.query_start  as "totaltime", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.current_query !~ \'%IDLE%\'::text AND pg_stat_activity.waiting = true;'

\set locks 'SELECT pid,mode,current_query FROM pg_locks,pg_stat_activity WHERE granted=false and locktype=\'transactionid\' and pid=procpid order by pid,granted;'

\set sizes 'SELECT nspname || ''.'' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (''information_schema'') AND C.relkind <> ''i'' AND nspname !~ ''^pg_toast'' AND relpages > 100 ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 30;'

\set cachehitrate '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;'

\set indexhitrate 'SELECT relname, 100 * idx_scan / (seq_scan + idx_scan), n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;'