Обсуждение: Generating "Subplan Removed" in EXPLAIN
Our document states that EXPLAIN can generate "Subplan Removed":
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION-PRUNING
It is possible to determine the number of partitions which were removed
during this phase by observing the “Subplans Removed” property in the
EXPLAIN output.
However, I can't figure out how to generate that string in EXPLAIN
output. I tried many examples and searched the web for examples but I
can't generate it in queries using git master.
For example, this website:
https://gist.github.com/amitlan/cd13271142bb2d26ae46b69afb675a31
has several EXPLAIN examples that show "Subplan Removed" but running the
queries in git master doesn't generate it for me.
Does anyone know how to generate this? Thanks.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Embrace your flaws. They make you human, rather than perfect,
which you will never be.
On Tue, Jan 31, 2023 at 08:59:57PM -0500, Bruce Momjian wrote: > Our document states that EXPLAIN can generate "Subplan Removed": > > https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION-PRUNING > > It is possible to determine the number of partitions which were removed > during this phase by observing the “Subplans Removed” property in the > EXPLAIN output. Sorry, here is the full paragraph: During initialization of the query plan. Partition pruning can be performed here for parameter values which are known during the initialization phase of execution. Partitions which are pruned during this stage will not show up in the query's EXPLAIN or EXPLAIN ANALYZE. It is possible to determine the number of partitions which were removed during this phase by observing the “Subplans Removed” property in the EXPLAIN output. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Embrace your flaws. They make you human, rather than perfect, which you will never be.
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/regress/expected/partition_prune.out:29 -- Justin
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>
On Wed, Feb 1, 2023 at 11:53:34AM +0900, Yugo NAGATA wrote: > 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. Yes, that is exactly what I as missing. Thank you! -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Embrace your flaws. They make you human, rather than perfect, which you will never be.
On Wed, 1 Feb 2023 at 15:53, Yugo NAGATA <nagata@sraoss.co.jp> wrote:
> Maybe, you missed to set plan_cache_mode to force_generic_plan.
> "Subplan Removed" doesn't appear when using a custom plan.
I wouldn't say that's 100% true. The planner is only able to prune
using values which are known during planning. Constant folding is
going to evaluate any immutable functions during planning, but nothing
more.
Partition pruning might be delayed until execution time if some
expression that's being compared to the partition key is stable. e.g:
create table rp (t timestamp not null) partition by range(t);
create table rp2022 partition of rp for values from ('2022-01-01') to
('2023-01-01');
create table rp2023 partition of rp for values from ('2023-01-01') to
('2024-01-01');
explain select * from rp where t >= now();
Append (cost=0.00..95.33 rows=1506 width=8)
Subplans Removed: 1
-> Seq Scan on rp2023 rp_1 (cost=0.00..43.90 rows=753 width=8)
Filter: (t >= now())
David
On Wed, 1 Feb 2023 16:52:07 +1300
David Rowley <dgrowleyml@gmail.com> wrote:
> On Wed, 1 Feb 2023 at 15:53, Yugo NAGATA <nagata@sraoss.co.jp> wrote:
> > Maybe, you missed to set plan_cache_mode to force_generic_plan.
> > "Subplan Removed" doesn't appear when using a custom plan.
>
> I wouldn't say that's 100% true. The planner is only able to prune
> using values which are known during planning. Constant folding is
> going to evaluate any immutable functions during planning, but nothing
> more.
>
> Partition pruning might be delayed until execution time if some
> expression that's being compared to the partition key is stable. e.g:
>
> create table rp (t timestamp not null) partition by range(t);
> create table rp2022 partition of rp for values from ('2022-01-01') to
> ('2023-01-01');
> create table rp2023 partition of rp for values from ('2023-01-01') to
> ('2024-01-01');
>
> explain select * from rp where t >= now();
>
> Append (cost=0.00..95.33 rows=1506 width=8)
> Subplans Removed: 1
> -> Seq Scan on rp2023 rp_1 (cost=0.00..43.90 rows=753 width=8)
> Filter: (t >= now())
>
I am sorry for my explanation was not completely correct. Thank you for
your clarification.
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>