Re: pg_plan_advice

On Wed, Dec 17, 2025 at 5:12 AM Jakub Wartak
<jakub(dot)wartak(at)enterprisedb(dot)com> 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(at)expr_1)
> INDEX_SCAN(partsupp public.pk_partsupp)
> SEMIJOIN_NON_UNIQUE((partsupp part))
> NO_GATHER(supplier nation partsupp part lineitem(at)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