Re: Generating "Subplan Removed" in EXPLAIN
On Wed, 1 Feb 2023 16:52:07 +1300
David Rowley wrote:
> On Wed, 1 Feb 2023 at 15:53, Yugo NAGATA 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
Re: Generating "Subplan Removed" in EXPLAIN
On Wed, 1 Feb 2023 at 15:53, Yugo NAGATA 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
Re: Generating "Subplan Removed" in EXPLAIN
On Wed, Feb 1, 2023 at 11:53:34AM +0900, Yugo NAGATA wrote: > On Tue, 31 Jan 2023 20:38:21 -0600 > Justin Pryzby wrote: > > > > > To: Bruce Momjian > > 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. Yes, that is exactly what I as missing. Thank you! -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Embrace your flaws. They make you human, rather than perfect, which you will never be.
Re: Generating "Subplan Removed" in EXPLAIN
On Tue, 31 Jan 2023 20:38:21 -0600 Justin Pryzby wrote: > > To: Bruce Momjian > 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
Re: Generating "Subplan Removed" in EXPLAIN
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
Re: Generating "Subplan Removed" in EXPLAIN
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 https://momjian.us EDB https://enterprisedb.com Embrace your flaws. They make you human, rather than perfect, which you will never be.
