git clone https://github.com/hom3chuk/psqlrc-helpers.git
cd psqlrc-helpers
cat psqlrc >> ~/.psqlrc

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;

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);

:fe select * from pg_stat_activity

explain (analyze, buffers, verbose) 

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;

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;

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;

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;