Horton
Menu

Re: pg_plan_advice

Ajay Pal <ajay.pal.k@gmail.com>

View thread
From: Ajay Pal <ajay.pal.k@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Alexandra Wang <alexandra.wang.oss@gmail.com>, Richard Guo <guofenglinux@gmail.com>, Lukas Fittl <lukas@fittl.com>, Tom Lane <tgl@sss.pgh.pa.us>, Jacob Champion <jacob.champion@enterprisedb.com>, Dian Fay <di@nmfay.com>, Matheus Alcantara <matheusssilv97@gmail.com>, Jakub Wartak <jakub.wartak@enterprisedb.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2026-02-12T11:41:25Z
Lists: pgsql-hackers 
Hi,

pg_plan_advice failed to match JOIN_ORDER advice because the genetic
algorithm never attempts the specific join path requested.

Test SQL:

LOAD 'pg_plan_advice';
SET pg_plan_advice.always_explain_supplied_advice = on;

-- Create enough tables to trigger GEQO (default threshold is 12)
CREATE TABLE t1 (id int); CREATE TABLE t2 (id int); CREATE TABLE t3 (id int);
CREATE TABLE t4 (id int); CREATE TABLE t5 (id int); CREATE TABLE t6 (id int);
CREATE TABLE t7 (id int); CREATE TABLE t8 (id int); CREATE TABLE t9 (id int);
CREATE TABLE t10 (id int); CREATE TABLE t11 (id int); CREATE TABLE t12 (id int);
CREATE TABLE t13 (id int);

-- 1. Force GEQO on
SET geqo = on;
SET geqo_threshold = 12;

-- 2. Run a massive join. Verify if advice is generated.
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13
WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id AND t4.id = t5.id
  AND t5.id = t6.id AND t6.id = t7.id AND t7.id = t8.id AND t8.id = t9.id
  AND t9.id = t10.id AND t10.id = t11.id AND t11.id = t12.id AND
t12.id = t13.id;

--3. SET pg_plan_advice.advice = 'JOIN_ORDER(t13 (t5 (t12 (t1 (t6 (t9
(t11 (t10 (t2 (t7 (t4 (t8 t3))))))))))))';
--4. Run Query again

Supplied Plan Advice:
   JOIN_ORDER(t13 (t5 (t12 (t1 (t6 (t9 (t11 (t10 (t2 (t7 (t4 (t8
t3)))))))))))) /* matched, failed */
 Generated Plan Advice:
   JOIN_ORDER(t13 (t5 (t12 (t8 t9 t1 t10 t3 t4 t6 t7 t2 t11))))
   NESTED_LOOP_PLAIN(t9 t1 t10 t3 t4 t6 t7 t2 t11)
   HASH_JOIN((t1 t2 t3 t4 t6 t7 t8 t9 t10 t11) (t1 t2 t3 t4 t6 t7 t8 t9 t10 t11
    t12) (t1 t2 t3 t4 t5 t6 t7 t8 t9 t10 t11 t12))
   SEQ_SCAN(t13 t5 t12 t8 t9 t1 t10 t3 t4 t6 t7 t2 t11)
   NO_GATHER(t1 t2 t3 t4 t5 t6 t7 t8 t9 t10 t11 t12 t13)

Thanks
Ajay

On Wed, Feb 11, 2026 at 4:36 AM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Mon, Feb 9, 2026 at 10:55 AM Alexandra Wang
> <alexandra.wang.oss@gmail.com> wrote:
> > On Sat, Feb 7, 2026 at 9:38 AM Robert Haas <robertmhaas@gmail.com> wrote:
> > > Here is a new patch set (v14).
> >
> > Thanks for the patches! 0003 - 0005 look good to me.
>
> I have committed those, as well as 0001 and 0002. Here's v15. The main
> patch is now 0002, and has the following changes since the last
> version:
>
> - Added a new GUC pg_plan_advice.feedback_warnings, disabled by
> default, which can be set to true to produce a warning about plan
> advice strings that aren't fully working. (Previously, you had to use
> EXPLAIN to get this information.)
>
> - Use get_namespace_name_or_temp, rather than get_name_namespace,
> consistently. One use of the latter function crept in, breaking
> INDEX_SCAN and INDEX_ONLY_SCAN advice for temporary tables.
>
> - Fix a problem in pgpa_scan.c that could cause spurious NO_GATHER
> advice to be generated in certain situations, such as when joins were
> proven empty.
>
> - Fix a logic error in the handling of JOIN_ORDER advice that could
> cause it to be marked as conflicting with PARTITIONWISE advice when
> that was not in reality the case.
>
> - Incorporate documentation corrections from David G. Johnston. I
> didn't take all of his suggestions, but I took many of them, sometimes
> with some additional wordsmithing on my part.
>
> - Remove a stray comment.
>
> Also a reminder that 0003 and 0004 (previously 0008 and 0009) don't
> properly belong to this thread, but I've included them here because
> otherwise the tests in the last patch don't pass. See
> http://postgr.es/m/CA+TgmobRufbUSksBoxytGJS1P+mQY4rWctCk-d0iAUO6-k9Wrg@mail.gmail.com
> for discussion of those patches.
>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. pg_plan_advice: Fix another unique-semijoin bug.

  2. pg_plan_advice: Export feedback-related definitions.

  3. pg_plan_advice: Fix a bug when a subquery is pruned away entirely.

  4. pg_plan_advice: Add alternatives test to Makefile.

  5. pg_plan_advice: Handle non-repeatable TABLESAMPLE scans.

  6. pg_stash_advice: Allow stashed advice to be persisted to disk.

  7. Add pg_stash_advice contrib module.

  8. pg_plan_advice: Avoid assertion failure with partitionwise aggregate.

  9. pg_plan_advice: Invent DO_NOT_SCAN(relation_identifier).

  10. Add an alternative_plan_name field to PlannerInfo.

  11. pg_plan_advice: Refactor to invent pgpa_planner_info

  12. Respect disabled_nodes in fix_alternative_subplan.

  13. get_memoize_path: Don't exit quickly when PGS_NESTLOOP_PLAIN is unset.

  14. test_plan_advice: Set TAP test priority 50 in meson.build.

  15. pg_plan_advice: Avoid a crash under GEQO.

  16. Test pg_plan_advice using a new test_plan_advice module.

  17. pg_plan_advice: Always install pg_plan_advice.h, and in the right place

  18. pg_plan_advice: Fix failures to accept identifier keywords.

  19. Add pg_plan_advice contrib module.

  20. Allow extensions to mark an individual index as disabled.

  21. Replace get_relation_info_hook with build_simple_rel_hook.

  22. Store information about Append node consolidation in the final plan.

  23. Store information about elided nodes in the final plan.

  24. Store information about range-table flattening in the final plan.

  25. Pass cursorOptions to planner_setup_hook.

  26. Fix PGS_CONSIDER_NONPARTIAL interaction with Materialize nodes.

  27. Fix mistakes in commit 4020b370f214315b8c10430301898ac21658143f

  28. Allow for plugin control over path generation strategies.

  29. Update some comments for fasthash

  30. Allow passing a pointer to GetNamedDSMSegment()'s init callback.

  31. Don't reset the pathlist of partitioned joinrels.

  32. Treat number of disabled nodes in a path as a separate cost metric.