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