Re: BUG #19493: Assertion failure in pg_plan_advice with EXISTS subquery and DO_NOT_SCAN advice

Hi, all

Tender Wang <tndrwang@gmail.com> 于2026年5月27日周三 09:28写道：
>
> Hi, all
>
> Tender Wang <tndrwang@gmail.com> 于2026年5月27日周三 09:17写道：
> >
> > Tom Lane <tgl@sss.pgh.pa.us> 于2026年5月27日周三 09:08写道：
> > >
> > > =?UTF-8?B?0JrQsNC70LjQvdC40L0g0J3QuNC60LjRgtCw?= <n.kalinin@postgrespro.ru> writes:
> > > > Could this be related to GCC? I'm using GCC 16.1.1 and I can reproduce 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="-O0 -g3". And my
> > 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 ==
> PGPA_TARGET_IDENTIFIER);
> -                                       pgpa_trove_add_to_slice(&trove->scan,
> -
>                  item->tag, target);
> +                                       if (target->ttype ==
> PGPA_TARGET_IDENTIFIER)
> +
> pgpa_trove_add_to_slice(&trove->scan,
> +
>                          item->tag, target);
> +                                       else
> +                                       {
> +                                               Assert(target->ttype
> == 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 ==
PGPA_TARGET_IDENTIFIER);
                                        pgpa_trove_add_to_slice(&trove->scan,

                 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=# EXPLAIN
SELECT *
FROM a
WHERE EXISTS (
    SELECT 1
    FROM b
    WHERE b.i = a.i
);
                              QUERY PLAN
-----------------------------------------------------------------------
 Hash Join  (cost=46.38..102.75 rows=1275 width=4)
   Hash Cond: (a.i = b.i)
   ->  Seq Scan on a  (cost=0.00..35.50 rows=2550 width=4)
         Disabled: true
   ->  Hash  (cost=43.88..43.88 rows=200 width=4)
         ->  HashAggregate  (cost=41.88..43.88 rows=200 width=4)
               Group Key: b.i
               ->  Seq Scan on b  (cost=0.00..35.50 rows=2550 width=4)
 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