Re: CTE push down

Ashutosh Bapat писал 2021-04-14 16:01:
> On Tue, Apr 13, 2021 at 6:58 PM Alexander Pyhalov
> <a.pyhalov@postgrespro.ru> wrote:

> I believe step2 is needed to avoid materializing rows which will never
> be selected. That would be a good improvement. However, care needs to
> be taken for volatile quals. I think, the quals on CTE will be
> evaluated twice, once when materializing the CTE result and second
> time when scanning the materialized result. volatile quals may produce
> different results when run multiple times.
> 
>> 
>> Is there something else I miss?
>> Does somebody work on alternative solution or see issues in such
>> approach?
> 
> IMO, a POC patch will help understand your idea.

Hi.

I have a POC patch, which allows to distribute restrictinfos inside 
CTEs.
However, I found I can't efficiently do partition pruning.
When CTE replan stage happens, plans are already done. I can create 
alternative paths for relations,
for example, like in Try-prune-partitions patch.

However, new paths are not propagated to finalrel (UPPER_REL).
I'm not sure how to achieve this and need some advice.
Should we redo part of work, done by grouping_planner(), in the end of 
SS_replan_ctes()?
Should we rely on executor partition pruning (with current patches it 
doesn't work)?
Should we create init plans for ctes after grouping_planner(), not 
before?

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional