WITH s AS (
    SELECT
        current_setting('autovacuum_analyze_scale_factor')::float8  AS  analyze_factor,
        current_setting('autovacuum_analyze_threshold')::float8  AS  analyze_threshold,
        current_setting('autovacuum_vacuum_scale_factor')::float8   AS  vacuum_factor,
        current_setting('autovacuum_vacuum_threshold')::float8   AS  vacuum_threshold
)
SELECT
    n.nspname,
    c.relname,
    c.oid AS relid,
    reltuples::bigint AS estimate,
    t.n_dead_tup,
    t.n_mod_since_analyze,
    c.reltuples * s.vacuum_factor + s.vacuum_threshold AS v_threshold,
    c.reltuples * s.analyze_factor + s.analyze_threshold AS a_threshold
FROM
    s,
    pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    JOIN pg_stat_all_tables t ON c.oid = t.relid
WHERE
    c.relkind = 'r'
ORDER BY c.relname;

SELECT
 c.oid AS "oid"
 , n.nspname AS "schema"
 , c.relname AS "table"
 , s.last_vacuum AS last_manual_vacuum
 , now() - s.last_vacuum AS time_since_last_manual_vacuum
 , s.last_autovacuum AS last_auto_vacuum
 , now() - s.last_autovacuum AS time_since_last_auto_vacuum
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_stat_all_tables s ON c.oid = s.relid AND (s.last_vacuum IS NOT NULL OR s.last_autovacuum IS NOT NULL)
WHERE c.relkind IN ('r', 'm')
ORDER BY 6 DESC, time_since_last_auto_vacuum DESC;

WITH max_age AS (
    SELECT 2000000000 as max_old_xid,
            setting AS autovacuum_freeze_max_age
    FROM pg_catalog.pg_settings
    WHERE name = 'autovacuum_freeze_max_age'
),
per_database_stats AS (
    SELECT datname
        , m.max_old_xid::int
        , m.autovacuum_freeze_max_age::int
        , age(d.datfrozenxid) AS oldest_current_xid
    FROM pg_catalog.pg_database d
    JOIN max_age m ON (true)
    WHERE d.datallowconn
)
SELECT datname,max(oldest_current_xid) AS oldest_current_xid
, max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
, max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac
FROM per_database_stats
GROUP BY datname
;

SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY 2 DESC,1 ASC
;

WITH cur_vaccs AS (SELECT split_part(split_part(substring(query from 'public\..*'),'.',2),' ',1) as tab FROM pg_stat_activity WHERE query like 'autovacuum%')
select 'VACUUM FREEZE "'|| n.nspname ||'"."'|| c.relname ||'"; --"'||pg_size_pretty(pg_table_size(c.oid))||'" --"'||age(c.relfrozenxid)||'"'
  from pg_class c
  inner join pg_namespace n on c.relnamespace = n.oid
  left join pg_class t on c.reltoastrelid = t.oid and t.relkind = 't'
where c.relkind in ('r','m')
AND NOT EXISTS (SELECT * FROM cur_vaccs WHERE tab = c.relname)
order by GREATEST(age(c.relfrozenxid),age(t.relfrozenxid)) DESC
limit 10;


\gexec

