date

printf "\n\n\n**** AUTOANALYZE STATS\n"

spacewalk-sql --select-mode - <<EOF
  SELECT
    pg_stat_user_tables.relname AS table,
    analyze_base_threshold.setting AS "analyze base threshold",
    analyze_scale_factor.setting AS "analyze scale factor",
    pg_stat_user_tables.n_mod_since_analyze AS "modified tuples since last analyze",
    analyze_base_threshold.setting + analyze_scale_factor.setting * pg_class.reltuples AS "autoanalyze threshold",
    CASE
      WHEN CAST(current_setting('autovacuum_analyze_threshold') AS bigint)
        + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
        * pg_class.reltuples) < pg_stat_user_tables.n_mod_since_analyze
      THEN '*'
      ELSE ''
    END AS "expect autoanalyze",
    pg_stat_user_tables.autoanalyze_count AS "autoanalyze count",
    pg_stat_user_tables.last_autoanalyze AS "last autoanalyze",
    pg_stat_user_tables.analyze_count AS "analyze count",
    pg_stat_user_tables.last_analyze AS "last analyze"
    FROM pg_stat_user_tables JOIN pg_class ON pg_stat_user_tables.relid = pg_class.oid,
      (SELECT CAST(setting AS FLOAT) FROM pg_settings WHERE name = 'autovacuum_analyze_threshold') AS analyze_base_threshold,
      (SELECT CAST(setting AS FLOAT) FROM pg_settings WHERE name = 'autovacuum_analyze_scale_factor') AS analyze_scale_factor
    ORDER BY pg_stat_user_tables.n_mod_since_analyze / (CAST(current_setting('autovacuum_analyze_threshold') AS bigint)
        + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
        * pg_class.reltuples)) DESC;
EOF

printf "\n\n\n**** AUTOVACUUM STATS\n"

spacewalk-sql --select-mode - <<EOF
  SELECT
    pg_stat_user_tables.relname AS table,
    vacuum_base_threshold.setting AS "vacuum base threshold",
    vacuum_scale_factor.setting AS "vacuum scale factor",
    pg_class.reltuples AS "number of tuples",
    vacuum_base_threshold.setting + vacuum_scale_factor.setting * pg_class.reltuples AS "autovacuum threshold",
    pg_stat_user_tables.n_dead_tup AS "dead tuples",
    CASE
      WHEN CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
        + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
        * pg_class.reltuples) < pg_stat_user_tables.n_dead_tup
      THEN '*'
      ELSE ''
    END AS "expect autovacuum",
    pg_stat_user_tables.autovacuum_count AS "autovacuum count",
    pg_stat_user_tables.last_autovacuum AS "last autovacuum",
    pg_stat_user_tables.vacuum_count AS "vacuum count",
    pg_stat_user_tables.last_vacuum AS "last vacuum"
    FROM pg_stat_user_tables JOIN pg_class ON pg_stat_user_tables.relid = pg_class.oid,
      (SELECT CAST(setting AS FLOAT) FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold') AS vacuum_base_threshold,
      (SELECT CAST(setting AS FLOAT) FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor') AS vacuum_scale_factor
    ORDER BY pg_stat_user_tables.n_dead_tup / (CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
        + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
        * pg_class.reltuples)) DESC;
EOF