Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE t1 (
    e int2    -- 6 bytes of padding after int2
  , a int8
  , f int2    -- 6 bytes of padding after int2
  , b int8
  , g int2    -- 6 bytes of padding after int2
  , c int8
  , h int2    -- 6 bytes of padding after int2
  , d int8);

INSERT INTO t1 VALUES (1,1,1,1,1,1,1,1);

CREATE TABLE t2 (
    a int8
  , b int8
  , c int8
  , d int8
  , e int2
  , f int2
  , g int2
  , h int2);

INSERT INTO t2 VALUES (1,1,1,1,1,1,1,1);

CREATE TABLE

INSERT 0 1

CREATE TABLE

INSERT 0 1

SELECT pg_column_size(t1) AS not_optimized FROM t1;

not_optimized

88

SELECT 1

SELECT pg_column_size(t2) AS optimized FROM t2;

optimized

64

SELECT 1

SELECT pg_column_size('{1}'::int[]) AS single_int4_plus_array_overhead;

single_int4_plus_array_overhead

28

SELECT 1