-- q8 failure summary: JOIN_ORDER(n1 region customer orders lineitem part supplier n2) /* matched, conflicting, failed */ NESTED_LOOP_PLAIN(part) /* matched, conflicting, failed */ NESTED_LOOP_PLAIN(supplier) /* matched, conflicting, failed */ --- /tmp/plan 2026-01-14 08:03:36.360974545 +0100 +++ /tmp/planadviced 2026-01-14 08:03:36.362974547 +0100 @@ -3,8 +3,10 @@ -> Sort Sort Key: (EXTRACT(year FROM orders.o_orderdate)) -> Nested Loop - -> Nested Loop - -> Nested Loop + -> Hash Join + Hash Cond: (lineitem.l_partkey = part.p_partkey) + -> Hash Join + Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Nested Loop -> Nested Loop -> Nested Loop @@ -23,10 +25,10 @@ Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate <= '1996-12-31'::date)) -> Index Scan using pk_lineitem on lineitem Index Cond: (l_orderkey = orders.o_orderkey) + -> Hash + -> Index Scan using pk_supplier on supplier + -> Hash -> Index Scan using pk_part on part - Index Cond: (p_partkey = lineitem.l_partkey) Filter: ((p_type)::text = 'ECONOMY ANODIZED STEEL'::text) - -> Index Scan using pk_supplier on supplier - Index Cond: (s_suppkey = lineitem.l_suppkey) -> Index Scan using pk_nation on nation n2 Index Cond: (n_nationkey = supplier.s_nationkey) -- -- manual repro details: -- dbt3=# explain (costs off, plan_advice) SELECT o_year, sum(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END) / sum(volume) AS mkt_share FROM ( SELECT extract(year FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) AS volume, n2.n_name AS nation FROM part, supplier, lineitem, orders, customer, nation n1, nation n2, region WHERE p_partkey = l_partkey AND s_suppkey = l_suppkey AND l_orderkey = o_orderkey AND o_custkey = c_custkey AND c_nationkey = n1.n_nationkey AND n1.n_regionkey = r_regionkey AND r_name = 'AMERICA' AND s_nationkey = n2.n_nationkey AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31' AND p_type = 'ECONOMY ANODIZED STEEL' ) AS all_nations GROUP BY o_year ORDER BY o_year; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- GroupAggregate Group Key: (EXTRACT(year FROM orders.o_orderdate)) -> Sort Sort Key: (EXTRACT(year FROM orders.o_orderdate)) -> Nested Loop -> Nested Loop -> Nested Loop -> Nested Loop -> Nested Loop -> Nested Loop -> Hash Join Hash Cond: (n1.n_regionkey = region.r_regionkey) -> Seq Scan on nation n1 -> Hash -> Seq Scan on region Filter: (r_name = 'AMERICA'::bpchar) -> Bitmap Heap Scan on customer Recheck Cond: (c_nationkey = n1.n_nationkey) -> Bitmap Index Scan on customer_c_nationkey_c_custkey_idx Index Cond: (c_nationkey = n1.n_nationkey) -> Index Scan using orders_o_custkey_idx on orders Index Cond: (o_custkey = customer.c_custkey) Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate <= '1996-12-31'::date)) -> Index Scan using pk_lineitem on lineitem Index Cond: (l_orderkey = orders.o_orderkey) -> Index Scan using pk_part on part Index Cond: (p_partkey = lineitem.l_partkey) Filter: ((p_type)::text = 'ECONOMY ANODIZED STEEL'::text) -> Index Scan using pk_supplier on supplier Index Cond: (s_suppkey = lineitem.l_suppkey) -> Index Scan using pk_nation on nation n2 Index Cond: (n_nationkey = supplier.s_nationkey) Generated Plan Advice: JOIN_ORDER(n1 region customer orders lineitem part supplier n2) NESTED_LOOP_PLAIN(customer orders lineitem part supplier n2) HASH_JOIN(region) SEQ_SCAN(n1 region) BITMAP_HEAP_SCAN(customer public.customer_c_nationkey_c_custkey_idx) INDEX_SCAN(orders public.orders_o_custkey_idx lineitem public.pk_lineitem part public.pk_part supplier public.pk_supplier n2 public.pk_nation) NO_GATHER(part supplier lineitem orders customer n1 n2 region) -- copy paste because why not? dbt3=# set pg_plan_advice.advice = 'JOIN_ORDER(n1 region customer orders lineitem part supplier n2) NESTED_LOOP_PLAIN(customer orders lineitem part supplier n2) HASH_JOIN(region) SEQ_SCAN(n1 region) BITMAP_HEAP_SCAN(customer public.customer_c_nationkey_c_custkey_idx) INDEX_SCAN(orders public.orders_o_custkey_idx lineitem public.pk_lineitem part public.pk_part supplier public.pk_supplier n2 public.pk_nation) NO_GATHER(part supplier lineitem orders customer n1 n2 region)'; SET -- -- 3x failures while adapting from advice, 2x for NESTED_LOOP_PLAIN -- causing HJ instead of NL -- dbt3=# explain (costs off) SELECT o_year, sum(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END) / sum(volume) AS mkt_share FROM ( SELECT extract(year FROM o_orderdate) AS o_year, l_extendedprice * (1 - l_discount) AS volume, n2.n_name AS nation FROM part, supplier, lineitem, orders, customer, nation n1, nation n2, region WHERE p_partkey = l_partkey AND s_suppkey = l_suppkey AND l_orderkey = o_orderkey AND o_custkey = c_custkey AND c_nationkey = n1.n_nationkey AND n1.n_regionkey = r_regionkey AND r_name = 'AMERICA' AND s_nationkey = n2.n_nationkey AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31' AND p_type = 'ECONOMY ANODIZED STEEL' ) AS all_nations GROUP BY o_year ORDER BY o_year; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- GroupAggregate Group Key: (EXTRACT(year FROM orders.o_orderdate)) -> Sort Sort Key: (EXTRACT(year FROM orders.o_orderdate)) -> Nested Loop -> Hash Join Hash Cond: (lineitem.l_partkey = part.p_partkey) -> Hash Join Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Nested Loop -> Nested Loop -> Nested Loop -> Hash Join Hash Cond: (n1.n_regionkey = region.r_regionkey) -> Seq Scan on nation n1 -> Hash -> Seq Scan on region Filter: (r_name = 'AMERICA'::bpchar) -> Bitmap Heap Scan on customer Recheck Cond: (c_nationkey = n1.n_nationkey) -> Bitmap Index Scan on customer_c_nationkey_c_custkey_idx Index Cond: (c_nationkey = n1.n_nationkey) -> Index Scan using orders_o_custkey_idx on orders Index Cond: (o_custkey = customer.c_custkey) Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate <= '1996-12-31'::date)) -> Index Scan using pk_lineitem on lineitem Index Cond: (l_orderkey = orders.o_orderkey) -> Hash -> Index Scan using pk_supplier on supplier -> Hash -> Index Scan using pk_part on part Filter: ((p_type)::text = 'ECONOMY ANODIZED STEEL'::text) -> Index Scan using pk_nation on nation n2 Index Cond: (n_nationkey = supplier.s_nationkey) Supplied Plan Advice: SEQ_SCAN(n1) /* matched */ SEQ_SCAN(region) /* matched */ BITMAP_HEAP_SCAN(customer public.customer_c_nationkey_c_custkey_idx) /* matched */ INDEX_SCAN(orders public.orders_o_custkey_idx) /* matched */ INDEX_SCAN(lineitem public.pk_lineitem) /* matched */ INDEX_SCAN(part public.pk_part) /* matched */ INDEX_SCAN(supplier public.pk_supplier) /* matched */ INDEX_SCAN(n2 public.pk_nation) /* matched */ JOIN_ORDER(n1 region customer orders lineitem part supplier n2) /* matched, conflicting, failed */ NESTED_LOOP_PLAIN(customer) /* matched, conflicting */ NESTED_LOOP_PLAIN(orders) /* matched, conflicting */ NESTED_LOOP_PLAIN(lineitem) /* matched, conflicting */ NESTED_LOOP_PLAIN(part) /* matched, conflicting, failed */ NESTED_LOOP_PLAIN(supplier) /* matched, conflicting, failed */ NESTED_LOOP_PLAIN(n2) /* matched, conflicting */ HASH_JOIN(region) /* matched, conflicting */ NO_GATHER(part) /* matched */ NO_GATHER(supplier) /* matched */ NO_GATHER(lineitem) /* matched */ NO_GATHER(orders) /* matched */ NO_GATHER(customer) /* matched */ NO_GATHER(n1) /* matched */ NO_GATHER(n2) /* matched */ NO_GATHER(region) /* matched */ (59 rows) -- of course after manual stats gathering the advices are different: -- dbt3=# analyze ; ANALYZE dbt3=# explain (costs off, plan_advice) SELECT [..] Generated Plan Advice: JOIN_ORDER(n1 region customer orders lineitem supplier part n2) NESTED_LOOP_PLAIN(customer orders lineitem n2) HASH_JOIN(region supplier part) SEQ_SCAN(n1 region) BITMAP_HEAP_SCAN(customer public.customer_c_nationkey_c_custkey_idx) INDEX_SCAN(orders public.orders_o_custkey_idx lineitem public.pk_lineitem supplier public.pk_supplier part public.pk_part n2 public.pk_nation) NO_GATHER(part supplier lineitem orders customer n1 n2 region) (68 rows) -- diff of advices: ----- JOIN_ORDER is the same (just different sort), ----- of course more in HJ less in NL advice, so the question is still why we couldn't use the plan we generated initially --- /tmp/no-stats 2026-01-14 10:39:27.697249882 +0100 +++ /tmp/after-stats 2026-01-14 10:39:16.224234561 +0100 @@ -1,9 +1,9 @@ Generated Plan Advice: - JOIN_ORDER(n1 region customer orders lineitem part supplier n2) - NESTED_LOOP_PLAIN(customer orders lineitem part supplier n2) - HASH_JOIN(region) + JOIN_ORDER(n1 region customer orders lineitem supplier part n2) + NESTED_LOOP_PLAIN(customer orders lineitem n2) + HASH_JOIN(region supplier part) SEQ_SCAN(n1 region) BITMAP_HEAP_SCAN(customer public.customer_c_nationkey_c_custkey_idx) INDEX_SCAN(orders public.orders_o_custkey_idx lineitem public.pk_lineitem - part public.pk_part supplier public.pk_supplier n2 public.pk_nation) + supplier public.pk_supplier part public.pk_part n2 public.pk_nation) NO_GATHER(part supplier lineitem orders customer n1 n2 region) -- so even with TRUNCATE pg_statistics the advice is different than the initial one () -- so what makes it generate so wildly different plan advice if in theory both situation is the same? --- /tmp/no-stats 2026-01-14 10:39:27.697249882 +0100 +++ /tmp/after-stats-after-trunc-pg_stats 2026-01-14 10:43:33.864576258 +0100 @@ -1,9 +1,9 @@ Generated Plan Advice: - JOIN_ORDER(n1 region customer orders lineitem part supplier n2) - NESTED_LOOP_PLAIN(customer orders lineitem part supplier n2) - HASH_JOIN(region) - SEQ_SCAN(n1 region) + JOIN_ORDER(orders (n1 region customer) lineitem supplier n2 part) + NESTED_LOOP_PLAIN(customer lineitem supplier part) + HASH_JOIN(region (customer n1 region) n2) + SEQ_SCAN(orders n1 region n2) BITMAP_HEAP_SCAN(customer public.customer_c_nationkey_c_custkey_idx) - INDEX_SCAN(orders public.orders_o_custkey_idx lineitem public.pk_lineitem - part public.pk_part supplier public.pk_supplier n2 public.pk_nation) - NO_GATHER(part supplier lineitem orders customer n1 n2 region) + INDEX_SCAN(lineitem public.pk_lineitem supplier public.pk_supplier part + public.pk_part) + GATHER((part supplier lineitem orders customer n1 n2 region)) -- so if press harder and do: -- 1. ANALYZE; -- makes problem go away -- 2. TRUNCATE pg_statisitc + reconnect -- problem still not reproducible -- 3. (new)select pg_clear_relation_stats('public', relname) from pg_class where relnamespace = 2200 and relkind = 'r'; -- the problem is AGAIN reproducible