Re: Generating "Subplan Removed" in EXPLAIN

On Wed, 1 Feb 2023 at 15:53, Yugo NAGATA <nagata(at)sraoss(dot)co(dot)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