postgresql.queries.md

Exploitation

Database information

Schema settings

SELECT r.rolname, d.datname, rs.setconfig
FROM   pg_db_role_setting rs
LEFT   JOIN pg_roles      r ON r.oid = rs.setrole
LEFT   JOIN pg_database   d ON d.oid = rs.setdatabase

Database size

SELECT pg_database.datname as "database_name", pg_size_pretty(pg_database_size(pg_database.datname)) AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;

Tables and indexes size

SELECT
   table_name,
   pg_size_pretty(table_size) AS table_size,
   pg_size_pretty(indexes_size) AS indexes_size,
   pg_size_pretty(total_size) AS total_size
FROM (
   SELECT
       table_name,
       pg_table_size(table_name) AS table_size,
       pg_indexes_size(table_name) AS indexes_size,
       pg_total_relation_size(table_name) AS total_size
   FROM (
       SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
       FROM information_schema.tables
   ) AS all_tables
   ORDER BY total_size DESC
) AS pretty_sizes;

Relation Files path

SELECT
 (
   SELECT
     setting
   FROM
     pg_settings
   WHERE
     name = 'data_directory') || '/' || (
   SELECT
     oid
   FROM
     pg_database
   WHERE
     datname = current_database()) || '/' || relfilenode AS filename,
 relkind,
 nspname || '.' || relname AS relname,
 relpages
FROM
 pg_class c
 JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
 nspname !~ '^pg_|^info'
 AND relkind IN ('r', 't', 'm', 'i', 'p', 'I')
ORDER BY
 relkind,
 c.relname;

;

How to list locks type

select  pg_class.relname, act.pid, act.datname, act.query, string_agg(lock.mode, ',')
from pg_stat_activity act
natural join pg_locks lock
join pg_class on lock.relation = pg_class.oid
where act.backend_type = 'client backend' and act.pid <> pg_backend_pid() and pg_class.relkind = 'r'
group by 1,2,3,4;

How to detect query which holds the lock

 with queries_blocked as (select pid as blocked_pid, 
       usename, 
       unnest(pg_blocking_pids(pid)) as blocked_by_pid, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0) 
select queries_blocked.*, query as blocked_by_query
from pg_stat_activity blocked_by
join queries_blocked on blocked_by.pid = queries_blocked.blocked_by_pid;

Slow queries (need pg_stat_statements extension)

SELECT
    (pss.total_time / 1000) AS total_second,
    ((pss.total_time /1000) / calls) AS total_average_time,
    -- pss.min_time / 1000 as min_time,
    -- pss.max_time / 1000 as max_time,
    pss.calls AS calls,
    pss.rows AS total_row_count,
    u.usename,
    pd.datname,
    regexp_replace(pss.query, E'[\\n\\r]+', ' ', 'g' ) AS sql_query,
    100.0 * pss.shared_blks_hit / nullif(pss.shared_blks_hit + pss.shared_blks_read, 0) AS hit_percent
FROM
    pg_stat_statements AS pss
INNER JOIN pg_database AS pd ON pss.dbid = pd.oid
INNER JOIN pg_catalog.pg_user u on u.usesysid = pss.userid
-- WHERE
--         upper(pss.query) ILIKE '%SELECT %'
--         or upper(pss.query) ILIKE '%INSERT %'
--         or upper(pss.query) ILIKE '%UPDATE %'
--         or upper(pss.query) ILIKE '%DELETE %'
ORDER BY (pss.total_time / calls) DESC
LIMIT 50;


SELECT substring(query, 1, 50) AS short_query,
round(total_time::numeric, 2) AS total_time,
calls,
round(mean_time::numeric, 2) AS mean,
round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

Usage

Generate random different data

INSERT INTO  start_date_application_technical_fee_rate ("id","rate","month") 
       select
              uuid_in(md5(random() :: text || now() :: text) :: cstring),
              floor(random() * (10 -2 + 1) + 2) :: int as rate, /*between 2 and 10*/
              start_month::date as start_date
       from generate_series('2018-01-01','2018-12-31', '1 month'::interval) start_month

Move all tables from a schema to another

\set schema_origin public
\set schema_destination clients

select 'ALTER TABLE ' || :'schema_origin'||'.'|| t.tablename || ' SET SCHEMA to "' || :'schema_destination' ||'" ' From  pg_tables t where schemaname =:'schema_origin' \gexec

How to change table owner of a specific schema

 select 'ALTER TABLE schema1.' || t.tablename || ' owner to "owner1" ' from  pg_tables t where schemaname ='schema1' \gexec

Set all sequences of schema

