public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ajay Pal <[email protected]>
To: Robert Haas <[email protected]>
Cc: Alexandra Wang <[email protected]>
Cc: Richard Guo <[email protected]>
Cc: Lukas Fittl <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Jacob Champion <[email protected]>
Cc: Dian Fay <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: Jakub Wartak <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: pg_plan_advice
Date: Thu, 12 Feb 2026 17:11:25 +0530
Message-ID: <CABRHmyuOhEjPSpej424UyridA9_knDcBEyL3_BVB1u=yDSOTKw@mail.gmail.com> (raw)
In-Reply-To: <CA+TgmoaCdsuvNn6T6SfQ_0YD2Hh2+hgTXh9fTGHQhPg1zvy2rQ@mail.gmail.com>
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>
<[email protected]>
<CA+Tgmoaf__2B0BUL+vrg28P+3buX=Ti-kybqkHiLTtFrrCfzuA@mail.gmail.com>
<CA+TgmoYpcLNOuypOTdgCSLW7FuA=t6BtB3meTARHX2-Dj_81xQ@mail.gmail.com>
<[email protected]>
<CA+TgmoZjv9OyFu1Gkt78w0vWEti8S33w8trYHmErf-GMmGSi=w@mail.gmail.com>
<[email protected]>
<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>
<[email protected]>
<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>
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 <[email protected]> wrote:
>
> On Mon, Feb 9, 2026 at 10:55 AM Alexandra Wang
> <[email protected]> wrote:
> > On Sat, Feb 7, 2026 at 9:38 AM Robert Haas <[email protected]> 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
view thread (143+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: pg_plan_advice
In-Reply-To: <CABRHmyuOhEjPSpej424UyridA9_knDcBEyL3_BVB1u=yDSOTKw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox