Re: Generating "Subplan Removed" in EXPLAIN

On Tue, 31 Jan 2023 20:38:21 -0600
Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

>
> To: Bruce Momjian <bruce(at)momjian(dot)us>
> Cc: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: Generating "Subplan Removed" in EXPLAIN
> Date: Tue, 31 Jan 2023 20:38:21 -0600
> User-Agent: Mutt/1.9.4 (2018-02-28)
>
> On Tue, Jan 31, 2023 at 08:59:57PM -0500, Bruce Momjian wrote:
> > Does anyone know how to generate this? Thanks.
>
> The regression tests know:
>
> $ git grep -c 'Subplans Removed' ./src/test/regress/
> src/test/regr

Maybe, you missed to set plan_cache_mode to force_generic_plan.
"Subplan Removed" doesn't appear when using a custom plan.

postgres=# set enable_indexonlyscan = off;
SET
postgres=# prepare ab_q1 (int, int, int) as
select * from ab where a between $1 and $2 and b <= $3;
PREPARE
postgres=# explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
Filter: ((a >= 2) AND (a <= 2) AND (b <= 3))
-> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
Filter: ((a >= 2) AND (a <= 2) AND (b <= 3))
-> Seq Scan on ab_a2_b3 ab_3 (actual rows=0 loops=1)
Filter: ((a >= 2) AND (a <= 2) AND (b <= 3))
(7 rows)

postgres=# show plan_cache_mode ;
plan_cache_mode
-----------------
auto
(1 row)

postgres=# set plan_cache_mode to force_generic_plan;
SET
postgres=# explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
Subplans Removed: 6
-> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a2_b3 ab_3 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
(8 rows)

Regards,
Yugo Nagata

--
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>