Decide automatically when to materialize a CTE #26237

Is your feature request related to a problem or challenge?

Follow-up to #25580 (WITH ... AS MATERIALIZED), part of #17737. Related to #8777 and to the EPIC #22676.

After that PR, a CTE is computed once only when the query says AS MATERIALIZED, and only in the PostgreSQL dialect. In all other cases the SQL planner inlines the CTE at each reference (datafusion/sql/src/cte.rs), and the body runs once for each reference:

-- `expensive` runs two times
WITH c AS (SELECT k, count(*) AS n FROM expensive GROUP BY k)
SELECT * FROM c a JOIN c b ON a.n = b.n;

PostgreSQL materializes a CTE with more than one reference by default. DuckDB decides for each CTE in an optimizer rule. Queries of this shape are frequent in TPC-DS.

Describe the solution you'd like

Make the decision in the optimizer, not in the SQL planner:

1. The SQL planner emits MaterializedCte / MaterializedCteScan for each CTE that has more than one reference and no directive.
2. A new logical optimizer rule inlines the CTEs for which materialization is not expected to help. DuckDB's cte_inlining.cpp is a reference for the conditions (for example: one reference only, a body that is cheap, a body where filter pushdown from the references is worth more than the shared work).
3. AS MATERIALIZED and AS NOT MATERIALIZED always override the rule.

The plan nodes from the PR are expected to support this without changes. The rule needs a way to turn a MaterializedCteScan back into a copy of the body.

Points to decide:

• The default. Materialization is a pipeline breaker and uses memory or disk, so a wrong decision makes a query slower. A config option that is off by default is a safe first step.
• Volatile bodies. A CTE with random() gives different results when it is inlined and when it is shared (Avoid inlining non deterministic CTE #10337).
• The decision is better with statistics on the scans (Materialized CTE scans report unknown statistics, ordering and partitioning #26230), filter pushdown into the body and column pruning of the body.

Describe alternatives you've considered

No response

Additional context

No response