Groupby performance between pyarrow and duckdb

[zacqed]

import pyarrow as pa
t = pa.table([
      pa.array(["a", "a", "b", "b", "c"]),
      pa.array([1, 2, 3, 4, 5]),
], names=["keys", "values"])

import duckdb
con = duckdb.connect()
# con.execute('PRAGMA threads=4')
ungrouped_aggregate = '''SELECT SUM(values) FROM t GROUP BY keys'''

%timeit t.group_by("keys").aggregate([("values", "sum")])
64.5 µs ± 6.32 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)

%timeit con.execute(ungrouped_aggregate).fetch_arrow_table()
1.15 ms ± 70.3 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Should this be considered normal?

[Alex-Monahan]

This is for a 5 row table, correct? DuckDB is typically focused on performance for millions or billions of rows. My guess is that it will scale very well - there may be some startup time that you are seeing with only 5 rows. Mind benchmarking with a million or billion rows?

[zacqed]

That about answers it. Using lineitem dataset, it is significantly faster than pyarrow.

[Alex-Monahan]

Mind sharing your benchmark results? We love seeing real user benchmarks! Thanks for checking out DuckDB!

[zacqed]

Here are the results:

import pyarrow.parquet as pq
lineitem = pq.read_table('lineitemsf1.snappy.parquet')
con = duckdb.connect()
%timeit lineitem.group_by("l_returnflag").aggregate([("l_extendedprice", "sum")])
ungrouped_aggregate = '''SELECT SUM(l_extendedprice) FROM lineitem GROUP BY l_returnflag'''
%timeit con.execute(ungrouped_aggregate).fetch_arrow_table()

%timeit lineitem.group_by("l_returnflag").aggregate([("l_extendedprice", "sum")])
207 ms ± 9.31 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit con.execute(ungrouped_aggregate).fetch_arrow_table()
71.8 ms ± 2.31 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

[zacqed]

This doesnt scale with threads. Is there anything additional to be done except setting
con.execute('PRAGMA threads=4')

[Mytherin]

No, but the default setting is setting the threads to the number of available cores.