Postgres 11

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 zodiac (zodiac_id "char" PRIMARY KEY, zodiac text UNIQUE NOT NULL);
INSERT INTO zodiac VALUES
  ('a', '♈ ARIES')
, ('b', '♉ TAURUS')
, ('c', '♊ GEMINI')
, ('d', '♋ CANCER')
, ('e', '♌ LEO')
, ('f', '♍ VIRGO')
, ('g', '♎ LIBRA')
, ('h', '♏ SCORPIO')
, ('i', '♐ SAGITTARIUS')
, ('j', '♑ CAPRICORN')
, ('k', '♒ AQUARIUS')
, ('l', '♓ PISCES');

CREATE TYPE zodiac_enum AS ENUM (
  '♈ ARIES'
, '♉ TAURUS'
, '♊ GEMINI'
, '♋ CANCER'
, '♌ LEO'
, '♍ VIRGO'
, '♎ LIBRA'
, '♏ SCORPIO'
, '♐ SAGITTARIUS'
, '♑ CAPRICORN'
, '♒ AQUARIUS'
, '♓ PISCES');

-- For the purpose of this test, I just use 12 zodic columns.
-- Real use cases would have different columns!

CREATE TABLE t_surrogate (
  id serial PRIMARY KEY
, z1 "char" REFERENCES zodiac
, z2 "char" REFERENCES zodiac
, z3 "char" REFERENCES zodiac
, z4 "char" REFERENCES zodiac
, z5 "char" REFERENCES zodiac
, z6 "char" REFERENCES zodiac
, z7 "char" REFERENCES zodiac
, z8 "char" REFERENCES zodiac
, z9 "char" REFERENCES zodiac
, za "char" REFERENCES zodiac
, zb "char" REFERENCES zodiac
, zc "char" REFERENCES zodiac
);

CREATE TABLE t_natural (
  id serial PRIMARY KEY
, z1 text REFERENCES zodiac(zodiac)
, z2 text REFERENCES zodiac(zodiac)
, z3 text REFERENCES zodiac(zodiac)
, z4 text REFERENCES zodiac(zodiac)
, z5 text REFERENCES zodiac(zodiac)
, z6 text REFERENCES zodiac(zodiac)
, z7 text REFERENCES zodiac(zodiac)
, z8 text REFERENCES zodiac(zodiac)
, z9 text REFERENCES zodiac(zodiac)
, za text REFERENCES zodiac(zodiac)
, zb text REFERENCES zodiac(zodiac)
, zc text REFERENCES zodiac(zodiac)
);

CREATE TABLE t_enum (
  id serial PRIMARY KEY
, z1 zodiac_enum
, z2 zodiac_enum
, z3 zodiac_enum
, z4 zodiac_enum
, z5 zodiac_enum
, z6 zodiac_enum
, z7 zodiac_enum
, z8 zodiac_enum
, z9 zodiac_enum
, za zodiac_enum
, zb zodiac_enum
, zc zodiac_enum
);

-- random values
INSERT INTO t_surrogate(z1,z2,z3,z4,z5,z6,z7,z8,z9,za,zb,zc)
SELECT (97 + trunc(random() * 12)::int)::"char"
     , (97 + trunc(random() * 12)::int)::"char"
     , (97 + trunc(random() * 12)::int)::"char"
     , (97 + trunc(random() * 12)::int)::"char"
     , (97 + trunc(random() * 12)::int)::"char"
     , (97 + trunc(random() * 12)::int)::"char"
     , (97 + trunc(random() * 12)::int)::"char"
     , (97 + trunc(random() * 12)::int)::"char"
     , (97 + trunc(random() * 12)::int)::"char"
     , (97 + trunc(random() * 12)::int)::"char"
     , (97 + trunc(random() * 12)::int)::"char"
     , (97 + trunc(random() * 12)::int)::"char"
FROM   generate_series(1, 100000);   -- 100k rows, go easy on fiddle

