-
Notifications
You must be signed in to change notification settings - Fork 1
/
psqlrc
18 lines (12 loc) · 3.8 KB
/
psqlrc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
\set fullexplain explain (analyze, buffers, verbose)
\set fe explain (analyze, buffers, verbose)
\set scanstat 'select relname, idx_scan, seq_scan, n_live_tup, trunc(coalesce(100.0*idx_scan / (case when (idx_scan+seq_scan) = 0 then 1 else (idx_scan+seq_scan) end), 0.00), 2) as idx_scan_percent from pg_stat_user_tables order by 5, 3 desc, 1;';
\set ss 'select relname, idx_scan, seq_scan, n_live_tup, trunc(coalesce(100.0*idx_scan / (case when (idx_scan+seq_scan) = 0 then 1 else (idx_scan+seq_scan) end), 0.00), 2) as idx_scan_percent from pg_stat_user_tables order by 5, 3 desc, 1;'
\set queries 'select pid, now() - query_start as "time", usename, query from pg_stat_activity where state <> ''idle'' and query not ilike ''<idle>'' and query not ilike ''%pepaka_query_filter%'' and query not ilike ''listen %'' and backend_type <> ''walsender'' and query_start < now() - interval ''1 minute'' order by 2 desc nulls last, 1 desc;'
\set q 'select pid, now() - query_start as "time", usename, query from pg_stat_activity where state <> ''idle'' and query not ilike ''<idle>'' and query not ilike ''%pepaka_query_filter%'' and query not ilike ''listen %'' and backend_type <> ''walsender'' and query_start < now() - interval ''1 minute'' order by 2 desc nulls last, 1 desc;'
\set listens 'select pid, now() - query_start as "time", usename, query from pg_stat_activity where query ilike ''listen %'' order by 2 desc nulls last, 1 desc;'
\set l 'select pid, now() - query_start as "time", usename, query from pg_stat_activity where query ilike ''listen %'' order by 2 desc nulls last, 1 desc;'
\set extensions 'select name, installed_version, default_version as available_version, case when (installed_version <> default_version) then ''outdated'' else ''latest'' end as status from pg_available_extensions where installed_version is not null order by 4 desc, 1;'
\set e 'select name, installed_version, default_version as available_version, case when (installed_version <> default_version) then ''outdated'' else ''latest'' end as status from pg_available_extensions where installed_version is not null order by 4 desc, 1;'
\set maintenance 'with slow_queries as (select count(*) from pg_stat_activity where state <> ''idle''and query not ilike ''<idle>'' and query not ilike ''%pepaka_query_filter%'' and query not ilike ''listen %'' and backend_type <> ''walsender'' and query_start < now() - interval ''1 minute''), stale_extensions as (select count(*) as count from pg_available_extensions where installed_version <> default_version), seq_scans as (select count(*) as count from pg_stat_user_tables where coalesce(seq_scan, 0) > coalesce(idx_scan, 0)) select case when stale_extensions.count > 0 then ''BAD: You have ''||stale_extensions.count||'' outdated extensions. Call `:e` for more.'' else ''GOOD: All extensions are up to date'' end as report from stale_extensions union select case when slow_queries.count > 0 then ''BAD: You have ''||slow_queries.count||'' long running queries (> 60s). Call `:q` for more.'' else ''GOOD: No long running queries'' end from slow_queries union select case when seq_scans.count > 0 then ''BAD: You have ''||seq_scans.count||'' tables with < 50% index scans. Call `:ss` for more.'' else ''GOOD: No <50% index scan tables'' end from seq_scans;'
\set help 'select * from (values ('':fullexplain'', '':fe'', ''runs EXPLAIN (ANALYZE, BUFFERS, VERBOSE)'', '':fullexplain select * from pg_stat_activity;''), ('':scanstat'', '':ss'', ''lists index/seq scan stats per table'', '':ss''), ('':queries'', '':q'', ''lists currently running queries ordered by execution time desc'', '':q''), ('':listens'', '':l'', ''lists all active LISTEN channels'', '':l''), ('':extensions'', '':e'', ''lists all installed extensions, outdated first'', '':e''), ('':help'', '''', ''prints this message'', '':help'')) as a(command, alias, description, example);'