q8_nl_instead_of_hj.txt
text/plain
-- 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