2010YOUY01 commented on code in PR #17636: URL: https://github.com/apache/datafusion/pull/17636#discussion_r2366278386
########## benchmarks/src/hj.rs: ########## @@ -0,0 +1,271 @@ +// 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 crate::util::{BenchmarkRun, CommonOpt, QueryResult}; +use datafusion::physical_plan::execute_stream; +use datafusion::{error::Result, prelude::SessionContext}; +use datafusion_common::instant::Instant; +use datafusion_common::{exec_datafusion_err, exec_err, DataFusionError}; +use structopt::StructOpt; + +use futures::StreamExt; + +// TODO: Add existence joins + +/// Run the Hash Join benchmark +/// +/// This micro-benchmark focuses on the performance characteristics of Hash Joins. +/// It uses simple equality predicates to ensure a hash join is selected. +/// Where we vary selectivity, we do so with additional cheap predicates that +/// do not change the join key (so the physical operator remains HashJoin). +#[derive(Debug, StructOpt, Clone)] +#[structopt(verbatim_doc_comment)] +pub struct RunOpt { + /// Query number (between 1 and 12). If not specified, runs all queries + #[structopt(short, long)] + query: Option<usize>, + + /// Common options (iterations, batch size, target_partitions, etc.) + #[structopt(flatten)] + common: CommonOpt, + + /// If present, write results json here + #[structopt(parse(from_os_str), short = "o", long = "output")] + output_path: Option<std::path::PathBuf>, +} + +/// Inline SQL queries for Hash Join benchmarks +/// +/// Each query's comment includes: +/// - Left row count × Right row count +/// - Join predicate selectivity (approximate output fraction). +const HASH_QUERIES: &[&str] = &[ + // Q1: INNER 10K x 10K | LOW ~0.1% + // equality on key + cheap filter to downselect + r#" + SELECT t1.value, t2.value + FROM generate_series(0,10000, 1000) AS t1(value) + JOIN range(10000) AS t2 + ON t1.value = t2.value; + "#, + // Q2: INNER 10K x 10K | MEDIUM ~20% + r#" + SELECT t1.value, t2.value + FROM range(10000) AS t1 + JOIN range(10000) AS t2 + ON t1.value = t2.value + WHERE t1.value % 5 = 0 + "#, + // Q3: INNER 10K x 10K | HIGH ~90% + r#" + SELECT t1.value, t2.value + FROM range(10000) AS t1 + JOIN range(10000) AS t2 + ON t1.value = t2.value + WHERE t1.value % 10 <> 0 + "#, + // Q4: INNER 30K x 30K | MEDIUM ~20% + r#" + SELECT t1.value, t2.value + FROM range(30000) AS t1 + JOIN range(30000) AS t2 + ON t1.value = t2.value + WHERE t1.value % 5 = 0 + "#, + // Q5: INNER 10K x 200K | LOW ~0.1% (small to large) + r#" + SELECT t1.value, t2.value + FROM range(10000) AS t1 + JOIN range(200000) AS t2 + ON t1.value = t2.value + WHERE t1.value % 1000 = 0 + "#, + // Q6: INNER 200K x 10K | LOW ~0.1% (large to small) + r#" + SELECT t1.value, t2.value + FROM range(200000) AS t1 + JOIN range(10000) AS t2 + ON t1.value = t2.value + WHERE t1.value % 1000 = 0 + "#, + // Q7: RIGHT OUTER 10K x 200K | LOW ~0.1% + // Outer join still uses HashJoin for equi-keys; the extra filter reduces matches + r#" + SELECT t1.value AS l, t2.value AS r + FROM range(10000) AS t1 + RIGHT JOIN range(200000) AS t2 + ON t1.value = t2.value + WHERE t2.value % 1000 = 0 + "#, + // Q8: LEFT OUTER 200K x 10K | LOW ~0.1% + r#" + SELECT t1.value AS l, t2.value AS r + FROM range(200000) AS t1 + LEFT JOIN range(10000) AS t2 + ON t1.value = t2.value + WHERE t1.value % 1000 = 0 + "#, + // Q9: FULL OUTER 30K x 30K | LOW ~0.1% + r#" + SELECT t1.value AS l, t2.value AS r + FROM range(30000) AS t1 + FULL JOIN range(30000) AS t2 + ON t1.value = t2.value + WHERE COALESCE(t1.value, t2.value) % 1000 = 0 + "#, + // Q10: FULL OUTER 30K x 30K | HIGH ~90% + r#" + SELECT t1.value AS l, t2.value AS r + FROM range(30000) AS t1 + FULL JOIN range(30000) AS t2 + ON t1.value = t2.value + WHERE COALESCE(t1.value, t2.value) % 10 <> 0 + "#, + // Q11: INNER 30K x 30K | MEDIUM ~50% | cheap predicate on parity + r#" + SELECT t1.value, t2.value + FROM range(30000) AS t1 + INNER JOIN range(30000) AS t2 + ON (t1.value % 2) = (t2.value % 2) + "#, + // Q12: FULL OUTER 30K x 30K | MEDIUM ~50% | expression key Review Comment: q11 and q12's selectivity is relative to Cartesian product, and other queries is relative to probe side size, let's mark it ########## benchmarks/src/hj.rs: ########## @@ -150,6 +149,20 @@ const HASH_QUERIES: &[&str] = &[ FULL JOIN range(30000) AS t2 ON (t1.value % 2) = (t2.value % 2) "#, + // Q13: INNER 30K x 30K | MEDIUM ~33% | double predicate + r#" + SELECT t1.value, t2.value + FROM range(30000) AS t1 + INNER JOIN range(30000) AS t2 + ON (t1.value = t2.value) AND (t1.value > 10000 and t2.value < 20000) Review Comment: and also update the selectivity for q13 and q14, 50% seem not correct for q14 -- 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-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
