Re: pg_plan_advice

On Wed, Dec 17, 2025 at 5:12 AM Jakub Wartak
<jakub.wartak@enterprisedb.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@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