Re: pg_plan_advice

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(at)gmail(dot)com> wrote:
>
> On Mon, Feb 9, 2026 at 10:55 AM Alexandra Wang
> <alexandra(dot)wang(dot)oss(at)gmail(dot)com> wrote:
> > On Sat, Feb 7, 2026 at 9:38 AM Robert Haas <robertmhaas(at)gmail(dot)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