[DfE] Register psql

indexes---index_scan.psql

-- NOTE: index scan indicator for busy table
SELECT
    relname
  , idx_scan
  , last_idx_scan
FROM
    pg_stat_user_tables
ORDER BY
    idx_scan DESC
  , relname ASC;

indexes---indexes_count.psql

-- NOTE: table and their total number of indexes
SELECT
  *
FROM
  (
    SELECT
      tablename
    , COUNT(tablename) AS COUNT
    FROM
      pg_indexes
    WHERE
      tablename NOT LIKE 'pg_%'
    GROUP BY
      tablename
    HAVING
      COUNT(*) > 1
  ) AS list_of_indexes
ORDER BY
  COUNT DESC

indexes---indexes_lists.psql

-- NOTE: index lists of table
SELECT
    tablename
  , indexname
  , indexdef
FROM
    pg_indexes
WHERE
    tablename NOT LIKE 'pg_%';
ORDER BY
    tablename ASC

long_running_queries.psql

SELECT
    CONCAT ('SELECT pg_cancel_backend(', pid, ');')
  , query_start
  , query_time
  , query
FROM
    (
        SELECT
            pid
          , query_start
          , now () - query_start AS query_time
          , query
          , state
        FROM
            pg_stat_activity
        ORDER BY
            query_start DESC
    ) AS SKIP_ME
WHERE
    state = 'active'
    AND query != ''
    AND query_time > INTERVAL '20 seconds'
    AND query NOT LIKE '%SKIP_ME%';

rows---dead_and_live_rows.psql

-- NOTE: n_dead_tup is important
--       value since last vacuum
--       best to be low value
SELECT
    relname
  , n_live_tup
  , n_dead_tup
  , (n_dead_tup > 15000) as run_vacuuum
FROM
    pg_stat_user_tables
WHERE
    n_live_tup > 0
ORDER BY
    n_dead_tup DESC
  , n_live_tup DESC
  , relname ASC;

rows---estimated_row_changes.psql

-- NOTE: Estimated row changes since last analyze/vacuum
SELECT
    relname
  , n_mod_since_analyze
  , n_ins_since_vacuum
FROM
    pg_stat_user_tables
ORDER BY
    n_ins_since_vacuum DESC
  , n_mod_since_analyze DESC
  , relname ASC;

SELECT
    CONCAT('VACUUM ', relname)
FROM
    pg_stat_user_tables

table_size.psql

-- NOTE: table sizes
SELECT
    table_name
  , pg_size_pretty (pg_table_size (quote_ident (table_name))) AS pg_table_size
  , pg_size_pretty (pg_indexes_size (quote_ident (table_name))) AS pg_indexes_size
  , pg_size_pretty (pg_total_relation_size (quote_ident (table_name))) AS pg_total_relation_size
  , pg_total_relation_size (quote_ident (table_name)) AS raw_size
FROM
    information_schema.tables
WHERE
    table_schema = 'public'
ORDER BY
    raw_size DESC;

vacuum---autovacuum_daemon_analyze_vacuum_stats.psql

-- NOTE: Autovacuum daemon analyze/vacuum
SELECT
    relname
  , last_autovacuum
  , last_autoanalyze
  , autovacuum_count
  , autoanalyze_count
  , (autovacuum_count + autoanalyze_count) AS total_counts
FROM
    pg_stat_user_tables
ORDER BY
    total_counts desc
  , relname ASC;

vacuum---manual_analyze_vacuum_stats.psql

-- NOTE: Manual analyze/vacuum
SELECT
    relname
  , last_vacuum
  , last_analyze
  , vacuum_count
  , analyze_count
  , (vacuum_count + analyze_count) AS total_counts
FROM
    pg_stat_user_tables
ORDER BY
    total_counts desc
  , relname ASC;