clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 582702 fiddles created (13301 in the last week).

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
 hidden batch(es)


VACUUM ANALYZE zodiac;
 hidden batch(es)


VACUUM ANALYZE t_enum;
 hidden batch(es)


VACUUM ANALYZE t_natural;
 hidden batch(es)


VACUUM ANALYZE t_surrogate;
 hidden batch(es)


SELECT avg(pg_column_size(n)) FROM t_natural n; -- 165 bytes
avg
165.0001400000000000
 hidden batch(es)


SELECT avg(pg_column_size(e)) FROM t_enum e; -- 76 bytes
avg
75.9999700000000000
 hidden batch(es)


SELECT avg(pg_column_size(s)) FROM t_surrogate s; -- 40 bytes
avg
39.9999700000000000
 hidden batch(es)


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: 2.943 ms
Execution Time: 222.473 ms
 hidden batch(es)


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.043 ms
Execution Time: 11.401 ms
 hidden batch(es)


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.042 ms
Execution Time: 5.699 ms
 hidden batch(es)