group by high cardinality column in datafusion 10 times slower than low cardinality column

[jiangzhx]

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.

[xudong963]

If I recall correctly, datafusion doesn't do fine optimization about group by and aggregate functions at present. Datafusion as an AP system, It's worth adding to our RoadMap and doing it in the future.

[jiangzhx]

If I recall correctly, datafusion doesn't do fine optimization about group by and aggregate functions at present. It's worth adding it to our RoadMap and doing it in the future.

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 functions was working fine; with sum(LO_EXTENDEDPRICE) or without; the performence has no big difference,there are also have 5~10 times slow;

low cardinality:

select 1  FROM lineorder_flat group by LO_ORDERPRIORITY;
5 rows in set. Query took 0.236 seconds.

high cardinality:

select 1  FROM lineorder_flat group by S_ADDRESS;
20000 rows in set. Query took 1.429 seconds.

[xudong963]

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]

Some relevant tickets:

#418
#956

[alamb]

Accidentally closed

[alamb]

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 here

[alamb]

Related PR: #6657

[alamb]

see #4973 (comment) for proposal

[alamb]

This should be closed by #6904