adriangb opened a new issue, #26232: URL: https://github.com/apache/datafusion/issues/26232
### 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/pull/25910 and https://github.com/apache/datafusion/issues/25804. When the body of a materialized CTE does not fit in memory, it is written to spill files, and each reference reads them back. Each reference reserves memory for the largest batch on disk. A spill file has no limit on the size of one batch in bytes, so one large batch (wide rows) sets the memory that every reference needs. A query with N references then needs N times the largest batch, and can fail with `ResourcesExhausted` when most of the pool is free. https://github.com/apache/datafusion/pull/25580 has a partial fix that is local to the operator. When it moves an in-memory batch to disk, it writes the batch in pieces no larger than the batches already on disk (see the review thread https://github.com/apache/datafusion/pull/25580#discussion_r4244985567). This has limits: - The pieces are equal row ranges, sized from `get_array_memory_size`. This is wrong for dictionary, view and list columns, where the IPC writer writes more than the rows of a slice. - Batches that go to disk while the body runs are written whole. The first large batch that is spilled sets the size for all later ones. ### Describe the solution you'd like https://github.com/apache/datafusion/pull/25910 adds a byte bound to `SpillManager` (`with_max_batch_bytes`), with a size model that agrees with the IPC writer. The bound is for readers that read a file to its end, which is what the CTE scans do. When that PR is merged: 1. Create the `SpillManager` of the materialized CTE with a byte bound. 2. Remove the slicing in `BufferedPartition::spill`. 3. Keep the check that a move to disk releases more memory than it adds to the readers. It depends on the number of references and stays in the operator. Open question: which bound to use. The sort derives it from `sort_spill_reservation_bytes`. The materialized CTE has no such setting. Options are a fraction of the memory pool divided by the number of concurrent readers, or a shared spill setting. ### 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]
