[I] Prune unused columns from the body of a materialized CTE [datafusion]

["adriangb (via GitHub)" <gi...@apache.org>]

adriangb opened a new issue, #26240:
URL: https://github.com/apache/datafusion/issues/26240

   ### 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.
   
   The body of a materialized CTE keeps all its columns, even when no reference reads them. `MaterializedCte::necessary_children_exprs` asks the body for every column, because each `MaterializedCteScan` is a leaf with a schema that is fixed when the SQL is planned.
   
   ```sql
   WITH c AS MATERIALIZED (SELECT * FROM wide_table)
   SELECT a FROM c
   UNION ALL
   SELECT b FROM c;
   ```
   
   All columns of `wide_table` are read and buffered. Only `a` and `b` are used. With the CTE inlined, each branch reads one column.
   
   This costs scan time, and memory or spill space for the buffer.
   
   ### Describe the solution you'd like
   
   Keep in the body only the columns that at least one reference needs:
   
   1. Collect the columns that each `MaterializedCteScan` of the CTE must give to its parent.
   2. Project the body to the union of these columns.
   3. Change the schema of every scan of that CTE to the new body schema, and correct the column indexes above the scans.
   
   `OptimizeProjections` works top-down on one node at a time, and here the requirement of the body comes from several leaves in the other input (or in a subquery). So this probably needs its own pass, or a first pass that collects the requirements before the body is visited.
   
   ### 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: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org