Re: pg_plan_advice

Thank you Robert for clarification.

On Tue, Jan 27, 2026 at 6:02 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Tue, Jan 27, 2026 at 2:49 AM Ajay Pal <ajay(dot)pal(dot)k(at)gmail(dot)com> wrote:
> > #1 Grouped Hash Join, This forces the join of dim1 and dim2 to happen
> > first, and then places that resulting set on the inner side of a Hash
> > Join against fact.
> > but the planner partially matches the generated advice.
> >
> > -- We want (dim1 JOIN dim2) to be the inner side of a Hash Join
> > SET LOCAL pg_plan_advice.advice = 'HASH_JOIN((dim1 dim2))';
> >
> > postgres=*# EXPLAIN (COSTS OFF, PLAN_ADVICE)
> > SELECT * FROM fact
> > JOIN dim1 ON fact.d1_id = dim1.id
> > JOIN dim2 ON fact.d2_id = dim2.id;
> > QUERY PLAN
> > -----------------------------------------------------------
> > Nested Loop
> > Disabled: true
> > -> Nested Loop
> > Disabled: true
> > -> Seq Scan on fact
> > -> Index Scan using dim1_pkey on dim1
> > Index Cond: (id = fact.d1_id)
> > -> Index Scan using dim2_pkey on dim2
> > Index Cond: (id = fact.d2_id)
> > Supplied Plan Advice:
> > HASH_JOIN((dim1 dim2)) /* partially matched */
> > Generated Plan Advice:
> > JOIN_ORDER(fact dim1 dim2)
> > NESTED_LOOP_PLAIN(dim1 dim2)
> > SEQ_SCAN(fact)
> > INDEX_SCAN(dim1 public.dim1_pkey dim2 public.dim2_pkey)
> > NO_GATHER(fact dim1 dim2)
> > (17 rows)
>
> Thanks for the report, but this is actually correct behavior. There's
> no join clause between dim1 and dim2, so the planner doesn't consider
> a dim1-dim2 join. This is a good example of the phenomenon described
> in the documentation: you can't force the planner to create an
> arbitrary plan that it wouldn't otherwise have considered. I might
> tweak the documentation wording a little to try to mention that this
> is another way "partially matched" can happen, but there's no bug
> here.
>
> > #2 Multiple Instances of Same Table in Subqueries, here target the
> > second instance of dim1 inside the subquery 'sq'. both seq_scan and
> > index_scan advices are not matching.
> >
> > SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(dim1#2(at)sq)
> > INDEX_SCAN(dim1(at)sq dim1_pkey)';
> >
> > postgres=*# EXPLAIN (COSTS OFF, PLAN_ADVICE)
> > SELECT * FROM fact
> > JOIN (
> > SELECT a.id FROM dim1 a
> > JOIN dim1 b ON a.id = b.id
> > OFFSET 0
> > ) sq ON fact.d1_id = sq.id;
> > QUERY PLAN
> > ---------------------------------------------------
> > Hash Join
> > Hash Cond: (fact.d1_id = b.id)
> > -> Seq Scan on fact
> > -> Hash
> > -> Seq Scan on dim1 b
> > Supplied Plan Advice:
> > SEQ_SCAN(dim1#2(at)sq) /* not matched */
> > INDEX_SCAN(dim1(at)sq dim1_pkey) /* not matched */
> > Generated Plan Advice:
> > JOIN_ORDER(fact sq)
> > HASH_JOIN(sq)
> > SEQ_SCAN(b(at)sq fact)
> > NO_GATHER(fact b(at)sq)
> > (13 rows)
>
> I'm not sure what why you expected this to work. You can see what the
> correct relation identifiers are from the generated plan advice, and
> you've used something else, so it doesn't match. It's documented in
> both the SGML documentation and the README that relation identifiers
> are based on the relation alias, not the relation name.
>
> In general, this seems like a good to reiterate that this is first and
> foremost a plan stability feature. More than anything, these examples
> show that if you try to write your own plan advice from scratch to
> force a novel plan that the planner has never produced itself, you may
> not have much luck. If you do want to try to produce a novel plan, you
> should at least look at the generated plan advice and adapt it instead
> of starting from scratch. And if you find, when trying to produce a
> novel plan, that it doesn't work, you need to consider the possibility
> that this is because the optimizer did not ever consider that plan,
> and that is why pg_plan_advice is unable to induce the planner to
> prefer it. That's not to say there can't be any remaining bugs in
> pg_plan_advice; there probably are. But it also is absolutely not a
> "write your own plan and do anything you like" feature.
>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com