[EPIC] CTE Materalization

[nathanb9]

Tasks

For approach details read POC PR description please: #22551

• Enable option to materalize CTEs. Addressing: Make datafusion support materializing option for CTE #17737
  • [READY FOR REVIEW] PR: Cache and materalize CTE with multiple references [Part 1 / 3] #22675
• Optimizer pass for smarter decision whether to do inlining or materializing.
• Optimizer pass for (CteFilterPusher)? To minimize the cardinality of what is materialized
• Memory Pool Integration
• Enable by default?

[Tpt]

Outsider comment: materialization is useful not only for CTEs but for duplicated subplans in general. Instead of focusing only on CTEs, what about not touching the SQL planner at all but add a logical (or physical?) optimizer rule that looks for duplicated subplans and according to some heuristics insert a new Materialized/Cached node?

Exploring relations with materialized views might also be great. Materializing a subplans doing a query evaluation is very similar to creating/using a materialized view.