.psqlrc

\set QUIET on
\pset linestyle unicode
\pset null ¤
;\set PROMPT1 '%n@%/=%#%x '
\pset pager on
\x auto
\set HISTCONTROL ignoreboth
\set HISTSIZE 10000
\set HISTFILE ~/.psql_history- :DBNAME
\set PSQL_EDITOR 'vim -c ":set ft=sql"'
\t
select 'search_path = ' || current_setting('search_path');
\t
;\timing on
\set QUIET off

\set c 'select count(*) from '

\set sa 'select pid, usename,  now() - query_start as query_age, now() - xact_start as xact_age, wait_event_type, wait_event, query from pg_stat_activity where state != \'idle\' and pid != pg_backend_pid() and (datname = current_database() or datname is null) order by now() - query_start desc limit 20;'

\set sat 'select pid, usename,  now() - query_start as query_age, now() - xact_start as xact_age, wait_event_type, wait_event, query::varchar(100) from pg_stat_activity where state != \'idle\' and pid != pg_backend_pid() and (datname = current_database() or datname is null) order by now() - query_start desc limit 5;'

\set sac 'select state, count(*), avg(now() - query_start) as avg_query from pg_stat_activity where datname = current_database() and state notnull and pid != pg_backend_pid() group by 1;'

\set sacl 'select state, wait_event_type, wait_event, count(*), avg(now() - query_start) as avg_query from pg_stat_activity where datname = current_database() and state notnull and pid != pg_backend_pid() group by 1, 2, 3 order by 4 desc;'

\set sstt 'select queryid, total_time * \'1ms\'::interval as total_time, (100.0 * total_time / sum(total_time) over())::numeric(3,1) as pct_total,  mean_time::int, stddev_time::int, calls / 1000 as calls_k, (100 * shared_blks_hit::numeric / (nullif(shared_blks_hit, 0) + nullif(shared_blks_read, 0)))::numeric(4,1) sb_hit, ltrim(regexp_replace(query, E\'[ \\t\\n\\r]+\' , \' \', \'g\'))::varchar(200) as query from pg_stat_statements where calls > 10 order by total_time desc limit 5;'

\set sstt13 'select queryid, total_exec_time * \'1ms\'::interval as total_time, (100.0::numeric * total_exec_time / (select sum(total_exec_time) from pg_stat_statements))::numeric(4,1) as pct_total,  mean_exec_time::numeric(10,3), stddev_exec_time::numeric(10,3), calls / 1000 as calls_k, (100 * shared_blks_hit::numeric / (nullif(shared_blks_hit, 0) + nullif(shared_blks_read, 0)))::numeric(4,1) sb_hit, ltrim(regexp_replace(query, E\'[ \\t\\n\\r]+\' , \' \', \'g\'))::varchar(200) as query from pg_stat_statements where calls > 10 order by total_time desc limit 5;'

\set ssc 'select queryid, (total_time / 1000)::int as total_time_s, (100.0 * calls / sum(calls) over())::numeric(3,1) as pct_total,  mean_time::int, calls / 1000 as calls_k, ltrim(regexp_replace(query, E\'[ \\t\\n\\r]+\' , \' \', \'g\'))::varchar(200) as query from pg_stat_statements where not query ~* $$^(BEGIN|COMMIT|SET)$$ order by calls desc limit 5;'

\set ssc13 'select queryid, total_exec_time * \'1s\'::interval as total_time, (100.0::numeric * calls / (select sum(calls) from pg_stat_statements))::numeric(3,1) as pct_total,mean_exec_time::int as mean_exec_time_ms, calls / 1000 as calls_k, ltrim(regexp_replace(query, E\'[ \\t\\n\\r]+\' , \' \', \'g\'))::varchar(200) as query from pg_stat_statements where not query ~* $$^(BEGIN|COMMIT|SET)$$ order by calls desc limit 10;'

\set nds 'SELECT (regexp_split_to_array(category, \' \'))[1] as category_shortened, name, case when length(current_setting(name)) < 50 then current_setting(name) else current_setting(name)::varchar(47) || \'...\' end as current_setting, /*case when length(reset_val) < 50 then reset_val else reset_val::varchar(47) || \'...\' end as reset_val,*/ boot_val as pg_default, unit, source FROM pg_settings WHERE NOT category ~* \'formatting\' AND NOT category ~* \'logging\' AND NOT category ~* \'ssl\' AND NOT name ~ \'file\' AND NOT name ~ \'directory\' AND NOT name IN (\'cluster_name\', \'max_stack_depth\') AND boot_val IS DISTINCT FROM reset_val ORDER BY category, name;'

\set conf 'select name as setting, current_setting(name) as value from pg_settings where name = ANY(\'{shared_buffers,work_mem,max_connections,random_page_cost,max_wal_size,checkpoint_timeout,checkpoint_completion_target,default_statistics_target,effective_cache_size,autovacuum,jit,shared_preload_libraries,max_parallel_workers_per_gather,autovacuum_vacuum_scale_factor,autovacuum_vacuum_cost_delay,autovacuum_analyze_scale_factor,effective_io_concurrency,autovacuum_max_workers,synchronous_commit,synchronous_standby_names,listen_addresses,log_destination,fsync,full_page_writes,huge_pages,hot_standby_feedback,log_min_duration_statement,log_min_duration_sample,log_statement,log_statement_sample_rate,max_locks_per_transaction,max_worker_processes,password_encryption,recovery_min_apply_delay,server_version,temp_buffers,temp_file_limit,track_functions,track_io_timing,wal_buffers,wal_compression,wal_recycle,wal_segment_size,archive_mode,archive_command}\') /*and boot_val is distinct from reset_val*/ order by 1;'

\set up 'select now() - pg_postmaster_start_time() as uptime;'

:sat;