adriangb opened a new issue, #26237: URL: https://github.com/apache/datafusion/issues/26237
### Is your feature request related to a problem or challenge? Follow-up to https://github.com/apache/datafusion/pull/25580 (`WITH ... AS MATERIALIZED`), part of https://github.com/apache/datafusion/issues/17737. Related to https://github.com/apache/datafusion/issues/8777 and to the EPIC https://github.com/apache/datafusion/issues/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: ```sql -- `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 (https://github.com/apache/datafusion/issues/10337). - The decision is better with statistics on the scans (https://github.com/apache/datafusion/issues/26230), filter pushdown into the body and column pruning of the body. ### Describe alternatives you've considered _No response_ ### Additional context _No response_ -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
