xumingming commented on PR #58229: URL: https://github.com/apache/spark/pull/58229#issuecomment-5602439097
Add more cross engine info here, I investigated popular compute engine like PostgreSQL and DuckDB: **PostgreSQL** - < 12: **every CTE was an unconditional optimization fence — always materialized**, no exceptions. - ≥ 12 (release notes (https://www.postgresql.org/docs/12/release-12.html)): flipped default — a WITH query is inlined if it's non-recursive, has no volatile functions, and is referenced exactly once. **Referenced ≥2 times → materialized by default.** Volatile or recursive → always materialized (can't be folded safely). Explicit MATERIALIZED/NOT MATERIALIZED keywords let you override in either direction. **DuckDB** - < 1.4: inlined by default (any reference count), which caused both duplicated computation and real correctness bugs. **- ≥ 1.4 ("Andium", Sept 2025): flipped the other way — materialized by default.** Only inlines when: referenced once, no volatile function, no grouped aggregation, and no NOT MATERIALIZED override forbidding it. DuckDB explicitly cites both performance and correctness bugs as the reason. Same MATERIALIZED/NOT MATERIALIZED syntax as Postgres exists but docs discourage using it manually. **Although for views all the engines inline by default. But from the performance point of view, IMO View and CTE are similar, they both captures a common shared snippet of SQL that could be referenced multiple times in a query. Add a switch to be able to materialize the View/CTE will give us real performance benefit** -- 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]
