[EPIC] Subplan Materalization

## Goals

* Enable option to materalize CTEs. Addressing: https://github.com/apache/datafusion/issues/17737
* 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?

---
* Current POC which does full materalization: https://github.com/apache/datafusion/pull/22551
* PR: https://github.com/apache/datafusion/pull/22675
* Working on alternative POC which tries to do minimal amount of pipeline breaking

Start by reading issue 17737 and the current full-materialization POCs in PRs 22551 and 22675. Compare them with the alternative minimal-pipeline-breaking approach mentioned here, then scope the optimizer, filter-pushing, memory-pool, and default-behavior goals before defining what a complete implementation would include.

Written by the indexing model from the issue text.