MIME-Version: 1.0
References: <19493-5878eac7a2525c23@postgresql.org>
 <2d59d7d6-6afe-4565-8ff7-ae764651589a@gmail.com>
 <ahY_s6GLOGoceXHT@paquier.xyz> <1779843554.737126560@fmail2.qdit>
 <418106.1779844099@sss.pgh.pa.us>
 <CAHewXNn7q9Bm=i=ZBMJxUsmFhavfdxwbwVzdhsV9uXZOk584HQ@mail.gmail.com>
 <CAHewXN=n_KzrOFgHyZwSHPuaXF-RRMig0o4yL+knoSE-_cMMPA@mail.gmail.com>
In-Reply-To: 
 <CAHewXN=n_KzrOFgHyZwSHPuaXF-RRMig0o4yL+knoSE-_cMMPA@mail.gmail.com>
From: Tender Wang <tndrwang@gmail.com>
Date: Wed, 27 May 2026 11:50:09 +0800
Message-ID: 
 <CAHewXNkHsjOjaWUcdtrGTWeHK8f1N8=L434O0b9ecgtGaMFQrg@mail.gmail.com>
Subject: Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS
 subquery and DO_NOT_SCAN advice
To: pgsql-bugs@lists.postgresql.org
Cc: =?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?=
 <n.kalinin@postgrespro.ru>,
	Michael Paquier <michael@paquier.xyz>,
 Pierre Forstmann <pierre.forstmann@gmail.com>,
	Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAHewXNkHsjOjaWUcdtrGTWeHK8f1N8%3DL434O0b9ecgtGaMFQrg%40mail.gmail.com>
Precedence: bulk

Hi, all

Tender Wang <tndrwang@gmail.com> =E4=BA=8E2026=E5=B9=B45=E6=9C=8827=E6=97=
=A5=E5=91=A8=E4=B8=89 09:28=E5=86=99=E9=81=93=EF=BC=9A
>
> Hi, all
>
> Tender Wang <tndrwang@gmail.com> =E4=BA=8E2026=E5=B9=B45=E6=9C=8827=E6=97=
=A5=E5=91=A8=E4=B8=89 09:17=E5=86=99=E9=81=93=EF=BC=9A
> >
> > Tom Lane <tgl@sss.pgh.pa.us> =E4=BA=8E2026=E5=B9=B45=E6=9C=8827=E6=97=
=A5=E5=91=A8=E4=B8=89 09:08=E5=86=99=E9=81=93=EF=BC=9A
> > >
> > > =3D?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?=3D <n.kalinin@postg=
respro.ru> writes:
> > > > Could this be related to GCC? I'm using GCC 16.1.1 and I can reprod=
uce the crash with that version.
> > >
> > > Maybe.  Does it still fail if you set the optimization level to -O0 ?
> > I can reproduce this crash on my machine with CFLAGS=3D"-O0 -g3". And m=
y
> > GCC version is 11.4.0
> diff --git a/contrib/pg_plan_advice/pgpa_trove.c
> b/contrib/pg_plan_advice/pgpa_trove.c
> index ca69f3bd3df..0d15af1cbba 100644
> --- a/contrib/pg_plan_advice/pgpa_trove.c
> +++ b/contrib/pg_plan_advice/pgpa_trove.c
> @@ -179,9 +179,18 @@ pgpa_build_trove(List *advice_items)
>                                          * but in the future this
> might not be true, e.g. a custom
>                                          * scan could replace a join.
>                                          */
> -                                       Assert(target->ttype =3D=3D
> PGPA_TARGET_IDENTIFIER);
> -                                       pgpa_trove_add_to_slice(&trove->s=
can,
> -
>                  item->tag, target);
> +                                       if (target->ttype =3D=3D
> PGPA_TARGET_IDENTIFIER)
> +
> pgpa_trove_add_to_slice(&trove->scan,
> +
>                          item->tag, target);
> +                                       else
> +                                       {
> +                                               Assert(target->ttype
> =3D=3D PGPA_TARGET_ORDERED_LIST);
> +
> foreach_ptr(pgpa_advice_target, child_target, target->children)
> +                                               {
> +
> pgpa_trove_add_to_slice(&trove->scan,
> +
>                          item->tag, child_target);
> +                                               }
> +                                       }
>                                 }
>
> I tried the above fix, and no crash again.

I find an easier way as follows:
diff --git a/contrib/pg_plan_advice/pgpa_trove.c
b/contrib/pg_plan_advice/pgpa_trove.c
index ca69f3bd3df..64af4b1435b 100644
--- a/contrib/pg_plan_advice/pgpa_trove.c
+++ b/contrib/pg_plan_advice/pgpa_trove.c
@@ -179,7 +179,6 @@ pgpa_build_trove(List *advice_items)
                                         * but in the future this
might not be true, e.g. a custom
                                         * scan could replace a join.
                                         */
-                                       Assert(target->ttype =3D=3D
PGPA_TARGET_IDENTIFIER);
                                        pgpa_trove_add_to_slice(&trove->sca=
n,

                 item->tag, target);
                                }

Just remove the Assert, then it works as well.
The previous fix is not ok, because the output of explain is not the
same as the user input:

Supplied Plan Advice:
   DO_NOT_SCAN(a) /* matched, failed */

We should get  DO_NOT_SCAN((a))

The new fix will get what we want:
postgres=3D# EXPLAIN
SELECT *
FROM a
WHERE EXISTS (
    SELECT 1
    FROM b
    WHERE b.i =3D a.i
);
                              QUERY PLAN
-----------------------------------------------------------------------
 Hash Join  (cost=3D46.38..102.75 rows=3D1275 width=3D4)
   Hash Cond: (a.i =3D b.i)
   ->  Seq Scan on a  (cost=3D0.00..35.50 rows=3D2550 width=3D4)
         Disabled: true
   ->  Hash  (cost=3D43.88..43.88 rows=3D200 width=3D4)
         ->  HashAggregate  (cost=3D41.88..43.88 rows=3D200 width=3D4)
               Group Key: b.i
               ->  Seq Scan on b  (cost=3D0.00..35.50 rows=3D2550 width=3D4=
)
 Supplied Plan Advice:
   DO_NOT_SCAN((a)) /* matched, failed */
(10 rows)

In pgpa_identifier_matches_target(), if it is not the
PGPA_TARGET_IDENTIFIER, we will check all descendants.
The original comments may need to be adjusted.
I added Robert to the cc list. He knows more about pg_plan_advice than I.


--
Thanks,
Tender Wang