adriangb opened a new issue, #26424: URL: https://github.com/apache/datafusion/issues/26424
### Is your feature request related to a problem or challenge? A scan of a `MATERIALIZED` CTE (`MaterializedCteScanExec`) reports no ordering and unknown partitioning. The continuation then sorts or repartitions rows that the CTE body already sorted or partitioned: ```sql WITH c AS MATERIALIZED (SELECT g, count(*) AS n FROM p GROUP BY g) SELECT g, sum(n) FROM c GROUP BY g; ``` Here the body is hash-partitioned on `g`, but the plan adds a `RepartitionExec` and a partial aggregate above the scan. The scan is a leaf, and the body is a child of `MaterializedCteExec`. The body gets its final ordering and partitioning only after `EnforceDistribution` and `EnforceSorting` run. These rule runs also decide what to put above the scans, so one optimizer pass cannot copy the properties in time. apache/datafusion#26271 first tried to fix this by optimizing the whole plan up to four times. That part was removed, and the PR now copies only the statistics. ### Describe the solution you'd like Optimize each CTE body before the full plan, as PostgreSQL plans a CTE subquery before the outer query: 1. Optimize each body on its own, in dependency order (a body that reads another CTE goes after that CTE). `MaterializedCteExec` puts no distribution or ordering requirement on the body, so the plan of the body does not depend on the continuation. 2. Give each scan the partition count, ordering and partitioning of its optimized body. 3. Optimize the full plan one time. 4. Keep the result only if each body still has the properties that its scans report. If not, use the plan with statistics only. This takes one full optimizer pass plus one pass for each body, with no retry loop. ### Describe alternatives you've considered - Optimize the full plan repeatedly until the scans match the bodies (the first version of apache/datafusion#26271). This takes up to four full passes, and nothing guarantees that it stops changing. - One rule that copies the properties before `EnforceDistribution` and `EnforceSorting`. At that point the body properties are not final. - A scan that reads the properties live from its body. `properties()` is cached and immutable, so a later rewrite of the body could make the claim false and give wrong results. ### Additional context Part of apache/datafusion#22676. Stacked on apache/datafusion#26271. The gain is a sort or a repartition over a buffer that is already in memory, so the PR should include a measurement of that gain. -- 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]
