Re: CTE push down

Ashutosh Bapat писал 2021-04-14 16:01:
> On Tue, Apr 13, 2021 at 6:58 PM Alexander Pyhalov
> <a(dot)pyhalov(at)postgrespro(dot)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