Pager usage is off. version ---------------------------------------------------------------------------------------------------------------------- PostgreSQL 17.11 (Debian 17.11-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:1436 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: 13 Options: Inlining true, Optimization true, Expressions true, Deforming true (19 rows) count ------- 0 (1 row) peak_rss_mb ------------- 107 (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 ------------- 94 (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 ------------- 206 (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 ------------- 102 (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 ------------------------------------------------------------------------------------------------------------ SetOp Except (cost=17679564844013.52..18208096172138.54 rows=1600000000 width=20) -> Sort (cost=17679564844013.52..17785271109638.52 rows=42282506250001 width=20) Sort Key: "*SELECT* 1".c0, "*SELECT* 1".c0_1, "*SELECT* 1".c0_2, "*SELECT* 1".c0_3 -> Append (cost=0.00..1162976270504.90 rows=42282506250001 width=20) -> Subquery Scan on "*SELECT* 1" (cost=0.00..951563739254.88 rows=42282506250000 width=20) -> 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) -> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=20) -> Result (cost=0.00..0.01 rows=1 width=16) JIT: Functions: 11 Options: Inlining true, Optimization true, Expressions true, Deforming true (20 rows) c0 | c0 | c0 | c0 ----+----+----+---- (0 rows) peak_rss_mb ------------- 95 (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=17669564844013.52..18198096172138.54 rows=1600000000 width=20) -> Sort (cost=17669564844013.52..17775271109638.52 rows=42282506250001 width=20) Sort Key: "*SELECT* 1".c0, "*SELECT* 1".c0_1, "*SELECT* 1".c0_2, "*SELECT* 1".c0_3 -> Append (cost=0.00..1162976270504.90 rows=42282506250001 width=20) -> Subquery Scan on "*SELECT* 1" (cost=0.00..951563739254.88 rows=42282506250000 width=20) -> 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) -> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=20) -> Result (cost=0.00..0.01 rows=1 width=16) JIT: Functions: 11 Options: Inlining true, Optimization true, Expressions true, Deforming true (20 rows) c0 | c0 | c0 | c0 ----+----+----+---- (0 rows) peak_rss_mb ------------- 95 (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=0.00..2008626395504.91 rows=1600000000 width=20) -> Append (cost=0.00..1585801333004.90 rows=42282506250001 width=20) -> Subquery Scan on "*SELECT* 1" (cost=0.00..1374388801754.88 rows=42282506250000 width=20) -> 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) -> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=20) -> Result (cost=0.00..0.01 rows=1 width=16) JIT: Functions: 12 Options: Inlining true, Optimization true, Expressions true, Deforming true (18 rows) c0 | c0 | c0 | c0 ----+----+----+---- (0 rows) peak_rss_mb ------------- 108 (1 row) == server log (errors and backtraces)