Hi.
Currently PostgreSQL supports CTE push down for SELECT statements, but
it is implemented as turning each CTE reference into subquery.
When CTE is referenced multiple times, we have choice - to materialize
CTE (and disable quals distribution to the CTE query) or inline it (and
so run CTE query multiple times,
which can be inefficient, for example, when CTE references foreign
tables).
I was looking if it is possible to collect quals referencing CTE,
combine in OR qual and add them to CTE query.
So far I consider the following changes.
1) Modify SS_process_ctes() to add a list of RestrictInfo* to
PlannerInfo - one NULL RestrictInfo pointer per CTE (let's call this
list cte_restrictinfos for now)/
2) In distribute_restrictinfo_to_rels(), when we get rel of RTE_CTE
relkind and sure that can safely pushdown restrictinfo, preserve
restrictinfo in cte_restrictinfos, converting multiple restrictions to
"OR" RestrictInfos.
3) In the end of subquery_planner() (after inheritance_planner() or
grouping_planner()) we can check if cte_restrictinfos contain some
non-null RestrictInfo pointers and recreate plan for corresponding CTEs,
distributing quals to relations inside CTE queries.
For now I'm not sure how to handle vars mapping when we push
restrictinfos to the level of cte root or when we push it down to the
cte plan, but properly mapping vars seems seems to be doable.
Is there something else I miss?
Does somebody work on alternative solution or see issues in such
approach?
--
Best regards,
Alexander Pyhalov,
Postgres Professional
