You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "jayzhan211 (via GitHub)" <gi...@apache.org> on 2024/08/03 03:02:35 UTC
[PR] Single mode for multi column group by [datafusion]
jayzhan211 opened a new pull request, #11792:
URL: https://github.com/apache/datafusion/pull/11792
## Which issue does this PR close?
<!--
We generally require a GitHub issue to be filed for all bug fixes and enhancements and this helps us generate change logs for our releases. You can link an issue to this PR using the GitHub syntax. For example `Closes #123` indicates that this PR will close issue #123.
-->
Production ready PR
Closes #.
## Rationale for this change
<!--
Why are you proposing this change? If this is already explained clearly in the issue then this section is not needed.
Explaining clearly why changes are proposed helps reviewers understand your changes and offer better suggestions for fixes.
-->
## What changes are included in this PR?
<!--
There is no need to duplicate the description in the issue here but it is sometimes worth providing a summary of the individual changes in this PR.
-->
## Are these changes tested?
<!--
We typically require tests for all PRs in order to:
1. Prevent the code from being accidentally broken by subsequent changes
2. Serve as another way to document the expected behavior of the code
If tests are not included in your PR, please explain why (for example, are they covered by existing tests)?
-->
## Are there any user-facing changes?
<!--
If there are user-facing changes then we may require documentation to be updated before approving the PR.
-->
<!--
If there are any breaking changes to public APIs, please add the `api change` label.
-->
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Single mode for multi column group by -- Almost 2x for ClickBench Q32 [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #11792:
URL: https://github.com/apache/datafusion/pull/11792#issuecomment-2269439713
I am running some benchmarks on this one
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Single mode for multi column group by -- Almost 2x for ClickBench Q32 [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #11792:
URL: https://github.com/apache/datafusion/pull/11792#issuecomment-2267512080
Thanks @jayzhan211 -- this looks quite interesting,. I will try and study this PR carefully early this week
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Single mode for multi column group by -- Almost 2x for ClickBench Q32 [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #11792:
URL: https://github.com/apache/datafusion/pull/11792#issuecomment-2269563693
I also measured some non trivial improvements (16 core) along with some slowdowns
```
--------------------
Benchmark clickbench_1.json
--------------------
┏━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ Query ┃ main_base ┃ single-mode-groupby ┃ Change ┃
┡━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ QQuery 0 │ 0.65ms │ 0.66ms │ no change │
│ QQuery 1 │ 70.34ms │ 69.76ms │ no change │
│ QQuery 2 │ 124.93ms │ 125.33ms │ no change │
│ QQuery 3 │ 131.01ms │ 129.34ms │ no change │
│ QQuery 4 │ 917.67ms │ 971.24ms │ 1.06x slower │
│ QQuery 5 │ 1061.52ms │ 1103.04ms │ no change │
│ QQuery 6 │ 65.61ms │ 67.14ms │ no change │
│ QQuery 7 │ 73.85ms │ 72.77ms │ no change │
│ QQuery 8 │ 1422.06ms │ 1199.52ms │ +1.19x faster │
│ QQuery 9 │ 1281.64ms │ 1347.37ms │ 1.05x slower │
│ QQuery 10 │ 446.75ms │ 449.22ms │ no change │
│ QQuery 11 │ 479.20ms │ 512.66ms │ 1.07x slower │
│ QQuery 12 │ 1149.46ms │ 1148.56ms │ no change │
│ QQuery 13 │ 2360.71ms │ 1915.35ms │ +1.23x faster │
│ QQuery 14 │ 1549.49ms │ 1217.84ms │ +1.27x faster │
│ QQuery 15 │ 1054.82ms │ 1090.16ms │ no change │
│ QQuery 16 │ 2843.22ms │ 2396.08ms │ +1.19x faster │
│ QQuery 17 │ 2707.32ms │ 2313.85ms │ +1.17x faster │
│ QQuery 18 │ 5627.90ms │ 4249.07ms │ +1.32x faster │
│ QQuery 19 │ 119.94ms │ 122.76ms │ no change │
│ QQuery 20 │ 1614.43ms │ 1635.75ms │ no change │
│ QQuery 21 │ 1994.01ms │ 2007.84ms │ no change │
│ QQuery 22 │ 4810.56ms │ 4824.01ms │ no change │
│ QQuery 23 │ 11246.33ms │ 11301.58ms │ no change │
│ QQuery 24 │ 754.54ms │ 745.52ms │ no change │
│ QQuery 25 │ 673.00ms │ 663.95ms │ no change │
│ QQuery 26 │ 825.60ms │ 812.36ms │ no change │
│ QQuery 27 │ 2487.83ms │ 2442.42ms │ no change │
│ QQuery 28 │ 15504.67ms │ 15553.00ms │ no change │
│ QQuery 29 │ 565.35ms │ 563.80ms │ no change │
│ QQuery 30 │ 1286.72ms │ 1109.49ms │ +1.16x faster │
│ QQuery 31 │ 1608.83ms │ 1251.64ms │ +1.29x faster │
│ QQuery 32 │ 7555.94ms │ 4070.29ms │ +1.86x faster │
│ QQuery 33 │ 4877.63ms │ 4743.06ms │ no change │
│ QQuery 34 │ 4887.61ms │ 4842.37ms │ no change │
│ QQuery 35 │ 1763.00ms │ 1464.69ms │ +1.20x faster │
│ QQuery 36 │ 314.89ms │ 322.15ms │ no change │
│ QQuery 37 │ 219.26ms │ 219.07ms │ no change │
│ QQuery 38 │ 184.48ms │ 189.16ms │ no change │
│ QQuery 39 │ 990.12ms │ 601.18ms │ +1.65x faster │
│ QQuery 40 │ 85.76ms │ 81.27ms │ +1.06x faster │
│ QQuery 41 │ 79.70ms │ 77.11ms │ no change │
│ QQuery 42 │ 95.45ms │ 93.21ms │ no change │
└──────────────┴────────────┴─────────────────────┴───────────────┘
```
It seems to have hurt TPCH a bit more:
```
--------------------
Benchmark tpch_mem_sf1.json
--------------------
┏━━━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ Query ┃ main_base ┃ single-mode-groupby ┃ Change ┃
┡━━━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ QQuery 1 │ 104.42ms │ 371.48ms │ 3.56x slower │
│ QQuery 2 │ 24.85ms │ 25.11ms │ no change │
│ QQuery 3 │ 39.93ms │ 42.32ms │ 1.06x slower │
│ QQuery 4 │ 32.57ms │ 32.83ms │ no change │
│ QQuery 5 │ 62.08ms │ 60.75ms │ no change │
│ QQuery 6 │ 8.38ms │ 8.65ms │ no change │
│ QQuery 7 │ 115.87ms │ 112.41ms │ no change │
│ QQuery 8 │ 27.71ms │ 27.06ms │ no change │
│ QQuery 9 │ 62.80ms │ 64.47ms │ no change │
│ QQuery 10 │ 72.36ms │ 69.71ms │ no change │
│ QQuery 11 │ 63.73ms │ 63.81ms │ no change │
│ QQuery 12 │ 28.73ms │ 28.54ms │ no change │
│ QQuery 13 │ 39.70ms │ 39.93ms │ no change │
│ QQuery 14 │ 11.17ms │ 11.14ms │ no change │
│ QQuery 15 │ 20.03ms │ 21.21ms │ 1.06x slower │
│ QQuery 16 │ 26.95ms │ 23.04ms │ +1.17x faster │
│ QQuery 17 │ 95.26ms │ 102.77ms │ 1.08x slower │
│ QQuery 18 │ 226.12ms │ 237.74ms │ 1.05x slower │
│ QQuery 19 │ 28.43ms │ 29.93ms │ 1.05x slower │
│ QQuery 20 │ 45.35ms │ 35.96ms │ +1.26x faster │
│ QQuery 21 │ 172.54ms │ 168.60ms │ no change │
│ QQuery 22 │ 13.48ms │ 14.03ms │ no change │
└──────────────┴───────────┴─────────────────────┴───────────────┘
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┓
┃ Benchmark Summary ┃ ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━┩
│ Total Time (main_base) │ 1322.44ms │
│ Total Time (single-mode-groupby) │ 1591.48ms │
│ Average Time (main_base) │ 60.11ms │
│ Average Time (single-mode-groupby) │ 72.34ms │
│ Queries Faster │ 2 │
│ Queries Slower │ 6 │
│ Queries with No Change │ 14 │
└────────────────────────────────────┴───────────┘
--------------------
Benchmark tpch_sf1.json
--------------------
┏━━━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ Query ┃ main_base ┃ single-mode-groupby ┃ Change ┃
┡━━━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ QQuery 1 │ 227.72ms │ 330.23ms │ 1.45x slower │
│ QQuery 2 │ 125.12ms │ 128.35ms │ no change │
│ QQuery 3 │ 124.25ms │ 125.15ms │ no change │
│ QQuery 4 │ 95.52ms │ 93.86ms │ no change │
│ QQuery 5 │ 172.74ms │ 174.37ms │ no change │
│ QQuery 6 │ 58.73ms │ 63.04ms │ 1.07x slower │
│ QQuery 7 │ 203.25ms │ 217.22ms │ 1.07x slower │
│ QQuery 8 │ 158.45ms │ 164.02ms │ no change │
│ QQuery 9 │ 257.60ms │ 258.15ms │ no change │
│ QQuery 10 │ 230.00ms │ 219.76ms │ no change │
│ QQuery 11 │ 96.91ms │ 101.00ms │ no change │
│ QQuery 12 │ 133.99ms │ 129.95ms │ no change │
│ QQuery 13 │ 282.25ms │ 288.57ms │ no change │
│ QQuery 14 │ 87.25ms │ 85.62ms │ no change │
│ QQuery 15 │ 118.44ms │ 149.96ms │ 1.27x slower │
│ QQuery 16 │ 84.88ms │ 62.51ms │ +1.36x faster │
│ QQuery 17 │ 221.45ms │ 218.67ms │ no change │
│ QQuery 18 │ 315.36ms │ 324.40ms │ no change │
│ QQuery 19 │ 159.36ms │ 156.41ms │ no change │
│ QQuery 20 │ 143.00ms │ 122.93ms │ +1.16x faster │
│ QQuery 21 │ 278.87ms │ 291.86ms │ no change │
│ QQuery 22 │ 67.32ms │ 68.52ms │ no change │
└──────────────┴───────────┴─────────────────────┴───────────────┘
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┓
┃ Benchmark Summary ┃ ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━┩
│ Total Time (main_base) │ 3642.44ms │
│ Total Time (single-mode-groupby) │ 3774.56ms │
│ Average Time (main_base) │ 165.57ms │
│ Average Time (single-mode-groupby) │ 171.57ms │
│ Queries Faster │ 2 │
│ Queries Slower │ 4 │
│ Queries with No Change │ 16 │
└────────────────────────────────────┴───────────┘
```
Maybe we can do some profiling and figure out if there is some way to get back the performance on the queries it is slower for
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Single mode for multi column group by -- Almost 2x for ClickBench Q32 [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on code in PR #11792:
URL: https://github.com/apache/datafusion/pull/11792#discussion_r1702446542
##########
datafusion/core/benches/multi_groupby.rs:
##########
@@ -0,0 +1,131 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+use arrow::{
+ datatypes::{DataType, Field, Schema},
+ record_batch::RecordBatch,
+};
+use arrow_array::{Int64Array, StringArray};
+use arrow_schema::SchemaRef;
+use criterion::{criterion_group, criterion_main, Criterion};
+use datafusion::prelude::SessionContext;
+use datafusion::{datasource::MemTable, error::Result};
+use futures::executor::block_on;
+use std::sync::Arc;
+use tokio::runtime::Runtime;
+
+async fn query(ctx: &mut SessionContext, sql: &str) {
+ let rt = Runtime::new().unwrap();
+
+ // execute the query
+ let df = rt.block_on(ctx.sql(sql)).unwrap();
+ criterion::black_box(rt.block_on(df.collect()).unwrap());
+}
+
+fn high_cardinality_batches(
+ array_len: usize,
+ batch_size: usize,
+ schema: SchemaRef,
+) -> Vec<RecordBatch> {
+ (0..array_len / batch_size)
+ .map(|i| {
+ let data1 = (0..batch_size)
+ .map(|j| (batch_size * i + j) as i64)
+ .collect::<Vec<_>>();
+ let data2 = (0..batch_size)
+ .map(|j| format!("a{}", (batch_size * i + j)))
+ .collect::<Vec<_>>();
+
+ RecordBatch::try_new(
+ schema.clone(),
+ vec![
+ Arc::new(Int64Array::from(data1)),
+ Arc::new(StringArray::from(data2)),
+ ],
+ )
+ .unwrap()
+ })
+ .collect::<Vec<_>>()
+}
+
+fn low_cardinality_batches(
+ array_len: usize,
+ batch_size: usize,
+ schema: SchemaRef,
+) -> Vec<RecordBatch> {
+ (0..array_len / batch_size)
+ .map(|i| {
+ let data1 = (0..batch_size)
+ .map(|j| ((batch_size * i + j) % 4 > 1) as i64)
+ .collect::<Vec<_>>();
+ let data2 = (0..batch_size)
+ .map(|j| format!("a{}", ((batch_size * i + j) % 2)))
+ .collect::<Vec<_>>();
+
+ RecordBatch::try_new(
+ schema.clone(),
+ vec![
+ Arc::new(Int64Array::from(data1)),
+ Arc::new(StringArray::from(data2)),
+ ],
+ )
+ .unwrap()
+ })
+ .collect::<Vec<_>>()
+}
+
+fn create_context(
+ batches: Vec<RecordBatch>,
+ schema: SchemaRef,
+) -> Result<SessionContext> {
+ let ctx = SessionContext::new();
+
+ // declare a table in memory. In spark API, this corresponds to createDataFrame(...).
+ let provider = MemTable::try_new(schema, vec![batches])?;
+ ctx.register_table("t", Arc::new(provider))?;
+
+ Ok(ctx)
+}
+
+fn criterion_benchmark(c: &mut Criterion) {
+ let array_len = 2000000; // 2M rows
+ let batch_size = 8192;
+ let schema = Arc::new(Schema::new(vec![
+ Field::new("a", DataType::Int64, false),
+ Field::new("b", DataType::Utf8, false),
+ ]));
+
+ c.bench_function("benchmark high cardinality", |b| {
+ let batches = high_cardinality_batches(array_len, batch_size, Arc::clone(&schema));
+ let mut ctx = create_context(batches, Arc::clone(&schema)).unwrap();
+ b.iter(|| block_on(query(&mut ctx, "select a, b, count(*) from t group by a, b order by count(*) desc limit 10")))
+ });
+
+ c.bench_function("benchmark low cardinality", |b| {
+ let batches = low_cardinality_batches(array_len, batch_size, Arc::clone(&schema));
+ let mut ctx = create_context(batches, Arc::clone(&schema)).unwrap();
+ b.iter(|| block_on(query(&mut ctx, "select a, b, count(*) from t group by a, b order by count(*) desc limit 10")))
+ });
+}
Review Comment:
main branch
```
Gnuplot not found, using plotters backend
Benchmarking benchmark high cardinality: Warming up for 3.0000 s
Warning: Unable to complete 10 samples in 5.0s. You may wish to increase target time to 11.0s.
benchmark high cardinality
time: [565.74 ms 964.40 ms 1.3864 s]
change: [+69.461% +175.35% +332.28%] (p = 0.01 < 0.05)
Performance has regressed.
benchmark low cardinality
time: [10.736 ms 11.140 ms 11.577 ms]
change: [-35.106% -31.565% -28.118%] (p = 0.00 < 0.05)
Performance has improved.
```
This PR has slightly regression for low cardinality but huge gain for high cardinality
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Single mode for multi column group by -- Almost 2x for ClickBench Q32 [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on code in PR #11792:
URL: https://github.com/apache/datafusion/pull/11792#discussion_r1702446542
##########
datafusion/core/benches/multi_groupby.rs:
##########
@@ -0,0 +1,131 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+use arrow::{
+ datatypes::{DataType, Field, Schema},
+ record_batch::RecordBatch,
+};
+use arrow_array::{Int64Array, StringArray};
+use arrow_schema::SchemaRef;
+use criterion::{criterion_group, criterion_main, Criterion};
+use datafusion::prelude::SessionContext;
+use datafusion::{datasource::MemTable, error::Result};
+use futures::executor::block_on;
+use std::sync::Arc;
+use tokio::runtime::Runtime;
+
+async fn query(ctx: &mut SessionContext, sql: &str) {
+ let rt = Runtime::new().unwrap();
+
+ // execute the query
+ let df = rt.block_on(ctx.sql(sql)).unwrap();
+ criterion::black_box(rt.block_on(df.collect()).unwrap());
+}
+
+fn high_cardinality_batches(
+ array_len: usize,
+ batch_size: usize,
+ schema: SchemaRef,
+) -> Vec<RecordBatch> {
+ (0..array_len / batch_size)
+ .map(|i| {
+ let data1 = (0..batch_size)
+ .map(|j| (batch_size * i + j) as i64)
+ .collect::<Vec<_>>();
+ let data2 = (0..batch_size)
+ .map(|j| format!("a{}", (batch_size * i + j)))
+ .collect::<Vec<_>>();
+
+ RecordBatch::try_new(
+ schema.clone(),
+ vec![
+ Arc::new(Int64Array::from(data1)),
+ Arc::new(StringArray::from(data2)),
+ ],
+ )
+ .unwrap()
+ })
+ .collect::<Vec<_>>()
+}
+
+fn low_cardinality_batches(
+ array_len: usize,
+ batch_size: usize,
+ schema: SchemaRef,
+) -> Vec<RecordBatch> {
+ (0..array_len / batch_size)
+ .map(|i| {
+ let data1 = (0..batch_size)
+ .map(|j| ((batch_size * i + j) % 4 > 1) as i64)
+ .collect::<Vec<_>>();
+ let data2 = (0..batch_size)
+ .map(|j| format!("a{}", ((batch_size * i + j) % 2)))
+ .collect::<Vec<_>>();
+
+ RecordBatch::try_new(
+ schema.clone(),
+ vec![
+ Arc::new(Int64Array::from(data1)),
+ Arc::new(StringArray::from(data2)),
+ ],
+ )
+ .unwrap()
+ })
+ .collect::<Vec<_>>()
+}
+
+fn create_context(
+ batches: Vec<RecordBatch>,
+ schema: SchemaRef,
+) -> Result<SessionContext> {
+ let ctx = SessionContext::new();
+
+ // declare a table in memory. In spark API, this corresponds to createDataFrame(...).
+ let provider = MemTable::try_new(schema, vec![batches])?;
+ ctx.register_table("t", Arc::new(provider))?;
+
+ Ok(ctx)
+}
+
+fn criterion_benchmark(c: &mut Criterion) {
+ let array_len = 2000000; // 2M rows
+ let batch_size = 8192;
+ let schema = Arc::new(Schema::new(vec![
+ Field::new("a", DataType::Int64, false),
+ Field::new("b", DataType::Utf8, false),
+ ]));
+
+ c.bench_function("benchmark high cardinality", |b| {
+ let batches = high_cardinality_batches(array_len, batch_size, Arc::clone(&schema));
+ let mut ctx = create_context(batches, Arc::clone(&schema)).unwrap();
+ b.iter(|| block_on(query(&mut ctx, "select a, b, count(*) from t group by a, b order by count(*) desc limit 10")))
+ });
+
+ c.bench_function("benchmark low cardinality", |b| {
+ let batches = low_cardinality_batches(array_len, batch_size, Arc::clone(&schema));
+ let mut ctx = create_context(batches, Arc::clone(&schema)).unwrap();
+ b.iter(|| block_on(query(&mut ctx, "select a, b, count(*) from t group by a, b order by count(*) desc limit 10")))
+ });
+}
Review Comment:
main branch
```
Gnuplot not found, using plotters backend
Benchmarking benchmark high cardinality: Warming up for 3.0000 s
Warning: Unable to complete 10 samples in 5.0s. You may wish to increase target time to 11.0s.
benchmark high cardinality
time: [565.74 ms 964.40 ms 1.3864 s]
change: [+69.461% +175.35% +332.28%] (p = 0.01 < 0.05)
Performance has regressed.
benchmark low cardinality
time: [10.736 ms 11.140 ms 11.577 ms]
change: [-35.106% -31.565% -28.118%] (p = 0.00 < 0.05)
Performance has improved.
```
This change slightly regress for low cardinality for huge gain for high cardinality
##########
datafusion/core/benches/multi_groupby.rs:
##########
@@ -0,0 +1,131 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+use arrow::{
+ datatypes::{DataType, Field, Schema},
+ record_batch::RecordBatch,
+};
+use arrow_array::{Int64Array, StringArray};
+use arrow_schema::SchemaRef;
+use criterion::{criterion_group, criterion_main, Criterion};
+use datafusion::prelude::SessionContext;
+use datafusion::{datasource::MemTable, error::Result};
+use futures::executor::block_on;
+use std::sync::Arc;
+use tokio::runtime::Runtime;
+
+async fn query(ctx: &mut SessionContext, sql: &str) {
+ let rt = Runtime::new().unwrap();
+
+ // execute the query
+ let df = rt.block_on(ctx.sql(sql)).unwrap();
+ criterion::black_box(rt.block_on(df.collect()).unwrap());
+}
+
+fn high_cardinality_batches(
+ array_len: usize,
+ batch_size: usize,
+ schema: SchemaRef,
+) -> Vec<RecordBatch> {
+ (0..array_len / batch_size)
+ .map(|i| {
+ let data1 = (0..batch_size)
+ .map(|j| (batch_size * i + j) as i64)
+ .collect::<Vec<_>>();
+ let data2 = (0..batch_size)
+ .map(|j| format!("a{}", (batch_size * i + j)))
+ .collect::<Vec<_>>();
+
+ RecordBatch::try_new(
+ schema.clone(),
+ vec![
+ Arc::new(Int64Array::from(data1)),
+ Arc::new(StringArray::from(data2)),
+ ],
+ )
+ .unwrap()
+ })
+ .collect::<Vec<_>>()
+}
+
+fn low_cardinality_batches(
+ array_len: usize,
+ batch_size: usize,
+ schema: SchemaRef,
+) -> Vec<RecordBatch> {
+ (0..array_len / batch_size)
+ .map(|i| {
+ let data1 = (0..batch_size)
+ .map(|j| ((batch_size * i + j) % 4 > 1) as i64)
+ .collect::<Vec<_>>();
+ let data2 = (0..batch_size)
+ .map(|j| format!("a{}", ((batch_size * i + j) % 2)))
+ .collect::<Vec<_>>();
+
+ RecordBatch::try_new(
+ schema.clone(),
+ vec![
+ Arc::new(Int64Array::from(data1)),
+ Arc::new(StringArray::from(data2)),
+ ],
+ )
+ .unwrap()
+ })
+ .collect::<Vec<_>>()
+}
+
+fn create_context(
+ batches: Vec<RecordBatch>,
+ schema: SchemaRef,
+) -> Result<SessionContext> {
+ let ctx = SessionContext::new();
+
+ // declare a table in memory. In spark API, this corresponds to createDataFrame(...).
+ let provider = MemTable::try_new(schema, vec![batches])?;
+ ctx.register_table("t", Arc::new(provider))?;
+
+ Ok(ctx)
+}
+
+fn criterion_benchmark(c: &mut Criterion) {
+ let array_len = 2000000; // 2M rows
+ let batch_size = 8192;
+ let schema = Arc::new(Schema::new(vec![
+ Field::new("a", DataType::Int64, false),
+ Field::new("b", DataType::Utf8, false),
+ ]));
+
+ c.bench_function("benchmark high cardinality", |b| {
+ let batches = high_cardinality_batches(array_len, batch_size, Arc::clone(&schema));
+ let mut ctx = create_context(batches, Arc::clone(&schema)).unwrap();
+ b.iter(|| block_on(query(&mut ctx, "select a, b, count(*) from t group by a, b order by count(*) desc limit 10")))
+ });
+
+ c.bench_function("benchmark low cardinality", |b| {
+ let batches = low_cardinality_batches(array_len, batch_size, Arc::clone(&schema));
+ let mut ctx = create_context(batches, Arc::clone(&schema)).unwrap();
+ b.iter(|| block_on(query(&mut ctx, "select a, b, count(*) from t group by a, b order by count(*) desc limit 10")))
+ });
+}
Review Comment:
main branch
```
Gnuplot not found, using plotters backend
Benchmarking benchmark high cardinality: Warming up for 3.0000 s
Warning: Unable to complete 10 samples in 5.0s. You may wish to increase target time to 11.0s.
benchmark high cardinality
time: [565.74 ms 964.40 ms 1.3864 s]
change: [+69.461% +175.35% +332.28%] (p = 0.01 < 0.05)
Performance has regressed.
benchmark low cardinality
time: [10.736 ms 11.140 ms 11.577 ms]
change: [-35.106% -31.565% -28.118%] (p = 0.00 < 0.05)
Performance has improved.
```
This change slightly regress for low cardinality but huge gain for high cardinality
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Single mode for multi column group by -- Almost 2x for ClickBench Q32 [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on code in PR #11792:
URL: https://github.com/apache/datafusion/pull/11792#discussion_r1702445342
##########
datafusion/core/benches/multi_groupby.rs:
##########
@@ -0,0 +1,131 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+use arrow::{
+ datatypes::{DataType, Field, Schema},
+ record_batch::RecordBatch,
+};
+use arrow_array::{Int64Array, StringArray};
+use arrow_schema::SchemaRef;
+use criterion::{criterion_group, criterion_main, Criterion};
+use datafusion::prelude::SessionContext;
+use datafusion::{datasource::MemTable, error::Result};
+use futures::executor::block_on;
+use std::sync::Arc;
+use tokio::runtime::Runtime;
+
+async fn query(ctx: &mut SessionContext, sql: &str) {
+ let rt = Runtime::new().unwrap();
+
+ // execute the query
+ let df = rt.block_on(ctx.sql(sql)).unwrap();
+ criterion::black_box(rt.block_on(df.collect()).unwrap());
+}
+
+fn high_cardinality_batches(
+ array_len: usize,
+ batch_size: usize,
+ schema: SchemaRef,
+) -> Vec<RecordBatch> {
+ (0..array_len / batch_size)
+ .map(|i| {
+ let data1 = (0..batch_size)
+ .map(|j| (batch_size * i + j) as i64)
+ .collect::<Vec<_>>();
+ let data2 = (0..batch_size)
+ .map(|j| format!("a{}", (batch_size * i + j)))
+ .collect::<Vec<_>>();
+
+ RecordBatch::try_new(
+ schema.clone(),
+ vec![
+ Arc::new(Int64Array::from(data1)),
+ Arc::new(StringArray::from(data2)),
+ ],
+ )
+ .unwrap()
+ })
+ .collect::<Vec<_>>()
+}
+
+fn low_cardinality_batches(
+ array_len: usize,
+ batch_size: usize,
+ schema: SchemaRef,
+) -> Vec<RecordBatch> {
+ (0..array_len / batch_size)
+ .map(|i| {
+ let data1 = (0..batch_size)
+ .map(|j| ((batch_size * i + j) % 4 > 1) as i64)
+ .collect::<Vec<_>>();
+ let data2 = (0..batch_size)
+ .map(|j| format!("a{}", ((batch_size * i + j) % 2)))
+ .collect::<Vec<_>>();
+
+ RecordBatch::try_new(
+ schema.clone(),
+ vec![
+ Arc::new(Int64Array::from(data1)),
+ Arc::new(StringArray::from(data2)),
+ ],
+ )
+ .unwrap()
+ })
+ .collect::<Vec<_>>()
+}
+
+fn create_context(
+ batches: Vec<RecordBatch>,
+ schema: SchemaRef,
+) -> Result<SessionContext> {
+ let ctx = SessionContext::new();
+
+ // declare a table in memory. In spark API, this corresponds to createDataFrame(...).
+ let provider = MemTable::try_new(schema, vec![batches])?;
+ ctx.register_table("t", Arc::new(provider))?;
+
+ Ok(ctx)
+}
+
+fn criterion_benchmark(c: &mut Criterion) {
+ let array_len = 2000000; // 2M rows
+ let batch_size = 8192;
+ let schema = Arc::new(Schema::new(vec![
+ Field::new("a", DataType::Int64, false),
+ Field::new("b", DataType::Utf8, false),
+ ]));
+
+ c.bench_function("benchmark high cardinality", |b| {
+ let batches = high_cardinality_batches(array_len, batch_size, Arc::clone(&schema));
+ let mut ctx = create_context(batches, Arc::clone(&schema)).unwrap();
+ b.iter(|| block_on(query(&mut ctx, "select a, b, count(*) from t group by a, b order by count(*) desc limit 10")))
+ });
+
+ c.bench_function("benchmark low cardinality", |b| {
+ let batches = low_cardinality_batches(array_len, batch_size, Arc::clone(&schema));
+ let mut ctx = create_context(batches, Arc::clone(&schema)).unwrap();
+ b.iter(|| block_on(query(&mut ctx, "select a, b, count(*) from t group by a, b order by count(*) desc limit 10")))
+ });
+}
Review Comment:
```
Gnuplot not found, using plotters backend
benchmark high cardinality
time: [273.00 ms 350.24 ms 451.67 ms]
Found 1 outliers among 10 measurements (10.00%)
1 (10.00%) high mild
benchmark low cardinality
time: [15.764 ms 16.531 ms 17.145 ms]
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Single mode for multi column group by -- Almost 2x for ClickBench Q32 [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #11792:
URL: https://github.com/apache/datafusion/pull/11792#issuecomment-2275576800
Here are some more thoughts about why this approach works and an alternate idea of how we can make these queries all faster: https://github.com/apache/datafusion/issues/11680#issuecomment-2275575262
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org