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+njnRGcomnxTY6vsEW3wWigc0ruB0EyWFpb+PVVE8sWpw@mail.gmail.com>
 <CAOYmi+k4AyWCQHK=XVF99KVDuFkqxcADao61OWGLxu0nRYMONQ@mail.gmail.com>
 <CA+TgmoZ0x3ym_oueXRWzbM_=6ucKoPZVGj3rRMLBDC_FnetXDw@mail.gmail.com>
In-Reply-To: 
 <CA+TgmoZ0x3ym_oueXRWzbM_=6ucKoPZVGj3rRMLBDC_FnetXDw@mail.gmail.com>
From: Jakub Wartak <jakub.wartak@enterprisedb.com>
Date: Wed, 17 Dec 2025 11:12:40 +0100
Message-ID: 
 <CAKZiRmx538gbg=sV8CQUoR2zf6q5Y3c_1pGBd1A7XONG-5c2eg@mail.gmail.com>
Subject: Re: pg_plan_advice
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jacob Champion <jacob.champion@enterprisedb.com>, Dian Fay <di@nmfay.com>,
	Matheus Alcantara <matheusssilv97@gmail.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/CAKZiRmx538gbg%3DsV8CQUoR2zf6q5Y3c_1pGBd1A7XONG-5c2eg%40mail.gmail.com>
Precedence: bulk

On Mon, Dec 15, 2025 at 9:06=E2=80=AFPM Robert Haas <robertmhaas@gmail.com>=
 wrote:
>
> Here's v7.
[..]

OK, so I've tested today from Your's branch directly, so I hope that
was also v7. Given the following q20 query:

