Re: Generating "Subplan Removed" in EXPLAIN

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>