MIME-Version: 1.0
References: <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com>
 <CAKZiRmxtJAFG7e1+Vs9B8ngON=AOzJbuws+1ZeH4LsbJh5AzoQ@mail.gmail.com>
 <CA+TgmoY9Ne_Sh10u6LSPc3wvOQPLp3kF9nBp3nqJEG2JGF2QiA@mail.gmail.com>
 <CA+Tgmoa57S6mP=aTOXH2-gDAL4TMO1WbGgrHSg0s6J4zUH=04g@mail.gmail.com>
 <DEB1SAGVDX7S.JMV96X4R0RCS@gmail.com> <CA+Tgmoaf__2B0BUL+vrg28P+3buX=Ti-kybqkHiLTtFrrCfzuA@mail.gmail.com>
 <CA+TgmoYpcLNOuypOTdgCSLW7FuA=t6BtB3meTARHX2-Dj_81xQ@mail.gmail.com>
 <DEFNP3WX7BLX.1X7DS1P9X6JIF@nmfay.com> <CA+TgmoZjv9OyFu1Gkt78w0vWEti8S33w8trYHmErf-GMmGSi=w@mail.gmail.com>
 <DELPC4B56Y5A.3OBZ3WCH3T4VX@nmfay.com> <CA+TgmoaOSBQD9Ux4eG40w723ZN=c0J7p-+oX4+J8urUeyLMo5w@mail.gmail.com>
 <CAOYmi+=g+MMoOpWkk2weXWKJcKH0eKey8gKHHdH0dF4Tiawrhw@mail.gmail.com>
 <CA+TgmobwaT=PXPDDrgDup+jA8KHBbkxigtziD-zNzAKKkQYVgQ@mail.gmail.com>
 <CAOYmi+mOmEW=amDRQMfw6-Fb3ZmDEQFaJzwk8Bc8W8DzaP85XQ@mail.gmail.com>
 <CA+TgmoaX2AMW4cdFM3OngBJxmxpkdmzF33R7-CWhvRLfucbFMg@mail.gmail.com>
 <CAOYmi+k4AyWCQHK=XVF99KVDuFkqxcADao61OWGLxu0nRYMONQ@mail.gmail.com>
 <CA+TgmoZ0x3ym_oueXRWzbM_=6ucKoPZVGj3rRMLBDC_FnetXDw@mail.gmail.com>
 <CAP53Pkycc=7N2bLzVT3x+qE1JamvRZWev5tFjdLJ1+-AV3Di+Q@mail.gmail.com>
 <CA+TgmoaKhuD91RnazbRyGkmP7--JdNq8oNDC3UcgTZSWbMxC7w@mail.gmail.com>
 <CAP53Pkw5-wMEeDJXFmqo_RTyL_spzCXb7HHKrbSnQqokVoZcNQ@mail.gmail.com>
 <CA+Tgmob-69bzbdi3U_QtebqAf6u1y8js=5=oNK639csVe1VbhA@mail.gmail.com>
 <CA+TgmoaZMOikxK=LqS+Jn+835h9S139JLGk-3LyETVXw5W5j=w@mail.gmail.com>
 <1136161.1769654478@sss.pgh.pa.us> <CAP53PkwZ1ZTMARKg6iEfAw9qzBhkjBitj-9gr_Jvy7k2AwGgWA@mail.gmail.com>
 <CAMbWs4--NuEUFE_xTo991TRXaZryE29jarJPDnVxoaQOYdt7tA@mail.gmail.com>
 <CA+TgmobzR+XMGbRosVPbjHbSo4+cgJn=qZK6w05aF1sbj=C+9Q@mail.gmail.com>
 <CA+TgmoawzvCoZAwFS85tE5+c8vBkqgcS8ZstQ_ohjXQ9wGT9sw@mail.gmail.com>
 <CA+TgmoYS4ZCVAF2jTce=bMP0Oq_db_srocR4cZyO0OBp9oUoGg@mail.gmail.com>
 <CAK98qZ2RzbgCHrSg4zLkvpzyBam_X6te-KF8w1+_vON9BAVMEw@mail.gmail.com> <CA+TgmoaCdsuvNn6T6SfQ_0YD2Hh2+hgTXh9fTGHQhPg1zvy2rQ@mail.gmail.com>
In-Reply-To: <CA+TgmoaCdsuvNn6T6SfQ_0YD2Hh2+hgTXh9fTGHQhPg1zvy2rQ@mail.gmail.com>
From: Ajay Pal <ajay.pal.k@gmail.com>
Date: Thu, 12 Feb 2026 17:11:25 +0530
Message-ID: <CABRHmyuOhEjPSpej424UyridA9_knDcBEyL3_BVB1u=yDSOTKw@mail.gmail.com>
Subject: Re: pg_plan_advice
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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CABRHmyuOhEjPSpej424UyridA9_knDcBEyL3_BVB1u%3DyDSOTKw%40mail.gmail.com>
Precedence: bulk

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 =3D 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 =3D on;
SET geqo_threshold =3D 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 =3D t2.id AND t2.id =3D t3.id AND t3.id =3D t4.id AND t4.id =3D=
 t5.id
  AND t5.id =3D t6.id AND t6.id =3D t7.id AND t7.id =3D t8.id AND t8.id =3D=
 t9.id
  AND t9.id =3D t10.id AND t10.id =3D t11.id AND t11.id =3D t12.id AND
t12.id =3D t13.id;

--3. SET pg_plan_advice.advice =3D '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=E2=80=AFAM Robert Haas <robertmhaas@gmail.com>=
 wrote:
>
> On Mon, Feb 9, 2026 at 10:55=E2=80=AFAM Alexandra Wang
> <alexandra.wang.oss@gmail.com> wrote:
> > On Sat, Feb 7, 2026 at 9:38=E2=80=AFAM Robert Haas <robertmhaas@gmail.c=
om> 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@ma=
il.gmail.com
> for discussion of those patches.
>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com