Repository navigation
Description
Describe the bug
Small datasets undergo unnecessary repartitioning.
For example, a dataset of 5 rows will be split across multiple certain queries, leading to overhead that exceeds the parallelism benefit.
To Reproduce
An example can be found in the aggregate_repartition.slt file. This test creates two tables of 5 rows each in Parquet and CSV format, and an aggregation on one of their columns. Despite the small size, the query plan repartitions the data:
Setup the data:
COPY (
SELECT * FROM (VALUES
('prod', 100, 'A'),
('dev', 200, 'B'),
('test', 150, 'A'),
('prod', 300, 'C'),
('dev', 250, 'B')
) AS t(env, value, category)
)
TO 'test_files/scratch/aggregate_repartition/dim.csv'
STORED AS CSV
OPTIONS ('format.has_header' 'true');
COPY (
SELECT * FROM (VALUES
('prod', 100, 'A'),
('dev', 200, 'B'),
('test', 150, 'A'),
('prod', 300, 'C'),
('dev', 250, 'B')
) AS t(env, value, category)
)
TO 'test_files/scratch/aggregate_repartition/dim.parquet'
STORED AS PARQUET;
CREATE EXTERNAL TABLE dim_csv
STORED AS CSV
LOCATION 'test_files/scratch/aggregate_repartition/dim.csv'
OPTIONS ('format.has_header' 'true');
CREATE EXTERNAL TABLE dim_parquet
STORED AS PARQUET
LOCATION 'test_files/scratch/aggregate_repartition/dim.parquet';
CSV Physical Plan:
EXPLAIN SELECT env, count(*) FROM dim_csv GROUP BY env;
physical_plan
01)ProjectionExec: expr=[env@0 as env, count(Int64(1))@1 as count(*)]
02)--AggregateExec: mode=FinalPartitioned, gby=[env@0 as env], aggr=[count(Int64(1))]
03)----CoalesceBatchesExec: target_batch_size=8192
04)------RepartitionExec: partitioning=Hash([env@0], 4), input_partitions=4
05)--------AggregateExec: mode=Partial, gby=[env@0 as env], aggr=[count(Int64(1))]
06)----------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
07)------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/aggregate_repartition/dim.csv]]}, projection=[env], file_type=csv, has_header=true
Parquet Physical Plan:
EXPLAIN SELECT env, count(*) FROM dim_parquet GROUP BY env;
physical_plan
01)ProjectionExec: expr=[env@0 as env, count(Int64(1))@1 as count(*)]
02)--AggregateExec: mode=FinalPartitioned, gby=[env@0 as env], aggr=[count(Int64(1))]
03)----CoalesceBatchesExec: target_batch_size=8192
04)------RepartitionExec: partitioning=Hash([env@0], 4), input_partitions=1
05)--------AggregateExec: mode=Partial, gby=[env@0 as env], aggr=[count(Int64(1))]
06)----------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/aggregate_repartition/dim.parquet]]}, projection=[env], file_type=parquet
Expected behavior
The data sets should not be repartitioned when this small in size, thus should yield the plans:
CSV Physical Plan:
EXPLAIN SELECT env, count(*) FROM dim_csv GROUP BY env;
physical_plan
01)ProjectionExec: expr=[env@0 as env, count(Int64(1))@1 as count(*)]
02)--AggregateExec: mode=FinalPartitioned, gby=[env@0 as env], aggr=[count(Int64(1))]
03)----DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/aggregate_repartition/dim.csv]]}, projection=[env], file_type=csv, has_header=true
Parquet Physical Plan:
EXPLAIN SELECT env, count(*) FROM dim_parquet GROUP BY env;
physical_plan
01)ProjectionExec: expr=[env@0 as env, count(Int64(1))@1 as count(*)]
02)--AggregateExec: mode=FinalPartitioned, gby=[env@0 as env], aggr=[count(Int64(1))]
03)----DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/sqllogictest/test_files/scratch/aggregate_repartition/dim.parquet]]}, projection=[env], file_type=parquet
Additional context
The problem roots from enforce_distribution.rs:get_repartition_requirement_status.
Here round-robin partitioning is added based on statistics on the file:
- we are optimistic with CSV files and return
truesince CSV return no statistics. We could look at adding a better way to estimate if a CSV file needs repartitioning at the file level - We have exact statistics on Parquet files, thus we do not round robin repartition at the file level, but we still hash repartition later on in the query. We can look at how hash repartitioning is handles based on file statistics and improve this.
Activity
gene-bordegaray commentedon Nov 10, 2025
cc: @NGA-TRAN
NGA-TRAN commentedon Nov 10, 2025
I suggest we start with Parquet, since we already have precise statistics available. CSV is more complex and can be tackled separately once we’ve gathered the necessary stats.
gene-bordegaray commentedon Nov 10, 2025
ok, and yes the parquet will be much less work. Should I create two separate issues for these?
NGA-TRAN commentedon Nov 10, 2025
No need. Once someone closes this issue, they will open a new one as needed
LiaCastaneda commentedon Nov 12, 2025
I'm wondering where the fix to this should be, Rather than in the optimizer
EnforceDistributionor maybe in the planner? I wonder why its not selectingAggregateMode::Single(not partition) directly. This is where the node is created in the physical planner.feniljain commentedon Nov 12, 2025
Issue sounds very similar to 18513, are they related by any chance?
NGA-TRAN commentedon Nov 12, 2025
@feniljain
It may be similar issue. I wonder after this fix (see PR), will it get better? See the summary of the fix here
@LiaCastaneda
I suspect the fix might be tricky—by the time we detect that the file is small, we may have already committed to repartitioning elsewhere. This ticket likely requires a deeper investigation to fully understand the planning flow and its implications.
feniljain commentedon Nov 12, 2025
I tested on latest
main. It is still happening, also I am mentioning the issue due to this statement in it:though title of issue does say something else:
Output ordering with a filter over FileSource seems to cause some unexpected repartitioning behaviorgene-bordegaray commentedon Nov 12, 2025
Great catch @LiaCastaneda. I read read into some of the code and think a great place to look into is the condition which partitions on aggregates. This condition is in
physical_planner.rson line 801:here it could be useful to have another condition checking if we actually benefit from repartitioning. This new condition could check statistics of the data source and make a decision based on this. Say in my example we were to see we have one input partition, then we check is this partition actually large enough based on statistic to make the AggregatePartitioned.
I believe catching this at the physical planner level would be best though.
LiaCastaneda commentedon Nov 13, 2025
yeah this looks like a tricky problem 😿 , TLDR the issue is that here the Aggregate node has
FinalPartitionedmode which enforcesHashPartitionedmode which always enforcesRepartitionExecright?So the two options:
FinalPartitionedonAggregateExecand unless you traverse the plan tree you wont be able to change it, you can skip repartitionning still, but it might break correctness assumptions elsewhere as Nga says. You will need to check its backwards compatible.shashidhar-bm commentedon Nov 16, 2025
Hey everyone! I've been following this discussion and agree with @LiaCastaneda that Option 2 (Physical Planner approach) is better since we can choose
AggregateMode::Singleupfront for small datasets, avoiding the backwards compatibility issues of skipping repartitions afterFinalPartitionedmode is set. As @NGA-TRAN mentioned, I'd like to start with Parquet files since they already have row count statistics available. I'm thinking we could add a check in the physical planner that uses the row count to decide the aggregate mode. Would it be okay if I work on this?shashidhar-bm commentedon Nov 17, 2025
take
shashidhar-bm commentedon Dec 7, 2025
I have opened a fresh PR #19073 to resolve this.
This replaces the previous attempt in #18776. I started fresh because the old PR had too many changes and became hard to refactor. This version is much cleaner and includes all the feedback discussed previously.
Split a query across partitions only where enough rows reach it (#289)