Re: Generating "Subplan Removed" in EXPLAIN - Mailing list pgsql-hackers

From Yugo NAGATA
Subject Re: Generating "Subplan Removed" in EXPLAIN
Date
Msg-id 20230201115334.8fa2ad4e4d6f691ebf0711f7@sraoss.co.jp
Whole thread Raw
In response to Re: Generating "Subplan Removed" in EXPLAIN  (Justin Pryzby <pryzby@telsasoft.com>)
Responses Re: Generating "Subplan Removed" in EXPLAIN
Re: Generating "Subplan Removed" in EXPLAIN
List pgsql-hackers
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>



pgsql-hackers by date:

Previous
From: vignesh C
Date:
Subject: Re: [Commitfest 2023-01] has started
Next
From: Bruce Momjian
Date:
Subject: Re: Generating "Subplan Removed" in EXPLAIN