SELECT s_name, s_address
FROM supplier, nation
WHERE s_suppkey in
    (SELECT ps_suppkey
     FROM partsupp
     WHERE ps_partkey in
         (SELECT p_partkey
          FROM part
          WHERE p_name LIKE 'forest%' )
       AND ps_availqty >
         (SELECT 0.5 * sum(l_quantity)
          FROM lineitem
          WHERE l_partkey =3D ps_partkey
            AND l_suppkey =3D ps_suppkey
            AND l_shipdate >=3D DATE '1994-01-01'
            AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' year ) )
  AND s_nationkey =3D n_nationkey
  AND n_name =3D 'CANADA'
ORDER BY s_name;

in normal conditions (w/o advice) the above query generates:

 Sort  (cost=3D1010985030.44..1010985030.59 rows=3D61 width=3D51)
   Sort Key: supplier.s_name
   ->  Nested Loop  (cost=3D0.42..1010985028.63 rows=3D61 width=3D51)
         Join Filter: (nation.n_nationkey =3D supplier.s_nationkey)
         ->  Seq Scan on nation  (cost=3D0.00..1.31 rows=3D1 width=3D4)
               Filter: (n_name =3D 'CANADA'::bpchar)
         ->  Nested Loop Semi Join  (cost=3D0.42..1010985008.29
rows=3D1522 width=3D55)
               Join Filter: (partsupp.ps_suppkey =3D supplier.s_suppkey)
               ->  Seq Scan on supplier  (cost=3D0.00..249.30 rows=3D7730 w=
idth=3D59)
               ->  Materialize  (cost=3D0.42..1010755994.57 rows=3D1973 wid=
th=3D4)
                     ->  Nested Loop  (cost=3D0.42..1010755984.71
rows=3D1973 width=3D4)
                           ->  Seq Scan on part  (cost=3D0.00..4842.25
rows=3D1469 width=3D4)
                                 Filter: ((p_name)::text ~~ 'forest%'::text=
)
                           ->  Index Scan using pk_partsupp on
partsupp  (cost=3D0.42..688053.87 rows=3D1 width=3D8)
                                 Index Cond: (ps_partkey =3D part.p_partkey=
)
                                 Filter: ((ps_availqty)::numeric >
(SubPlan expr_1))
                                 SubPlan expr_1
                                   ->  Aggregate
(cost=3D172009.42..172009.44 rows=3D1 width=3D32)
                                         ->  Seq Scan on lineitem
(cost=3D0.00..172009.42 rows=3D1 width=3D5)
                                               Filter: ((l_shipdate >=3D
'1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp
without time zone) AND (l_partkey =3D partsupp.ps_partkey) AND
(l_suppkey =3D partsupp.ps_suppkey))


 Generated Plan Advice:
   JOIN_ORDER(nation (supplier (part partsupp)))
   NESTED_LOOP_PLAIN(partsupp partsupp) <--- [X]
   NESTED_LOOP_MATERIALIZE(partsupp)
   SEQ_SCAN(nation supplier part lineitem@expr_1)
   INDEX_SCAN(partsupp public.pk_partsupp)
   SEMIJOIN_NON_UNIQUE((partsupp part))
   NO_GATHER(supplier nation partsupp part lineitem@expr_1)

Please see the - I think it's confusing? -
NESTED_LOOP_MATERIALIZE(partsupp partsupp) - that's 2x the same
string? This causes it to turn into below plan -- I've marked the
problem with [X]

 Sort  (cost=3D50035755.50..50035755.66 rows=3D61 width=3D51)
   Sort Key: supplier.s_name
   ->  Nested Loop  (cost=3D12562154.32..50035753.70 rows=3D61 width=3D51)
         Join Filter: (nation.n_nationkey =3D supplier.s_nationkey)
         ->  Seq Scan on nation  (cost=3D0.00..1.31 rows=3D1 width=3D4)
               Filter: (n_name =3D 'CANADA'::bpchar)
         ->  Nested Loop Semi Join  (cost=3D12562154.32..50035733.36
rows=3D1522 width=3D55)
             [X] -- missing Join Filter here
               ->  Seq Scan on supplier  (cost=3D0.00..249.30 rows=3D7730 w=
idth=3D59)
               [X] -- HJ instead of Materialize+Nested Loop below:
               ->  Hash Join  (cost=3D12562154.32..12567002.09 rows=3D1 wid=
th=3D4)
                     Hash Cond: (part.p_partkey =3D partsupp.ps_partkey)
                     ->  Seq Scan on part  (cost=3D0.00..4842.25
rows=3D1469 width=3D4)
                           Filter: ((p_name)::text ~~ 'forest%'::text)
                     ->  Hash  (cost=3D12562154.02..12562154.02 rows=3D24 w=
idth=3D8)
                           ->  Index Scan using pk_partsupp on
partsupp  (cost=3D0.42..12562154.02 rows=3D24 width=3D8)
                                 [X] -- wrong Index Cond below
(suppkey instead of partkey)
                                 Index Cond: (ps_suppkey =3D supplier.s_sup=
pkey)
                                 Filter: ((ps_availqty)::numeric >
(SubPlan expr_1))
                                 SubPlan expr_1
                                   ->  Aggregate
(cost=3D172009.42..172009.44 rows=3D1 width=3D32)
                                         ->  Seq Scan on lineitem
(cost=3D0.00..172009.42 rows=3D1 width=3D5)
                                               Filter: ((l_shipdate >=3D
'1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp
without time zone) AND (l_partkey =3D partsupp.ps_partkey) AND
(l_suppkey =3D partsupp.ps_suppkey))

Supplied Plan Advice:
   SEQ_SCAN(nation) /* matched */
   SEQ_SCAN(supplier) /* matched */
   SEQ_SCAN(part) /* matched */
   SEQ_SCAN(lineitem@expr_1) /* matched */
   INDEX_SCAN(partsupp public.pk_partsupp) /* matched */
   JOIN_ORDER(nation (supplier (part partsupp))) /* matched, conflicting */
   NESTED_LOOP_PLAIN(partsupp) /* matched, conflicting */
   NESTED_LOOP_PLAIN(partsupp) /* matched, conflicting */
   NESTED_LOOP_MATERIALIZE(partsupp) /* matched, conflicting, failed */
   SEMIJOIN_NON_UNIQUE((partsupp part)) /* matched, conflicting */
   NO_GATHER(supplier) /* matched */
   NO_GATHER(nation) /* matched */
   NO_GATHER(partsupp) /* matched */
   NO_GATHER(part) /* matched */
   NO_GATHER(lineitem@expr_1) /* matched */

So the difference is basically between:
    set pg_plan_advice.advice =3D '[..] NESTED_LOOP_PLAIN(partsupp
partsupp) NESTED_LOOP_MATERIALIZE(partsupp) [..]';
which causes wrong plan and outcome:
    NESTED_LOOP_MATERIALIZE(partsupp) /* matched, conflicting, failed */

and apparently proper advice like below which has better yield:
    set pg_plan_advice.advice =3D '[..] NESTED_LOOP_PLAIN(part partsupp)
NESTED_LOOP_MATERIALIZE(partsupp) [..]';
which is not generated , but caused good plan, however it also prints:
   NESTED_LOOP_PLAIN(part) /* matched, conflicting, failed */
   NESTED_LOOP_MATERIALIZE(partsupp) /* matched, conflicting */
but that seems "failed" there, seems to be untrue?

Another idea is perhaps, we could have some elog(WARNING) - but not
Asserts() - in assert-only enabled build that could alert us in case
of duplicated entries being detected for the same ops in
pg_plan_advice_explain_feedback()?

-J.