SELECT 'SELECT setval('''||seq.sequence_name||''', max("'||a.attname||'")) FROM "'||d.refobjid::regclass||'";'
FROM   pg_depend    d
JOIN   pg_attribute a ON a.attrelid = d.refobjid
  AND a.attnum   = d.refobjsubid
JOIN  pg_class as pg_class_seq on pg_class_seq.relname::regclass = d.objid and pg_class_seq.relkind = 'S'
JOIN  information_schema.sequences seq on seq.sequence_schema = 'sakila' and pg_class_seq.relname = seq.sequence_name
WHERE
1=1
AND d.refobjsubid > 0
AND    d.classid = 'pg_class'::regclass \gexec

Maintenance

Last vacuum

select
  schemaname,
  relname,
  last_autovacuum,
  last_autoanalyze,
  n_mod_since_analyze
from
  pg_stat_user_tables
order by
  n_mod_since_analyze,
  last_autoanalyze desc

Last vacuum analyse

select
  schemaname,
  relname,
  vacuum_count,
  analyze_count,
  last_vacuum,
  last_analyze
from
  pg_stat_user_tables
order by
  vacuum_count desc,
  analyze_count desc

Eligible vacuuming table

WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM 
pg_settings WHERE name = 'autovacuum_vacuum_threshold')
    , vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM 
pg_settings WHERE name = 'autovacuum_vacuum_scale_factor')
    , fma AS (SELECT setting AS autovacuum_freeze_max_age FROM 
pg_settings WHERE name = 'autovacuum_freeze_max_age')
    , sto AS (select opt_oid, split_part(setting, '=', 1) as param, 
split_part(setting, '=', 2) as value from (select oid opt_oid, 
unnest(reloptions) setting from pg_class) opt)
SELECT
    '"'||ns.nspname||'"."'||c.relname||'"' as relation
    , pg_size_pretty(pg_table_size(c.oid)) as table_size
    , age(relfrozenxid) as xid_age
    , coalesce(cfma.value::float, autovacuum_freeze_max_age::float) 
autovacuum_freeze_max_age
    , (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) 
+ coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * 
c.reltuples) as autovacuum_vacuum_tuples
    , n_dead_tup as dead_tuples
FROM pg_class c join pg_namespace ns on ns.oid = c.relnamespace
join pg_stat_all_tables stat on stat.relid = c.oid
join vbt on (1=1) join vsf on (1=1) join fma on (1=1)
left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and 
c.oid = cvbt.opt_oid
left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and 
c.oid = cvsf.opt_oid
left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and 
c.oid = cfma.opt_oid
WHERE c.relkind = 'r' and nspname <> 'pg_catalog'
and (
    age(relfrozenxid) >= coalesce(cfma.value::float, 
autovacuum_freeze_max_age::float)
    or
    coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + 
coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * 
c.reltuples <= n_dead_tup
   -- or 1 = 1
)
ORDER BY age(relfrozenxid) DESC LIMIT 50;

Indexes

Missing indexes on FK

-- check for FKs where there is no matching index
-- on the referencing side
-- or a bad index

with fk_actions ( code, action ) as (
    values ( 'a', 'error' ),
        ( 'r', 'restrict' ),
        ( 'c', 'cascade' ),
        ( 'n', 'set null' ),
        ( 'd', 'set default' )
),
fk_list as (
    select pg_constraint.oid as fkoid, conrelid, confrelid as parentid,
        conname, relname, nspname,
        fk_actions_update.action as update_action,
        fk_actions_delete.action as delete_action,
        conkey as key_cols
    from pg_constraint
        join pg_class on conrelid = pg_class.oid
        join pg_namespace on pg_class.relnamespace = pg_namespace.oid
        join fk_actions as fk_actions_update on confupdtype =
fk_actions_update.code
        join fk_actions as fk_actions_delete on confdeltype =
fk_actions_delete.code
    where contype = 'f'
),
fk_attributes as (
    select fkoid, conrelid, attname, attnum
    from fk_list
        join pg_attribute
            on conrelid = attrelid
            and attnum = any( key_cols )
    order by fkoid, attnum
),
fk_cols_list as (
    select fkoid, array_agg(attname) as cols_list
    from fk_attributes
    group by fkoid
),
index_list as (
    select indexrelid as indexid,
        pg_class.relname as indexname,
        indrelid,
        indkey,
        indpred is not null as has_predicate,
        pg_get_indexdef(indexrelid) as indexdef
    from pg_index
        join pg_class on indexrelid = pg_class.oid
    where indisvalid
),
fk_index_match as (
    select fk_list.*,
        indexid,
        indexname,
        indkey::int[] as indexatts,
        has_predicate,
        indexdef,
        array_length(key_cols, 1) as fk_colcount,
        array_length(indkey,1) as index_colcount,
        round(pg_relation_size(conrelid)/(1024^2)::numeric) as table_mb,
        cols_list
    from fk_list
        join fk_cols_list using (fkoid)
        LEFT OUTER join index_list
            on conrelid = indrelid
            and (indkey::int2[])[0:(array_length(key_cols,1) -1)] @> key_cols

),
fk_perfect_match as (
    select fkoid
    from fk_index_match
    where (index_colcount - 1) <= fk_colcount
        and not has_predicate
        and indexdef like '%using btree%'
),
fk_index_check as (
    select 'no index' as issue, *, 1 as issue_sort
    from fk_index_match
    where indexid is null
    union all
    select 'questionable index' as issue, *, 2
    from fk_index_match
    where indexid is not null
        and fkoid not in (
            select fkoid
            from fk_perfect_match)
),
parent_table_stats as (
    select fkoid, tabstats.relname as parent_name,
        (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as parent_writes,
        round(pg_relation_size(parentid)/(1024^2)::numeric) as parent_mb
    from pg_stat_user_tables as tabstats
        join fk_list
            on relid = parentid
),
fk_table_stats as (
    select fkoid,
        (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as writes,
        seq_scan as table_scans
    from pg_stat_user_tables as tabstats
        join fk_list
            on relid = conrelid
)
select nspname as schema_name,
    relname as table_name,
    conname as fk_name,
    issue,
    table_mb,
    writes,
    table_scans,
    parent_name,
    parent_mb,
    parent_writes,
    cols_list,
    indexdef
from fk_index_check
    join parent_table_stats using (fkoid)
    join fk_table_stats using (fkoid)
where table_mb > 9
    and ( writes > 1000
        or parent_writes > 1000
        or parent_mb > 10 )
order by issue_sort, table_mb desc, table_name, fk_name;

Using indexes

SELECT i.oid, d.oid, n.oid, c.oid,
	n.nspname as schema_name,
	c.relname as tablename,
	i.relname, i.reltablespace, i.relpages, i.reltuples, x.indisunique, x.indisprimary,
		x.indisvalid, x.indisready, x.indcheckxmin, x.xmin,
		pg_get_indexdef(i.oid), pg_relation_size(i.oid),
		pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid) as idx_blks_read ,-- idx_blks_read
		pg_stat_get_blocks_hit(i.oid), -- idx_blks_hit
		s.idx_scan
	FROM pg_database d, pg_index x
		JOIN pg_class c ON c.oid = x.indrelid
		JOIN pg_class i ON i.oid = x.indexrelid
		JOIN pg_stat_user_indexes s ON s.indexrelid = x.indexrelid
		LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
	WHERE d.datname = 'database name'
		AND c.relkind = 'r'::"char" AND i.relkind = 'i'::"char"
		AND n.nspname <> 'pg_catalog'
		AND n.nspname <> 'information_schema'
		AND n.nspname !~ '^pg_temp_'
		AND n.nspname !~ '^pg_toast'

Duplicate indexes

SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
      (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
      (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM (
   SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text
||E'\n'|| indkey::text ||E'\n'||
                                        COALESCE(indexprs::text,'')||E'\n' ||
COALESCE(indpred::text,'')) AS KEY
   FROM pg_index) sub
GROUP BY KEY HAVING COUNT(*)>1
ORDER BY SUM(pg_relation_size(idx)) DESC;

Missing indexes

SELECT
 relname,
 seq_scan - idx_scan AS too_much_seq,
 CASE
   WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'Missing Index ?'
   ELSE 'OK'
 END,
 pg_relation_size(relname::regclass) AS rel_size, 
 seq_scan, idx_scan
FROM pg_stat_all_tables
WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000 
ORDER BY too_much_seq DESC;

Tables need indexing

SELECT
 x1.table_in_trouble,
 pg_relation_size(x1.table_in_trouble) AS sz_n_byts,
 x1.seq_scan,
 x1.idx_scan,
 CASE
 WHEN pg_relation_size(x1.table_in_trouble) > 500000000
   THEN 'Exceeds 500 megs, too large to count in a view. For a count, count individually'::text
 ELSE count(x1.table_in_trouble)::text
 END                                   AS tbl_rec_count,
 x1.priority
FROM
 (
   SELECT
     (schemaname::text || '.'::text) || relname::text AS table_in_trouble,
     seq_scan,
     idx_scan,
     CASE
     WHEN (seq_scan - idx_scan) < 500
       THEN 'Minor Problem'::text
     WHEN (seq_scan - idx_scan) >= 500 AND (seq_scan - idx_scan) < 2500
       THEN 'Major Problem'::text
     WHEN (seq_scan - idx_scan) >= 2500
       THEN 'Extreme Problem'::text
     ELSE NULL::text
     END AS priority
   FROM
     pg_stat_all_tables
   WHERE
     seq_scan > idx_scan
     AND schemaname != 'pg_catalog'::name
   AND seq_scan > 100) x1
GROUP BY
 x1.table_in_trouble,
 x1.seq_scan,
 x1.idx_scan,
 x1.priority
ORDER BY
 x1.priority DESC,
 x1.seq_scan;

Bloat

SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
 (tblpages-est_tblpages)*bs AS extra_size,
 CASE WHEN tblpages - est_tblpages > 0
   THEN 100 * (tblpages - est_tblpages)/tblpages::float
   ELSE 0
 END AS extra_ratio, fillfactor,
 CASE WHEN tblpages - est_tblpages_ff > 0
   THEN (tblpages-est_tblpages_ff)*bs
   ELSE 0
 END AS bloat_size,
 CASE WHEN tblpages - est_tblpages_ff > 0
   THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float
   ELSE 0
 END AS bloat_ratio, is_na
 -- , tpl_hdr_size, tpl_data_size, (pst).free_percent + (pst).dead_tuple_percent AS real_frag -- (DEBUG INFO)
FROM (
 SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,
   ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
   tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
   -- , tpl_hdr_size, tpl_data_size, pgstattuple(tblid) AS pst -- (DEBUG INFO)
 FROM (
   SELECT
     ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
       - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
       - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
     ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
     toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na
     -- , tpl_hdr_size, tpl_data_size
   FROM (
     SELECT
       tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
       tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
       coalesce(toast.reltuples, 0) AS toasttuples,
       coalesce(substring(
         array_to_string(tbl.reloptions, ' ')
         FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
       current_setting('block_size')::numeric AS bs,
       CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
       24 AS page_hdr,
       23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END
          + CASE WHEN bool_or(att.attname = 'oid' and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
       sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size,
       bool_or(att.atttypid = 'pg_catalog.name'::regtype)
         OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na
     FROM pg_attribute AS att
       JOIN pg_class AS tbl ON att.attrelid = tbl.oid
       JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
       LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname
         AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
       LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
     WHERE NOT att.attisdropped
       AND tbl.relkind = 'r'
     GROUP BY 1,2,3,4,5,6,7,8,9,10
     ORDER BY 2,3
   ) AS s
 ) AS s2
) AS s3
WHERE 1=1 
AND schemaname not in ('information_schema', 'pg_catalog')
-- AND NOT is_na
--   AND tblpages*((pst).free_percent + (pst).dead_tuple_percent)::float4/100 >= 1
ORDER BY schemaname, tblname

Bloat table + index

SELECT

 schemaname||'.'|| tblname as "object",

 relkind, bs*tblpages AS real_size,

 CASE WHEN tblpages - est_tblpages_ff > 0

   THEN (tblpages-est_tblpages_ff)*bs

   ELSE 0

 END AS bloat_size,

 CASE WHEN tblpages - est_tblpages_ff > 0

   THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float

   ELSE 0

 END AS bloat_ratio

FROM (

 SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,

   ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,

   tblpages, relkind, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na

 FROM (

   SELECT

     ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)

       - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END

       - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END

     ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,

     toastpages, reltuples, relkind, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na

   FROM (

     SELECT

       tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples, tbl.relkind,

       tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,

       coalesce(toast.reltuples, 0) AS toasttuples,

       coalesce(substring(

         array_to_string(tbl.reloptions, ' ')

         FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,

       current_setting('block_size')::numeric AS bs,

       CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,

       24 AS page_hdr,

       23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END

          + CASE WHEN bool_or(att.attname = 'oid' and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,

       sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size,

       bool_or(att.atttypid = 'pg_catalog.name'::regtype)

         OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na

     FROM pg_attribute AS att

       JOIN pg_class AS tbl ON att.attrelid = tbl.oid

       JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace

       LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname

         AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname

       LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid

     WHERE NOT att.attisdropped

       AND tbl.relkind in ('r','m')

       AND ns.nspname NOT IN ('pg_catalog','information_schema')

     GROUP BY 1,2,3,4,5,6,7,8,9,10

     ORDER BY 2,3

   ) AS s

 ) AS s2

) AS s3

UNION ALL

SELECT

 nspname ||'.'|| tblname ||'.'|| idxname as "object", relkind

 , bs*(relpages)::bigint AS real_size,

 CASE WHEN relpages > est_pages_ff

   THEN bs*(relpages-est_pages_ff)

   ELSE 0

 END AS bloat_size,

 100 * (relpages-est_pages_ff)::float / relpages AS bloat_ratio

FROM (

 SELECT coalesce(1 +

        ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)

     ) AS est_pages,

     coalesce(1 +

        ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0

     ) AS est_pages_ff,

     bs, nspname, tblname, idxname, relkind, relpages, fillfactor, is_na

 FROM (

     SELECT maxalign, bs, nspname, tblname, idxname, relkind, reltuples, relpages, idxoid, fillfactor,

           ( index_tuple_hdr_bm +

               maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN

                 WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign

                 ELSE index_tuple_hdr_bm%maxalign

               END

             + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN

                 WHEN nulldatawidth = 0 THEN 0

                 WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign

                 ELSE nulldatawidth::integer%maxalign

               END

           )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na

     FROM (

         SELECT n.nspname, i.tblname, i.idxname, i.relkind, i.reltuples, i.relpages,

             i.idxoid, i.fillfactor, current_setting('block_size')::numeric AS bs,

             CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)

               WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8

               ELSE 4

             END AS maxalign,

             /* per page header, fixed size: 20 for 7.X, 24 for others */

             24 AS pagehdr,

             /* per page btree opaque data */

             16 AS pageopqdata,

             /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */

             CASE WHEN max(coalesce(s.null_frac,0)) = 0

                 THEN 2 -- IndexTupleData size

                 ELSE 2 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)

             END AS index_tuple_hdr_bm,

             /* data len: we remove null values save space using it fractionnal part from stats */

             sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth,

             max( CASE WHEN i.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na

         FROM (

           SELECT ct.relname AS tblname, ct.relnamespace, ic.idxname, ic.relkind, ic.attpos, ic.indkey

                  , ic.indkey[ic.attpos], ic.reltuples, ic.relpages, ic.tbloid, ic.idxoid, ic.fillfactor,

                 coalesce(a1.attnum, a2.attnum) AS attnum, coalesce(a1.attname, a2.attname) AS attname, coalesce(a1.atttypid, a2.atttypid) AS atttypid,

                 CASE WHEN a1.attnum IS NULL

                 THEN ic.idxname

                 ELSE ct.relname

                 END AS attrelname

             FROM (

                 SELECT idxname, relkind, reltuples, relpages, tbloid, idxoid, fillfactor, indkey,

                     pg_catalog.generate_series(1,indnatts) AS attpos

                 FROM (

                     SELECT ci.relname AS idxname, ci.relkind, ci.reltuples, ci.relpages, i.indrelid AS tbloid,

                         i.indexrelid AS idxoid,

                         coalesce(substring(

                             array_to_string(ci.reloptions, ' ')

                             from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor,

                         i.indnatts,

                         pg_catalog.string_to_array(pg_catalog.textin(

                             pg_catalog.int2vectorout(i.indkey)),' ')::int[] AS indkey

                     FROM pg_catalog.pg_index i

                     JOIN pg_catalog.pg_class ci ON ci.oid = i.indexrelid

                     WHERE ci.relam=(SELECT oid FROM pg_am WHERE amname = 'btree')

                     AND ci.relpages > 0

                 ) AS idx_data

             ) AS ic

             JOIN pg_catalog.pg_class ct ON ct.oid = ic.tbloid

             LEFT JOIN pg_catalog.pg_attribute a1 ON

                 ic.indkey[ic.attpos] <> 0

                 AND a1.attrelid = ic.tbloid

                 AND a1.attnum = ic.indkey[ic.attpos]

             LEFT JOIN pg_catalog.pg_attribute a2 ON

                 ic.indkey[ic.attpos] = 0

                 AND a2.attrelid = ic.idxoid

                 AND a2.attnum = ic.attpos

           ) i

           JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace

           JOIN pg_catalog.pg_stats s ON s.schemaname = n.nspname

                                     AND s.tablename = i.attrelname

               AND s.attname = i.attname

           WHERE n.nspname not in ('pg_catalog','information_schema')

           GROUP BY 1,2,3,4,5,6,7,8,9,10,11

     ) AS rows_data_stats

 ) AS rows_hdr_pdg_stats

) AS relation_stats

ORDER BY relkind, bloat_ratio DESC;