Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE OR REPLACE FUNCTION f_index_minimum_size(_tbl regclass, _cols VARIADIC text[], OUT estimated_minimum_size bigint)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _missing_column text;
BEGIN

-- assert
SELECT i.attname
FROM   unnest(_cols) AS i(attname)
LEFT   JOIN pg_catalog.pg_attribute a ON a.attname = i.attname
                                     AND a.attrelid = _tbl
WHERE  a.attname IS NULL
INTO   _missing_column;

IF FOUND THEN
   RAISE EXCEPTION 'Table % has no column named %', _tbl, quote_ident(_missing_column);
END IF;


SELECT INTO estimated_minimum_size
       COALESCE(1 + ceil(reltuples/trunc((blocksize-page_overhead)/(4+tuple_size)))::int, 0) * blocksize -- AS estimated_minimum_size
FROM  (
   SELECT maxalign, blocksize, reltuples, fillfactor, page_overhead
        , (maxalign  -- up to 16 columns, else nullbitmap may force another maxalign step
         + CASE WHEN datawidth <= maxalign  THEN maxalign
                WHEN datawidth%maxalign = 0 THEN datawidth
                ELSE                            (datawidth + maxalign) - datawidth%maxalign END  -- add padding to the data to align on MAXALIGN
          ) AS tuple_size
   FROM  (
      SELECT c.reltuples, count(*)
           , 90 AS fillfactor
           , current_setting('block_size')::bigint AS blocksize
           , CASE WHEN version() ~ '64-bit|x86_64|ppc64|ia64|amd64|mingw32'  -- MAXALIGN: 4 on 32bits, 8 on 64bits
                  THEN 8 ELSE 4 END AS maxalign
           , 40 AS page_overhead  -- 24 bytes page header + 16 bytes "special space"
           -- avg data width without null values
           , sum(ceil((1-COALESCE(s.null_frac, 0)) * COALESCE(s.avg_width, 1024))::int) AS datawidth  -- ceil() because avg width has a low bias
      FROM   pg_catalog.pg_class     c
      JOIN   pg_catalog.pg_attribute a ON a.attrelid = c.oid
      JOIN   pg_catalog.pg_stats     s ON s.schemaname = c.relnamespace::regnamespace::text
                                      AND s.tablename  = c.relname
                                      AND s.attname    = a.attname
      WHERE  c.oid = _tbl
      AND    a.attname = ANY(_cols) --  all exist, verified above
      GROUP  BY 1
      ) sub1
   ) sub2;
END
$func$;

CREATE FUNCTION


CREATE TABLE tbl (col1 bigint, col2 bigint, col3 bigint, col4 bigint, txt text);
INSERT INTO tbl
SELECT g
     , CASE WHEN g%2  = 0 THEN g END  -- 50% NULL
     , CASE WHEN g%5  = 0 THEN g END  -- 80% NULL
     , CASE WHEN g%20 = 0 THEN g END  -- 95% NULL
     , g::text
FROM   generate_series (1, 50000) g;

CREATE INDEX col1_idx    ON tbl (col1);
CREATE INDEX col4_idx    ON tbl (col4);
CREATE INDEX col1234_idx ON tbl (col1, col2, col3, col4);
CREATE INDEX t12_idx     ON tbl (txt, col1, col2);  -- few NULLs
CREATE INDEX t34_idx     ON tbl (txt, col3, col4);  -- many NULLs


ANALYZE tbl;  -- !

CREATE TABLE



INSERT 0 50000



CREATE INDEX



CREATE INDEX



CREATE INDEX



CREATE INDEX



CREATE INDEX



ANALYZE


SELECT count(col1) AS col1_notnull
     , count(col2) AS col2_notnull
     , count(col3) AS col3_notnull
     , count(col4) AS col4_notnull
     , count(txt)  AS txt_notnull
FROM   tbl;


col1_notnull
col2_notnull
col3_notnull
col4_notnull
txt_notnull


50000
25000
10000
2500
50000



SELECT 1


SELECT 'actual' AS size
     , pg_relation_size('col1_idx')    AS col1_idx
     , pg_relation_size('col4_idx')    AS col4_idx  -- many NULL but same size as 8 bytes = minimum
     , pg_relation_size('col1234_idx') AS col1234_idx
     , pg_relation_size('t12_idx')     AS t12_idx
     , pg_relation_size('t34_idx')     AS t34_idx
UNION ALL
SELECT 'estimate' AS size
     , f_index_minimum_size('tbl', 'col1')
     , f_index_minimum_size('tbl', 'col4')
     , f_index_minimum_size('tbl', 'col1', 'col2', 'col3', 'col4')
     , f_index_minimum_size('tbl', 'txt', 'col1', 'col2')
     , f_index_minimum_size('tbl', 'txt', 'col3', 'col4')
     
-- size of t34_idx is being underestimated because the *avg* sum of the 3 column sizes is below 8
-- so the function estimates minimum size, while in reality there is a lot more alignment padding.


size
col1_idx
col4_idx
col1234_idx
t12_idx
t34_idx


actual
1138688
1138688
1900544
2039808
1679360

estimate
1015808
1015808
1417216
1826816
1015808



SELECT 2


-- pretty sizes
SELECT 'actual' AS size
     , pg_size_pretty(pg_relation_size('col1_idx'))    AS col1_idx
     , pg_size_pretty(pg_relation_size('col4_idx'))    AS col2_idx
     , pg_size_pretty(pg_relation_size('col1234_idx')) AS col1234_idx
     , pg_size_pretty(pg_relation_size('t12_idx'))     AS t12_idx
     , pg_size_pretty(pg_relation_size('t34_idx'))     AS t34_idx
UNION ALL
SELECT 'estimate' AS size
     , pg_size_pretty(f_index_minimum_size('tbl', 'col1'))
     , pg_size_pretty(f_index_minimum_size('tbl', 'col4'))
     , pg_size_pretty(f_index_minimum_size('tbl', 'col1', 'col2', 'col3', 'col4'))
     , pg_size_pretty(f_index_minimum_size('tbl', 'txt', 'col1', 'col2'))
     , pg_size_pretty(f_index_minimum_size('tbl', 'txt', 'col3', 'col4'));


size
col1_idx
col2_idx
col1234_idx
t12_idx
t34_idx


actual
1112 kB
1112 kB
1856 kB
1992 kB
1640 kB

estimate
992 kB
992 kB
1384 kB
1784 kB
992 kB



SELECT 2
size	col1_idx	col4_idx	col1234_idx	t12_idx	t34_idx
actual	1138688	1138688	1900544	2039808	1679360
estimate	1015808	1015808	1417216	1826816	1015808
size	col1_idx	col2_idx	col1234_idx	t12_idx	t34_idx
actual	1112 kB	1112 kB	1856 kB	1992 kB	1640 kB
estimate	992 kB	992 kB	1384 kB	1784 kB	992 kB