Pager usage is off. version -------------------------------------------------------------------------------------------------------------------- PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (1 row) name | setting ---------------------+--------- enable_sort | on hash_mem_multiplier | 2 work_mem | 4096 (3 rows) psql:/repro/repro.sql:12: NOTICE: 00000: table "t0" does not exist, skipping LOCATION: DropErrorMsgNonExistent, tablecmds.c:1506 DROP TABLE CREATE TABLE == case 1: recursive UNION, inflated estimate (default settings) You are now connected to database "postgres" as user "postgres". SET QUERY PLAN ---------------------------------------------------------------------------------------------- Aggregate (cost=1902920129880.20..1902920129880.21 rows=1 width=8) CTE r -> Recursive Union (cost=0.00..951563739254.98 rows=42282506250010 width=16) -> Nested Loop (cost=0.00..528738676754.88 rows=42282506250000 width=16) -> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12) -> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8) -> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4) -> Materialize (cost=0.00..48.25 rows=2550 width=4) -> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4) -> Materialize (cost=0.00..48.25 rows=2550 width=4) -> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4) -> Materialize (cost=0.00..48.25 rows=2550 width=4) -> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4) -> Result (cost=0.00..0.00 rows=0 width=0) One-Time Filter: false -> CTE Scan on r (cost=0.00..845650125000.20 rows=42282506250010 width=0) JIT: Functions: 14 Options: Inlining true, Optimization true, Expressions true, Deforming true (19 rows) count ------- 0 (1 row) peak_rss_mb ------------- 112 (1 row) == control 1: same query, UNION ALL (no hash table) You are now connected to database "postgres" as user "postgres". count ------- 0 (1 row) peak_rss_mb ------------- 95 (1 row) == case 2: recursive UNION, accurate estimate of 1M rows You are now connected to database "postgres" as user "postgres". count --------- 1000000 (1 row) peak_rss_mb ------------- 184 (1 row) == control 2: same rows, UNION ALL You are now connected to database "postgres" as user "postgres". count --------- 1000000 (1 row) peak_rss_mb ------------- 105 (1 row) == case 3: hashed EXCEPT chosen while disabled (enable_sort = off) You are now connected to database "postgres" as user "postgres". SET SET QUERY PLAN -------------------------------------------------------------------------------------- HashSetOp Except (cost=951563739254.90..951567739254.90 rows=1600000000 width=16) Disabled: true -> Nested Loop (cost=0.00..528738676754.88 rows=42282506250000 width=16) -> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12) -> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8) -> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4) -> Materialize (cost=0.00..48.25 rows=2550 width=4) -> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4) -> Materialize (cost=0.00..48.25 rows=2550 width=4) -> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4) -> Materialize (cost=0.00..48.25 rows=2550 width=4) -> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4) -> Result (cost=0.00..0.01 rows=1 width=16) JIT: Functions: 11 Options: Inlining true, Optimization true, Expressions true, Deforming true (16 rows) c0 | c0 | c0 | c0 ----+----+----+---- (0 rows) peak_rss_mb ------------- 76 (1 row) == control 3: same query, default settings (sorted SetOp) You are now connected to database "postgres" as user "postgres". QUERY PLAN -------------------------------------------------------------------------------------------- SetOp Except (cost=15879164969983.26..16407700298108.28 rows=1600000000 width=16) -> Sort (cost=15879164969983.26..15984871235608.26 rows=42282506250000 width=16) Sort Key: a.c0, b.c0, c.c0, d.c0 -> Nested Loop (cost=0.00..528738676754.88 rows=42282506250000 width=16) -> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12) -> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8) -> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4) -> Materialize (cost=0.00..48.25 rows=2550 width=4) -> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4) -> Materialize (cost=0.00..48.25 rows=2550 width=4) -> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4) -> Materialize (cost=0.00..48.25 rows=2550 width=4) -> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4) -> Result (cost=0.00..0.01 rows=1 width=16) JIT: Functions: 8 Options: Inlining true, Optimization true, Expressions true, Deforming true (17 rows) c0 | c0 | c0 | c0 ----+----+----+---- (0 rows) peak_rss_mb ------------- 68 (1 row) == case 4: hashed EXCEPT, default settings, hash-only type (xid) You are now connected to database "postgres" as user "postgres". QUERY PLAN -------------------------------------------------------------------------------------- HashSetOp Except (cost=1374388801754.90..1374392801754.90 rows=1600000000 width=16) Disabled: true -> Nested Loop (cost=0.00..951563739254.88 rows=42282506250000 width=16) -> Nested Loop (cost=0.00..207348588.00 rows=16581375000 width=12) -> Nested Loop (cost=0.00..81358.62 rows=6502500 width=8) -> Seq Scan on t0 a (cost=0.00..35.50 rows=2550 width=4) -> Materialize (cost=0.00..48.25 rows=2550 width=4) -> Seq Scan on t0 b (cost=0.00..35.50 rows=2550 width=4) -> Materialize (cost=0.00..48.25 rows=2550 width=4) -> Seq Scan on t0 c (cost=0.00..35.50 rows=2550 width=4) -> Materialize (cost=0.00..48.25 rows=2550 width=4) -> Seq Scan on t0 d (cost=0.00..35.50 rows=2550 width=4) -> Result (cost=0.00..0.01 rows=1 width=16) JIT: Functions: 11 Options: Inlining true, Optimization true, Expressions true, Deforming true (16 rows) c0 | c0 | c0 | c0 ----+----+----+---- (0 rows) peak_rss_mb ------------- 76 (1 row) == server log (errors and backtraces)