INSERT INTO t_natural(z1,z2,z3,z4,z5,z6,z7,z8,z9,za,zb,zc)
SELECT z1.zodiac
     , z2.zodiac
     , z3.zodiac
     , z4.zodiac
     , z5.zodiac
     , z6.zodiac
     , z7.zodiac
     , z8.zodiac
     , z9.zodiac
     , za.zodiac
     , zb.zodiac
     , zc.zodiac
FROM   t_surrogate s
JOIN   zodiac z1 ON s.z1 = z1.zodiac_id
JOIN   zodiac z2 ON s.z2 = z2.zodiac_id
JOIN   zodiac z3 ON s.z3 = z3.zodiac_id
JOIN   zodiac z4 ON s.z4 = z4.zodiac_id
JOIN   zodiac z5 ON s.z5 = z5.zodiac_id
JOIN   zodiac z6 ON s.z6 = z6.zodiac_id
JOIN   zodiac z7 ON s.z7 = z7.zodiac_id
JOIN   zodiac z8 ON s.z8 = z8.zodiac_id
JOIN   zodiac z9 ON s.z9 = z9.zodiac_id
JOIN   zodiac za ON s.za = za.zodiac_id
JOIN   zodiac zb ON s.zb = zb.zodiac_id
JOIN   zodiac zc ON s.zc = zc.zodiac_id;

INSERT INTO t_enum(z1,z2,z3,z4,z5,z6,z7,z8,z9,za,zb,zc)
SELECT z1::zodiac_enum,z2::zodiac_enum,z3::zodiac_enum,z4::zodiac_enum,z5::zodiac_enum,z6::zodiac_enum,z7::zodiac_enum,z8::zodiac_enum,z9::zodiac_enum,za::zodiac_enum,zb::zodiac_enum,zc::zodiac_enum
FROM   t_natural;
12 rows affected

100000 rows affected

100000 rows affected

100000 rows affected

VACUUM ANALYZE zodiac;
VACUUM ANALYZE t_enum;
VACUUM ANALYZE t_natural;
VACUUM ANALYZE t_surrogate;
SELECT avg(pg_column_size(n)) FROM t_natural n;    -- 165 bytes


avg


165.0074000000000000


SELECT avg(pg_column_size(e)) FROM t_enum e;       --  76 bytes


avg


75.9999700000000000


SELECT avg(pg_column_size(s)) FROM t_surrogate s;  --  40 bytes


avg


39.9999700000000000


EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT s.id
     , z1.zodiac
     , z2.zodiac
     , z3.zodiac
     , z4.zodiac
     , z5.zodiac
     , z6.zodiac
     , z7.zodiac
     , z8.zodiac
     , z9.zodiac
     , za.zodiac
     , zb.zodiac
     , zc.zodiac
FROM   t_surrogate s
JOIN   zodiac z1 ON s.z1 = z1.zodiac_id
JOIN   zodiac z2 ON s.z2 = z2.zodiac_id
JOIN   zodiac z3 ON s.z3 = z3.zodiac_id
JOIN   zodiac z4 ON s.z4 = z4.zodiac_id
JOIN   zodiac z5 ON s.z5 = z5.zodiac_id
JOIN   zodiac z6 ON s.z6 = z6.zodiac_id
JOIN   zodiac z7 ON s.z7 = z7.zodiac_id
JOIN   zodiac z8 ON s.z8 = z8.zodiac_id
JOIN   zodiac z9 ON s.z9 = z9.zodiac_id
JOIN   zodiac za ON s.za = za.zodiac_id
JOIN   zodiac zb ON s.zb = zb.zodiac_id
JOIN   zodiac zc ON s.zc = zc.zodiac_id;


QUERY PLAN


