mohitgurav20 commented on PR #26000:
URL: https://github.com/apache/datafusion/pull/26000#issuecomment-6578174996
From checking the execution plans and benchmark numbers, turning off the
rewrite (`prefer_memory = true`) gives a big advantage mainly in two cases:
1. **`GROUP BY` queries on numeric data (especially with companion
aggregates):**
This showed the biggest memory drop. In queries like `SELECT y, sum(y),
sum(DISTINCT x)` and `SELECT y, sum(DISTINCT x)`, peak memory dropped by **60%
to 76%** (from around 194 MB down to 46–77 MB), and the runtime was basically
identical or even slightly faster (183 ms vs 209 ms).
The reason is that the 2-phase rewrite creates an extra intermediate
aggregate grouped by both `(y, x)` and shuffles that across partitions. But for
numeric types like ints and floats, values are small and fixed-size. With
single-phase, we only maintain the `y` group table and handle distinct values
in place, so we completely avoid materializing and shuffling that intermediate
`(y, x)` table.
2. **Low cardinality columns (like `smallint` or categorical IDs):**
For `count(DISTINCT smallint)`, single-phase was **2.5x faster** (2.3 ms
vs 5.8 ms) and used **88% less memory** (0.07 MB vs 0.57 MB). When there are
very few distinct values, setting up a 2-phase aggregation and repartitioning
is just extra overhead.
On the other hand, the benchmarks also clearly showed why the rewrite should
stay enabled by default:
- **Queries without `GROUP BY`:** Without the rewrite, DataFusion runs the
distinct aggregation on a single partition/thread, which causes a 1.6x to 3.4x
slowdown on larger datasets.
- **Strings:** Storing distinct strings without the early 2-phase reduction
causes a lot of heap allocations in the accumulators, which actually increases
peak memory (+10% to +45%) and makes it much slower.
So the flag works best when users have memory-heavy `GROUP BY` queries over
numeric or low-cardinality data, where they can save up to ~75% peak memory
with almost zero performance impact.
--
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]
