Repository navigation
group by high cardinality column in datafusion 10 times slower than low cardinality column #1246
Description
Describe the bug
group by high cardinality column in datafusion 10 times slower than low cardinality column.
also i tested on other olap engine, there are only 2 times slow or less;
-
trino olap engine write by java
low cardinality usage ms: 1400ms± high cardinality usage ms: 2700ms± -
doris olap engine write by c++
low cardinality usage ms: 350ms± high cardinality usage ms: 500ms±
To Reproduce
Steps to reproduce the behavior:
parquet table with 60,000,000 rows; data generate by ssb-dbgen
group by LO_ORDERPRIORITY
SELECT sum(LO_EXTENDEDPRICE) AS revenue FROM lineorder_flat group by LO_ORDERPRIORITY;
5 rows in set. Query took 0.341 seconds.
group by S_ADDRESS
SELECT sum(LO_EXTENDEDPRICE) AS revenue FROM lineorder_flat group by S_ADDRESS;
20000 rows in set. Query took 2.582 seconds.
Expected behavior
should some with other engine;
Additional context
Add any other context about the problem here.
Activity
xudong963 commentedon Nov 5, 2021
If I recall correctly, datafusion doesn't do fine optimization about
group byandaggregate functionsat present. Datafusion as an AP system, It's worth adding to our RoadMap and doing it in the future.jiangzhx commentedon Nov 5, 2021
i try to dig code in trino and doris; there are all have streaming aggregate node; but i can't understand how they working.
aggregate functionswas working fine; with sum(LO_EXTENDEDPRICE) or without; the performence has no big difference,there are also have 5~10 times slow;low cardinality:
high cardinality:
xudong963 commentedon Nov 5, 2021
I'll take a look at Doris on the weekend. Until then, we can wait for someone else to answer your questions. Thanks for your comparison @jiangzhx
Dandandan commentedon Nov 5, 2021
Some relevant tickets:
#418
#956
alamb commentedon Nov 5, 2021
Accidentally closed
alamb commentedon Nov 5, 2021
I think there is a lot of overhead creating and managing group keys via
ScalarValues that is a good thing to look into if we want to optimize the performance herealamb commentedon Jun 25, 2023
Related PR: #6657
alamb commentedon Jun 27, 2023
see #4973 (comment) for proposal
alamb commentedon Jul 13, 2023
This should be closed by #6904