Hash Join (actual rows=100000 loops=1)

  Hash Cond: (s.zc = zc.zodiac_id)

  ->  Hash Join (actual rows=100000 loops=1)

        Hash Cond: (s.zb = zb.zodiac_id)

        ->  Hash Join (actual rows=100000 loops=1)

              Hash Cond: (s.za = za.zodiac_id)

              ->  Hash Join (actual rows=100000 loops=1)

                    Hash Cond: (s.z9 = z9.zodiac_id)

                    ->  Hash Join (actual rows=100000 loops=1)

                          Hash Cond: (s.z8 = z8.zodiac_id)

                          ->  Hash Join (actual rows=100000 loops=1)

                                Hash Cond: (s.z7 = z7.zodiac_id)

                                ->  Hash Join (actual rows=100000 loops=1)

                                      Hash Cond: (s.z6 = z6.zodiac_id)

                                      ->  Hash Join (actual rows=100000 loops=1)

                                            Hash Cond: (s.z5 = z5.zodiac_id)

                                            ->  Hash Join (actual rows=100000 loops=1)

                                                  Hash Cond: (s.z4 = z4.zodiac_id)

                                                  ->  Hash Join (actual rows=100000 loops=1)

                                                        Hash Cond: (s.z3 = z3.zodiac_id)

                                                        ->  Hash Join (actual rows=100000 loops=1)

                                                              Hash Cond: (s.z2 = z2.zodiac_id)

                                                              ->  Hash Join (actual rows=100000 loops=1)

                                                                    Hash Cond: (s.z1 = z1.zodiac_id)

                                                                    ->  Seq Scan on t_surrogate s (actual rows=100000 loops=1)

                                                                    ->  Hash (actual rows=12 loops=1)

                                                                          Buckets: 1024  Batches: 1  Memory Usage: 9kB

                                                                          ->  Seq Scan on zodiac z1 (actual rows=12 loops=1)

                                                              ->  Hash (actual rows=12 loops=1)

                                                                    Buckets: 1024  Batches: 1  Memory Usage: 9kB

                                                                    ->  Seq Scan on zodiac z2 (actual rows=12 loops=1)

                                                        ->  Hash (actual rows=12 loops=1)

                                                              Buckets: 1024  Batches: 1  Memory Usage: 9kB

                                                              ->  Seq Scan on zodiac z3 (actual rows=12 loops=1)

                                                  ->  Hash (actual rows=12 loops=1)

                                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB

                                                        ->  Seq Scan on zodiac z4 (actual rows=12 loops=1)

                                            ->  Hash (actual rows=12 loops=1)

                                                  Buckets: 1024  Batches: 1  Memory Usage: 9kB

                                                  ->  Seq Scan on zodiac z5 (actual rows=12 loops=1)

                                      ->  Hash (actual rows=12 loops=1)

                                            Buckets: 1024  Batches: 1  Memory Usage: 9kB

                                            ->  Seq Scan on zodiac z6 (actual rows=12 loops=1)

                                ->  Hash (actual rows=12 loops=1)

                                      Buckets: 1024  Batches: 1  Memory Usage: 9kB

                                      ->  Seq Scan on zodiac z7 (actual rows=12 loops=1)

                          ->  Hash (actual rows=12 loops=1)

                                Buckets: 1024  Batches: 1  Memory Usage: 9kB

                                ->  Seq Scan on zodiac z8 (actual rows=12 loops=1)

                    ->  Hash (actual rows=12 loops=1)

                          Buckets: 1024  Batches: 1  Memory Usage: 9kB

                          ->  Seq Scan on zodiac z9 (actual rows=12 loops=1)

              ->  Hash (actual rows=12 loops=1)

                    Buckets: 1024  Batches: 1  Memory Usage: 9kB

                    ->  Seq Scan on zodiac za (actual rows=12 loops=1)

        ->  Hash (actual rows=12 loops=1)

              Buckets: 1024  Batches: 1  Memory Usage: 9kB

              ->  Seq Scan on zodiac zb (actual rows=12 loops=1)

  ->  Hash (actual rows=12 loops=1)

        Buckets: 1024  Batches: 1  Memory Usage: 9kB

        ->  Seq Scan on zodiac zc (actual rows=12 loops=1)

Planning Time: 3.829 ms

Execution Time: 274.220 ms


EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM t_natural;


QUERY PLAN


Seq Scan on t_natural (actual rows=100000 loops=1)

Planning Time: 0.082 ms

Execution Time: 17.921 ms


EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT * FROM t_enum;


QUERY PLAN


Seq Scan on t_enum (actual rows=100000 loops=1)

Planning Time: 0.059 ms

Execution Time: 7.988 ms