comphead commented on issue #27282:
URL: https://github.com/apache/datafusion/issues/27282#issuecomment-6991797603

   ### Heap profile with dhat
   
   I ran Q02 at SF10 under [dhat-rs](https://github.com/nnethercote/dhat-rs) 
0.3.3 to see where the decorrelation shows up in memory. The data and machine 
are the same as above, with 18 partitions. The runner registers the five 
tables, runs the query once to warm the metadata cache, plans it, and then 
executes the plan once under the profiler, so planning is not included. I 
grouped allocation sites by the DataFusion operators on each stack. For the 
four variants I ran on both commits, `main` at 5ea948bc0 gives the same totals 
as 14b3edd9a within 0.01%, so the numbers hold on current `main`.
   
   | SF10, 18 partitions | Allocated | Allocations | Peak heap (dhat) | Peak 
footprint, system allocator | Peak footprint, `datafusion-cli` (mimalloc) |
   |---|---:|---:|---:|---:|---:|
   | `q2.sql` | 3.08 GB | 495K | 240 MB | 302-310 MB | 487-529 MB |
   | Polars plan as DataFusion SQL (`MATERIALIZED` CTE) | 1.47 GB | 341K | 186 
MB | 212-222 MB | 318-325 MB |
   | Subquery only | 1.55 GB | 148K | 183 MB | | |
   | Outer join only | 1.42 GB | 311K | 184 MB | | |
   | `q2.sql`, subquery tables listed as `region, nation, supplier, partsupp` | 
2.56 GB | 529K | 225 MB | | |
   | `q2.sql`, `pushdown_filters = true` | 2.27 GB | 477K | 234 MB | | |
   
   "Subquery only" is `select count(*), min(m)` over the decorrelated 
`min(ps_supplycost) ... group by ps_partkey`. "Outer join only" is the five-way 
join with the same columns, `ORDER BY` and `LIMIT`. Footprints are 
`/usr/bin/time -l` over one cold run, as ranges over three to six runs. Polars 
streaming peaks at 272-274 MB the same way. dhat counts each `realloc` as an 
allocation of its new size, and its global lock serializes allocations, so its 
peak is only indicative.
   
   <details><summary>Where <code>q2.sql</code> allocates, by plan node</summary>
   
   | Plan node | Allocated | Share | Live at the dhat peak |
   |---|---:|---:|---:|
   | All `RepartitionExec`s (grouped `take_arrays` plus output coalescing) | 
723 MB | 23.5% | 21.5 MB |
   | Subquery: `supplier`, `nation` and `region` joins | 611 MB | 19.9% | 13.3 
MB |
   | Outer: `partsupp` scan | 467 MB | 15.2% | 0.4 MB |
   | Subquery: `partsupp` scan | 466 MB | 15.1% | 45.8 MB |
   | Subquery: final aggregate | 184 MB | 6.0% | 0.2 MB |
   | Subquery: partial aggregate | 155 MB | 5.0% | 69.0 MB |
   | Outer: `part` scan and filter | 153 MB | 5.0% | 0.4 MB |
   | Object store reads, all scans | 124 MB | 4.0% | 53.7 MB |
   | Outer: joins | 110 MB | 3.6% | 13.5 MB |
   | `supplier` scans, both sides | 52 MB | 1.7% | 14.5 MB |
   | Final semi join and top-k | 29 MB | 0.9% | 7.0 MB |
   | Total | 3,077 MB | | 239.9 MB |
   
   </details>
   
   ### Findings
   
   1. **The subquery allocates as much as the rest of the query, and its joins 
are the largest part.** In the subquery, the `supplier` and `nation` joins each 
output all 8.00M `partsupp` rows, and only then does the `region` semi join 
keep 1.60M. The output batches of the three joins are 611 MB, and decoding the 
second `partsupp` scan is 466 MB. The partial and final aggregates over 1.18M 
groups are only 155 and 184 MB. Reusing the outer join, as the `MATERIALIZED` 
CTE does, cuts the total from 3.08 to 1.47 GB.
   2. **Join order inside the subquery is worth 0.52 GB but only 3 to 4 ms.** 
DataFusion keeps the FROM order, so the EUROPE filter comes last. Listing the 
subquery's tables as `region, nation, supplier, partsupp` gives semi joins that 
reach `partsupp` with the 20.03K EUROPE suppliers, so the joins output 1.60M 
rows once. The subquery's join output falls from 611 to 89 MB. `q2.sql` goes 
from 38.0 to 34-35 ms, against 21.0 ms for the `MATERIALIZED` CTE in the same 
runs (`datafusion-cli`, round medians of about 20 runs, three interleaved 
rounds).
   3. **Hash `RepartitionExec` copies each row twice, and the outer 
`Hash(ps_partkey)` repartition gets all 8.00M rows.** Each row is copied once 
by the grouped `take_arrays` and again by the output `LimitedBatchCoalescer`. 
Since the scan does not filter rows by default (finding 2 in the issue), the 
outer-only run spends 565 MB on repartitioning, nearly all of it in this 
repartition, which is more than decoding the rows (465 MB). The [proposal in 
#7001](https://github.com/apache/datafusion/issues/7001#issuecomment-5801503499)
 would drop the `take_arrays` copy by passing the partition indices to 
`BatchCoalescer::push_batch_with_indices`. That builds on 
apache/arrow-rs#11174, which was merged on 2026-11-12 and is not in arrow 
60.1.0.
   4. **`pushdown_filters = true` cuts 0.81 GB but not the peak.** The outer 
`partsupp` scan returns 31.4K rows, so the repartitions drop from 723 to 169 
MB. In the subquery, the `nation` scan returns 5 rows instead of 25, so the 
`nation` join outputs 1.60M rows and the subquery joins drop from 611 to 421 
MB. The `supplier` scans still return all 100K rows. The routed hash lookup in 
the outer scan adds 164 MB, because `evaluate_routed` allocates three buffers 
for every batch (20 bytes per row). The peak stays at 234 MB.
   5. **The subquery sets the peak.** dhat's peak comes late in the run, when 
the outer scans hold under 1 MB. The partial aggregate holds 69 MB (hash table, 
group values and `min` states), the second `partsupp` scan 46 MB, mostly 
dictionary pages, and the `LocalFileSystem::read_range` buffers for column 
chunks 54 MB. The partial aggregate only turns 1.60M rows into 1.18M. 
`partsupp` is stored in `ps_partkey` order, so each partition sees a contiguous 
key range with about 0.74 groups per row, below the 0.8 skip ratio. Lowering 
`skip_partial_aggregation_probe_rows_threshold` to 50,000 changed nothing.
   
   <details><summary>Runner</summary>
   
   `benchmarks/src/bin/q02_dhat.rs`, with `dhat = "0.3.3"` added to 
`benchmarks/Cargo.toml`. Built with `cargo build --profile profiling --config 
'profile.profiling.debug="line-tables-only"' -p datafusion-benchmarks --bin 
q02_dhat`, and run as `q02_dhat <tpch dir> <sql file> <dhat json> 1`. Passing 
`-` instead of a JSON path skips the profiler, which gave the system allocator 
footprints.
   
   ```rust
   //! Heap-profiles one TPC-H query with dhat.
   //!
   //! usage: q02_dhat <tpch dir> <sql file> <dhat json | -> [warmup runs]
   //!
   //! Statements before the last one in the SQL file (for example `SET`) run
   //! once. The last statement runs `warmup` times unprofiled, is planned
   //! unprofiled, and then executes once under the profiler. With `-` as the
   //! output path the profiler is not started, so the run uses the plain
   //! system allocator.
   
   use std::sync::Arc;
   use std::time::Instant;
   
   use datafusion::error::Result;
   use datafusion::physical_plan::collect;
   use datafusion::prelude::*;
   
   #[global_allocator]
   static ALLOC: dhat::Alloc = dhat::Alloc;
   
   #[tokio::main]
   async fn main() -> Result<()> {
       let usage = "usage: q02_dhat <tpch dir> <sql file> <dhat json | -> 
[warmup runs]";
       let mut args = std::env::args().skip(1);
       let data = args.next().expect(usage);
       let sql_file = args.next().expect(usage);
       let out = args.next().expect(usage);
       let warmup: usize = args.next().map(|s| s.parse().unwrap()).unwrap_or(1);
   
       let sql = std::fs::read_to_string(&sql_file)?;
       let statements: Vec<&str> = sql
           .split(';')
           .map(str::trim)
           .filter(|s| !s.is_empty())
           .collect();
       let (query, setup) = statements.split_last().expect("empty sql file");
   
       let ctx = SessionContext::new_with_config(SessionConfig::from_env()?);
       for table in ["nation", "part", "partsupp", "region", "supplier"] {
           ctx.register_parquet(
               table,
               format!("{data}/{table}/"),
               ParquetReadOptions::default(),
           )
           .await?;
       }
       for statement in setup {
           ctx.sql(statement).await?.collect().await?;
       }
       for _ in 0..warmup {
           ctx.sql(query).await?.collect().await?;
       }
   
       let plan = ctx.sql(query).await?.create_physical_plan().await?;
       let task_ctx = ctx.task_ctx();
       let profiler = (out != "-").then(|| {
           dhat::Profiler::builder()
               .file_name(&out)
               .trim_backtraces(Some(usize::MAX))
               .build()
       });
       let start = Instant::now();
       let batches = collect(Arc::clone(&plan), task_ctx).await?;
       let elapsed = start.elapsed();
       let rows: usize = batches.iter().map(|b| b.num_rows()).sum();
       drop(batches);
       drop(profiler);
       eprintln!(
           "{sql_file}: {rows} rows, execution {:.1} ms",
           elapsed.as_secs_f64() * 1e3
       );
       Ok(())
   }
   ```
   
   </details>
   


-- 
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]

Reply via email to