Thread

1. pg_plan_advice: add NO_ scan and join method tags

   Florin Irion <irionr@gmail.com> — 2026-07-02T16:35:24Z

   Hello,
   
   The attached patch adds negative scan- and join-method advice to
   pg_plan_advice. These tags are the complement of the existing
   positive ones:
   
      NO_SEQ_SCAN, NO_INDEX_SCAN, NO_INDEX_ONLY_SCAN,
      NO_BITMAP_HEAP_SCAN, NO_TID_SCAN
   
      NO_HASH_JOIN, NO_NESTED_LOOP (+ _PLAIN/_MATERIALIZE/_MEMOIZE),
      NO_MERGE_JOIN (+ _PLAIN/_MATERIALIZE)
   
   Each NO_ tag forbids the corresponding strategy for the named
   relation, so the planner is free to choose any other one that is
   eligible. In many cases this is more convenient than listing all the
   methods you want, when in fact you only need to exclude a single one.
   
   Some points about the design:
   
   - The NO_ join tags keep the same join-order constraint as their
   positive counterparts. NO_HASH_JOIN(x) means "use hash join or
   merge join with x on the inner side", so x cannot become the
   driving relation.
   - Several NO_ tags for the same relation can be combined without
   problem. A NO_ tag and a positive tag for the same method are in
   conflict (both are marked as conflicting and neither is applied); a
   NO_ tag and a positive tag for different methods are compatible.
   - The scan NO_ tags use simple_target_list (no sublists), and the
   join NO_ tags use generic_target_list, exactly as their positive
   counterparts do.
   
   Regression tests (no_scan, no_join) and documentation are included.
   The patch applies on master and is pgindent-clean.
   
   I would be glad to receive any feedback.
   
   Thank you,
   Florin

2. Re: pg_plan_advice: add NO_ scan and join method tags

   Florin Irion <irionr@gmail.com> — 2026-07-03T13:32:56Z

   Hello,
   
   Adding v2 as it v1 doesn't apply anymore.
   
   
   Cheers,
   Florin
   www.enterprisedb.com
   
   

3. Re: pg_plan_advice: add NO_ scan and join method tags

   solai v <solai.cdac@gmail.com> — 2026-07-07T07:23:48Z

   Hi Florin,
   I tested the patch on current master, and it worked well in my testing.
   Before applying the patch, advice such as NO_SEQ_SCAN(t1) was rejected
   with a parser error because the NO_ scan and join tags were not
   supported.
   After applying the patch, the new NO_ tags were accepted successfully.
   I verified that:
   NO_SEQ_SCAN prevented the planner from using a Sequential Scan when
   another eligible scan method was available.
   NO_HASH_JOIN caused the planner to choose an alternative join method.
   Multiple NO_ advice tags worked as expected.
   Conflicting positive and negative advice for the same method was
   detected correctly.
   Positive and negative advice for different methods worked together as expected.
   I also ran the regression tests, and everything completed successfully.
   
   Thanks for working on this enhancement.
   
   Regards
   
   solai