On Tue, 31 Jan 2023 20:38:21 -0600
Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> To: Bruce Momjian <bruce@momjian.us>
> Cc: pgsql-hackers@postgresql.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@sraoss.co.jp>