WITH constants AS (
    -- definiowanie stalych wykorzystywanych w zapytaniu
    -- na podstawie parametrow postgresa
    -- przechowywanych jako CTE (Common Table Expression)
    SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 8 AS ma
),
no_stats AS (
    -- odfiltrowanie tabel ktore posiadajacych atrybuty
    -- ktore nie posiadaja statystyk, takich jak JSON
    SELECT table_schema, table_name,
        n_live_tup::numeric as est_rows,
        pg_table_size(relid)::numeric as table_size
    FROM information_schema.columns
        JOIN pg_stat_user_tables as psut
           ON table_schema = psut.schemaname
           AND table_name = psut.relname
        LEFT OUTER JOIN pg_stats
        ON table_schema = pg_stats.schemaname
            AND table_name = pg_stats.tablename
            AND column_name = attname
    WHERE attname IS NULL
        AND table_schema NOT IN ('pg_catalog', 'information_schema')
    GROUP BY table_schema, table_name, relid, n_live_tup
),
null_headers AS (
    -- znajdowanie pustych kolumn
    -- pomijanie tabel ktore nie posiadaja kompletnych statystyk
    -- oraz posiadaja atrybuty dla ktorych statystyki sa niedostepne
    SELECT
        hdr+1+(sum(case when null_frac <> 0 THEN 1 else 0 END)/8) as nullhdr,
        SUM((1-null_frac)*avg_width) as datawidth,
        MAX(null_frac) as maxfracsum,
        schemaname,
        tablename,
        hdr, ma, bs
    FROM pg_stats CROSS JOIN constants
        LEFT OUTER JOIN no_stats
            ON schemaname = no_stats.table_schema
            AND tablename = no_stats.table_name
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
        AND no_stats.table_name IS NULL
        AND EXISTS ( SELECT 1
            FROM information_schema.columns
                WHERE schemaname = columns.table_schema
                    AND tablename = columns.table_name )
    GROUP BY schemaname, tablename, hdr, ma, bs
),
data_headers AS (
    -- szacunkowy rozmiar naglowka i wiersza w bloku danych
    SELECT
        ma, bs, hdr, schemaname, tablename,
        (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
        (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM null_headers
),
table_estimates AS (
    -- szacowanie rzeczywistego rozmiaru danych w tabeli
    -- na podstawie rozmiaru wierzsza i bloku danych
    SELECT schemaname, tablename, bs,
        reltuples::numeric as est_rows, relpages * bs as table_bytes,
    CEIL((reltuples*
            (datahdr + nullhdr2 + 4 + ma -
                (CASE WHEN datahdr%ma=0
                    THEN ma ELSE datahdr%ma END)
                )/(bs-20))) * bs AS expected_bytes,
        reltoastrelid
    FROM data_headers
        JOIN pg_class ON tablename = relname
        JOIN pg_namespace ON relnamespace = pg_namespace.oid
            AND schemaname = nspname
    WHERE pg_class.relkind = 'r'
),
estimates_with_toast AS (
    -- szacunkowy rozmiar tabel toast
    -- szacunek oparty o zalozenie ze 4 wiersze toast znaduja sie
    -- w kazdym bloku danych, nie ma tutaj dokladnej wartosci i nie
    -- jestesmy w stanie jej odczytac ze statystyk
    SELECT schemaname, tablename,
        TRUE as can_estimate,
        est_rows,
        table_bytes + ( coalesce(toast.relpages, 0) * bs ) as table_bytes,
        expected_bytes + ( ceil( coalesce(toast.reltuples, 0) / 4 ) * bs ) as expected_bytes
    FROM table_estimates LEFT OUTER JOIN pg_class as toast
        ON table_estimates.reltoastrelid = toast.oid
            AND toast.relkind = 't'
),
table_estimates_plus AS (
-- dodatkowe wartosci zawierajace metadata dla tabel
-- i obliczenia wykorzystywane pozniej w zapytaniu glownym
-- oraz informacja czy mozemy oszacowac realny rozmiar danych
-- i ile miejsca bedziemy w stanie odzyskac
    SELECT current_database() as databasename,
            schemaname, tablename, can_estimate,
            est_rows,
            CASE WHEN table_bytes > 0
                THEN table_bytes::NUMERIC
                ELSE NULL::NUMERIC END
                AS table_bytes,
            CASE WHEN expected_bytes > 0
                THEN expected_bytes::NUMERIC
                ELSE NULL::NUMERIC END
                    AS expected_bytes,
            CASE WHEN expected_bytes > 0 AND table_bytes > 0
                AND expected_bytes <= table_bytes
                THEN (table_bytes - expected_bytes)::NUMERIC
                ELSE 0::NUMERIC END AS bloat_bytes
    FROM estimates_with_toast
    UNION ALL
    SELECT current_database() as databasename,
        table_schema, table_name, FALSE,
        est_rows, table_size,
        NULL::NUMERIC, NULL::NUMERIC
    FROM no_stats
),
bloat_data AS (
    -- koncowe obliczenia dla bloatu tabel
    select current_database() as databasename,
        schemaname, tablename, can_estimate,
        table_bytes, round(table_bytes/(1024^2)::NUMERIC,3) as table_mb,
        expected_bytes, round(expected_bytes/(1024^2)::NUMERIC,3) as expected_mb,
        round(bloat_bytes*100/table_bytes) as pct_bloat,
        round(bloat_bytes/(1024::NUMERIC^2),2) as mb_bloat,
        table_bytes, expected_bytes, est_rows
    FROM table_estimates_plus
)
-- zapytanie glowne i filtrowanie wynikow
SELECT databasename, schemaname, tablename,
    can_estimate,
    est_rows,
    pct_bloat, mb_bloat,
    table_mb
FROM bloat_data
-- tutaj definiujemy ktore tabele uznajemy za problematyczne
-- i maja sie pojawic w wyniku zapytania
-- przyklad ponizej zwraca tabele ktore maja bloat powyzej 50%
-- oraz rozmiar powyzej 200mb, lub bloat wiekszy niz 25%
-- i rozmiar tabeli powyzej 10GB
WHERE ( pct_bloat >= 50 AND mb_bloat >= 200 )
    OR ( pct_bloat >= 25 AND mb_bloat >= 10240 )
ORDER BY pct_bloat DESC;

-- szacowanie bloatu dla indeksow btree
-- definiowanie stalych wykorzystywanych w zapytaniu
-- na podstawie parametrow postgresa i wlasnosci indeksow
-- przechowywanych jako CTE (Common Table Expression)
WITH btree_index_atts AS (
    SELECT nspname,
        indexclass.relname as index_name,
        indexclass.reltuples,
        indexclass.relpages,
        indrelid, indexrelid,
        indexclass.relam,
        tableclass.relname as tablename,
        regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
        indexrelid as index_oid
    FROM pg_index
    JOIN pg_class AS indexclass ON pg_index.indexrelid = indexclass.oid
    JOIN pg_class AS tableclass ON pg_index.indrelid = tableclass.oid
    JOIN pg_namespace ON pg_namespace.oid = indexclass.relnamespace
    JOIN pg_am ON indexclass.relam = pg_am.oid
    WHERE pg_am.amname = 'btree' and indexclass.relpages > 0
         AND nspname NOT IN ('pg_catalog','information_schema')
    ),
index_item_sizes AS (
    SELECT
    ind_atts.nspname, ind_atts.index_name,
    ind_atts.reltuples, ind_atts.relpages, ind_atts.relam,
    indrelid AS table_oid, index_oid,
    current_setting('block_size')::numeric AS bs,
    8 AS maxalign,
    24 AS pagehdr,
    CASE WHEN max(coalesce(pg_stats.null_frac,0)) = 0
        THEN 2
        ELSE 6
    END AS index_tuple_hdr,
    sum( (1-coalesce(pg_stats.null_frac, 0)) * coalesce(pg_stats.avg_width, 1024) ) AS nulldatawidth
    FROM pg_attribute
    JOIN btree_index_atts AS ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid AND pg_attribute.attnum = ind_atts.attnum
    JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname
          -- statystyki dla indeksow regularnych
          AND ( (pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_catalog.pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE))
          -- statystyki dla indeksow funkcyjnych
          OR   (pg_stats.tablename = ind_atts.index_name AND pg_stats.attname = pg_attribute.attname))
    WHERE pg_attribute.attnum > 0
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
),
index_aligned_est AS (
    SELECT maxalign, bs, nspname, index_name, reltuples,
        relpages, relam, table_oid, index_oid,
        coalesce (
            ceil (
                reltuples * ( 6
                    + maxalign
                    - CASE
                        WHEN index_tuple_hdr%maxalign = 0 THEN maxalign
                        ELSE index_tuple_hdr%maxalign
                      END
                    + nulldatawidth
                    + maxalign
                    - CASE /* Add padding to the data to align on MAXALIGN */
                        WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
                        ELSE nulldatawidth::integer%maxalign
                      END
                )::numeric
              / ( bs - pagehdr::NUMERIC )
              +1 )
         , 0 )
      as expected
    FROM index_item_sizes
),
raw_bloat AS (
    SELECT current_database() as dbname, nspname, pg_class.relname AS table_name, index_name,
        bs*(index_aligned_est.relpages)::bigint AS totalbytes, expected,
        CASE
            WHEN index_aligned_est.relpages <= expected
                THEN 0
                ELSE bs*(index_aligned_est.relpages-expected)::bigint
            END AS wastedbytes,
        CASE
            WHEN index_aligned_est.relpages <= expected
                THEN 0
                ELSE bs*(index_aligned_est.relpages-expected)::bigint * 100 / (bs*(index_aligned_est.relpages)::bigint)
            END AS realbloat,
        pg_relation_size(index_aligned_est.table_oid) as table_bytes,
        stat.idx_scan as index_scans
    FROM index_aligned_est
    JOIN pg_class ON pg_class.oid=index_aligned_est.table_oid
    JOIN pg_stat_user_indexes AS stat ON index_aligned_est.index_oid = stat.indexrelid
),
format_bloat AS (
SELECT dbname as database_name, nspname as schema_name, table_name, index_name,
        round(realbloat) as bloat_pct, round(wastedbytes/(1024^2)::NUMERIC) as bloat_mb,
        round(totalbytes/(1024^2)::NUMERIC,3) as index_mb,
        round(table_bytes/(1024^2)::NUMERIC,3) as table_mb,
        index_scans
FROM raw_bloat
)
-- zapytanie glowne i filtrowanie wynikow
-- w klauzula where w ktorej definiujemy ktore indeksy maja zostac zwrocone
-- oraz wskazujemy klucz do sortowania w ORDER BY
SELECT *
FROM format_bloat
WHERE ( bloat_pct > 25 and bloat_mb > 10 )
ORDER BY bloat_mb DESC;