Re: Generating "Subplan Removed" in EXPLAIN

On Wed, 1 Feb 2023 16:52:07 +1300
David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> 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())
>

I am sorry for my explanation was not completely correct. Thank you for
your clarification.

Regards,
Yugo Nagata

--
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>