datafusion-python: Some group by query is 6~7x slower than DuckDB
Describe the bug
Hi team, I have encountered a performance issue when I run same query on a big table with datafusion comparing with DuckDB.
I will try to simplify my case and replicate the issue in my following codes.
To Reproduce
import timeit
import numpy as np
import pyarrow as pa
import datafusion
from datafusion import SessionContext
import duckdb
print(duckdb.__version__)
print(datafusion.__version__)
# prepare data
batches = 100000
names = list("abcdefghijklmnopqrstuvwxyz")
names = [n + m for n in names for m in names]
names_array = pa.concat_arrays([pa.array(names)] * batches)
values_array = pa.concat_arrays([pa.array(np.random.randint(1, 100, len(names))) for _ in range(batches)])
pa_table = pa.Table.from_arrays([names_array, values_array], names=["name", "value"])
# prepare query
sql = "select name, sum(value) as value FROM pa_table group by name;"
n_round = 10
# duckb
elapsed = timeit.timeit('duckdb.sql(sql).to_arrow_table()', number=n_round , globals=globals())
duckdb_per_round = elapsed / n_round
# datafusion
ctx = SessionContext()
_ = ctx.from_arrow(pa_table, "pa_table")
elapsed = timeit.timeit('ctx.sql(sql).to_arrow_table()', number=n_round , globals=globals())
datafusion_per_round= elapsed / n_round
# result
print(f"{'duckdb':<12}: {duckdb_per_round * 1000:.2f}ms")
print(f"{'datafusion':<12}: {datafusion_per_round * 1000:.2f}ms")
the output will look like:
1.3.1
47.0.0
duckdb : 152.15ms
datafusion : 1002.04ms
Expected behavior
No response
Additional context
No response
About this issue
- Original URL
- State: open
- Created a year ago
- Comments: 15 (9 by maintainers)
From the code it seems to me
pa.concat_arraysis converting the input arrays into a single array.DataFusion won’t currently split the batch into smaller batches as of now, so the aggregation for a single array/batch will use a single thread only (
RepartitionExecwill send the input batch to a single output stream).I see the output
One thing I noticed is that only 1 CPU core is used
I could not seem to get a useful profile (the stack traces I had didn’t show a stack frame)
Profiling command
Here is the query
Here is the explain plan
Here is the
explain(analyze=True):Note that the first AggregateExec takes most of the time:
elapsed_compute=1.056854918sConclusion
This plan shows the proper repartitioning (by 16) so I would expect it to keep all 16 cores busy.
However, only a single core was busy, so I conclude the bottleneck is in the input DataSource – it seems to me that it can’t feed the data fast enough
So one thing we could potentially do here is make RepartitionExec smarter for large batches, and if the input is really large split it into smaller batch sizes or something 🤔
I tried changing the code to use ChunkedArray rather than a single array
So the table now looks like
And then actually I see the reverse performance (duckdb is slower 🤯 ):