On Wed, Dec 17, 2025 at 5:12 AM Jakub Wartak <[email protected]> wrote: > Sort (cost=1010985030.44..1010985030.59 rows=61 width=51) > Sort Key: supplier.s_name > -> Nested Loop (cost=0.42..1010985028.63 rows=61 width=51) > Join Filter: (nation.n_nationkey = supplier.s_nationkey) > -> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4) > Filter: (n_name = 'CANADA'::bpchar) > -> Nested Loop Semi Join (cost=0.42..1010985008.29 > rows=1522 width=55) > Join Filter: (partsupp.ps_suppkey = supplier.s_suppkey) > -> Seq Scan on supplier (cost=0.00..249.30 rows=7730 > width=59) > -> Materialize (cost=0.42..1010755994.57 rows=1973 width=4) > -> Nested Loop (cost=0.42..1010755984.71 > rows=1973 width=4) > -> Seq Scan on part (cost=0.00..4842.25 > rows=1469 width=4) > Filter: ((p_name)::text ~~ 'forest%'::text) > -> Index Scan using pk_partsupp on > partsupp (cost=0.42..688053.87 rows=1 width=8) > Index Cond: (ps_partkey = part.p_partkey) > Filter: ((ps_availqty)::numeric > > (SubPlan expr_1)) > SubPlan expr_1 > -> Aggregate > (cost=172009.42..172009.44 rows=1 width=32) > -> Seq Scan on lineitem > (cost=0.00..172009.42 rows=1 width=5) > Filter: ((l_shipdate >= > '1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp > without time zone) AND (l_partkey = partsupp.ps_partkey) AND > (l_suppkey = partsupp.ps_suppkey)) > > > Generated Plan Advice: > JOIN_ORDER(nation (supplier (part partsupp))) > NESTED_LOOP_PLAIN(partsupp partsupp) <--- [X] > NESTED_LOOP_MATERIALIZE(partsupp) > SEQ_SCAN(nation supplier part lineitem@expr_1) > INDEX_SCAN(partsupp public.pk_partsupp) > SEMIJOIN_NON_UNIQUE((partsupp part)) > NO_GATHER(supplier nation partsupp part lineitem@expr_1)
Yeah, that's not right. There are three nested loops here, so we should have three pieces of nested loop advice. NESTED_LOOP_MATERIALIZE(partsupp) covers the innermost nested loop. The other two are NESTED_LOOP_PLAIN, but the advice should cover all the tables on the inner side of the join. I think it should read: NESTED_LOOP_PLAIN((part partsupp) (supplier part partsupp)) Ordering isn't significant here, so NESTED_LOOP_PLAIN((part supplier partsupp) (partsupp part)) would be logically equivalent. Doesn't matter exactly what we output here, but it shouldn't be just partsupp. > and apparently proper advice like below which has better yield: > set pg_plan_advice.advice = '[..] NESTED_LOOP_PLAIN(part partsupp) This isn't quite what you want, because this says that part should be on the outer side of a NESTED_LOOP_PLAIN by itself and partsupp should also be on the outer side of a NESTED_LOOP_PLAIN by itself. You need the extra set of parentheses to indicate that the join product of those two tables should be on the outer side of a NESTED_LOOP_PLAIN, rather than each table individually. What must be happening here is that either pgpa_join.c (maybe with complicity from pgpa_walker.c) is not populating the pgpa_plan_walker_context's join_strategies[JSTRAT_NESTED_LOOP_PLAIN] member correctly, or else pgpa_output.c is not serializing it to text correctly. I suspect the former is a more likely but I'm not sure exactly what's happening. -- Robert Haas EDB: http://www.enterprisedb.com
