Skip to content

Commit b987501

Browse files
adriangbclaude
andauthored
bench: add a ClickBench extended query for a grouped COUNT(DISTINCT) over a string (apache#25026)
## Which issue does this PR close? No existing issue. This is benchmark coverage carved out of apache#24859 so that it can land first: a benchmark query added in the same PR that needs it cannot appear in an A/B run, because the bot compares against the merge base and the merge base does not have the query. ## Rationale for this change `COUNT(DISTINCT)` has a specialized `GroupsAccumulator` for the integer types and for no other type. Every other type falls back to `GroupsAccumulatorAdapter`, which holds one boxed `Accumulator`, and therefore one hash table, for each group. The cost of that fallback is per group, so the group cardinality is what decides how much it costs. Nothing in either suite measures that: - Extended Q2 is the only query that puts a `COUNT(DISTINCT)` on a non-integer column at all. It groups by `BrowserCountry`, so it exercises the adapter at a low group cardinality, which is where the adapter is cheapest. - Standard Q8, Q10, Q11 and Q13 hold a distinct aggregate that stands alone, so `SingleDistinctToGroupBy` already rewrites them and they never reach the adapter. - Standard Q9 carries an `AVG`, which that rule has never accepted. - Standard Q22 is the one query that pairs a lone distinct aggregate with a non-distinct count, and it counts distinct `UserID`, an `Int64`, which has a specialized accumulator. So a lone `COUNT(DISTINCT <string>)` grouped by a high cardinality key, next to a non-distinct `COUNT(*)`, is uncovered. That is an ordinary analytics shape, it is the shape apache#24857 changed the memory profile of, and it is the shape apache#24859 proposes to rewrite. Neither of those could show its effect on any benchmark in this repository. ## What changes are included in this PR? The query: ```sql SELECT "SearchPhrase", COUNT(*) AS c, COUNT(DISTINCT "MobilePhoneModel") AS models FROM hits WHERE "SearchPhrase" <> '' GROUP BY "SearchPhrase" ORDER BY c DESC LIMIT 10; ``` The extended ClickBench queries live in three places, and a query added to only one of them is invisible to the others. This PR adds it to all three: - `benchmarks/queries/clickbench/extended/q14.sql`, which feeds `dfbench clickbench --queries-path`. Queries are discovered from the directory, so the runner needs no change. - `benchmarks/sql_benchmarks/clickbench_extended/benchmarks/q14.benchmark`, which feeds `benchmark_runner clickbench_extended`. Structurally identical to `q13.benchmark` apart from the query. Suite files are also discovered from the directory; `benchmark_runner clickbench_extended --list` now reports 15 queries. - `datafusion/sqllogictest/test_files/clickbench_extended.slt`, which runs the extended queries against the committed ten row `clickbench_hits_10.parquet` fixture. That last file had only ever mirrored q0 through q6. q7 through q13 were added to the queries directory without a matching sqllogictest entry, and q14 would have widened that gap, so this backfills q7 through q14 together. That is why the diff is larger than one query. Having to add one query in three places is itself the problem, and the copies have already drifted: extended q6 carries a cast in its `sql_benchmarks` copy that the other two do not have. I filed apache#25031 for that; it is out of scope here. There is one pre-existing staleness this PR does not fix. `datafusion/core/benches/sql_planner.rs` builds its ClickBench planning set from a hardcoded `(0..=7)` over the extended directory, so extended Q8 through Q13 were already outside it before this PR and Q14 joins them. That is a separate cleanup. ## What is the testing strategy for this PR? The sqllogictest entries are the test. `clickbench_extended.slt` runs every extended query against the committed ten row fixture and asserts its output, so q7 through q14 are now executed on every CI run rather than only by whoever runs the benchmark suite by hand. `cargo test -p datafusion-sqllogictest --test sqllogictests -- clickbench` passes, 2 of 2 files. `cargo test -p datafusion-benchmarks` passes, 192 tests, and `benchmark_runner clickbench_extended --list` reports the suite at 15 queries, confirming the new `.benchmark` file parses and is discovered. I do not have a `hits.parquet` to hand, so I have not run the query against the full dataset. The plan shape, which is the whole value of the query, was checked separately: on an equivalent local table with the same filter, grouping, `COUNT(*)` and string `COUNT(DISTINCT)`, the aggregate keeps `aggr=[[count(Int64(1)), count(DISTINCT ...)]]`, which is the `GroupsAccumulatorAdapter` path this query exists to measure, and it is not rewritten away. What I have not established is the effect size on the real dataset, since that depends on how many distinct `SearchPhrase` values survive the filter. If a reviewer with the data runs it, that number is worth having on this PR. `ci/scripts/doc_prettier_check.sh` passes on the README change. ## Are there any user-facing changes? No. This adds a benchmark query and documentation only. --------- Co-authored-by: Claude Opus 5 <[email protected]>
1 parent 6ab4ce6 commit b987501

4 files changed

Lines changed: 139 additions & 0 deletions

File tree

‎benchmarks/queries/clickbench/README.md‎

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -261,6 +261,44 @@ WHERE "URL" < 'zzzz';
261261
```
262262

263263

264+
### Q14: Grouped `COUNT(DISTINCT <string>)` beside a non-distinct `COUNT(*)`
265+
266+
**Question**: "For the top 10 search phrases by hit count, how many distinct
267+
mobile phone models were used?"
268+
269+
**Important Query Properties**: A single `COUNT(DISTINCT)` over a high
270+
cardinality string, next to a non-distinct `COUNT(*)`, grouped by a high
271+
cardinality string.
272+
273+
`COUNT(DISTINCT)` has a specialized `GroupsAccumulator` for the integer types
274+
and for no other type. Every other type falls back to
275+
`GroupsAccumulatorAdapter`, which holds one boxed `Accumulator`, and therefore
276+
one hash table, for each group. The cost of that fallback is per group, so it is
277+
the group cardinality that decides how much it costs.
278+
279+
Extended Q2 above is the only other query that puts a `COUNT(DISTINCT)` on a
280+
non-integer column, and it groups by `BrowserCountry`. This query groups by
281+
`SearchPhrase` instead, which has far more distinct values, so it exercises the
282+
adapter where its cost actually scales.
283+
284+
The shape is also the one `SingleDistinctToGroupBy` reasons about. That rule
285+
rewrites a lone distinct aggregate into a two phase group by, which is what
286+
keeps it off the adapter. Standard Q8, Q10, Q11 and Q13 are already rewritten
287+
because their distinct aggregate stands alone, and Q9 is not because it carries
288+
an `AVG`. Q22 is the only standard query that pairs a lone distinct aggregate
289+
with a non-distinct count, and its distinct argument is an `Int64`, which has a
290+
specialized accumulator and never reaches the adapter. So no query in either
291+
suite covers a lone `COUNT(DISTINCT <string>)` next to a non-distinct count.
292+
293+
```sql
294+
SELECT "SearchPhrase", COUNT(*) AS c, COUNT(DISTINCT "MobilePhoneModel") AS models
295+
FROM hits
296+
WHERE "SearchPhrase" <> ''
297+
GROUP BY "SearchPhrase"
298+
ORDER BY c DESC
299+
LIMIT 10;
300+
```
301+
264302

265303

266304
## Data Notes
Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
-- Must set for ClickBench hits_partitioned dataset. See https://github.com/apache/datafusion/issues/16591
2+
-- set datafusion.execution.parquet.binary_as_string = true
3+
4+
-- A grouped COUNT(DISTINCT <string>) beside a non-distinct COUNT(*). No query in
5+
-- the standard suite has this shape: q22 is the only one that pairs a lone
6+
-- distinct aggregate with a non-distinct count, and its distinct argument is an
7+
-- Int64, which has a specialized GroupsAccumulator. A string argument has none,
8+
-- so one boxed Accumulator, and one hash table, is built for every group.
9+
SELECT "SearchPhrase", COUNT(*) AS c, COUNT(DISTINCT "MobilePhoneModel") AS models FROM hits WHERE "SearchPhrase" <> '' GROUP BY "SearchPhrase" ORDER BY c DESC LIMIT 10;
Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
name Q14
2+
group clickbench_extended
3+
subgroup ${CLICKBENCH_TYPE:-single}
4+
5+
init sql_benchmarks/clickbench/init/set_config.sql
6+
7+
load sql_benchmarks/clickbench/init/load-${CLICKBENCH_TYPE:-single}.sql
8+
9+
assert I
10+
SELECT COUNT(*) > 0 from hits;
11+
----
12+
true
13+
14+
run
15+
SELECT "SearchPhrase", COUNT(*) AS c, COUNT(DISTINCT "MobilePhoneModel") AS models
16+
FROM hits
17+
WHERE "SearchPhrase" <> ''
18+
GROUP BY "SearchPhrase"
19+
ORDER BY c DESC
20+
LIMIT 10;
21+
22+
result sql_benchmarks/clickbench_extended/results/q14.csv

‎datafusion/sqllogictest/test_files/clickbench_extended.slt‎

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -61,6 +61,76 @@ SELECT COUNT(*) AS ShareCount FROM hits WHERE "IsMobile" = 1 AND "MobilePhoneMod
6161
----
6262
0
6363

64+
query IIII
65+
SELECT "WatchID", MIN("ResolutionWidth") as wmin, MAX("ResolutionWidth") as wmax, SUM("IsRefresh") as srefresh FROM hits GROUP BY "WatchID" ORDER BY "WatchID" DESC LIMIT 10;
66+
----
67+
9110818468285196899 0 0 0
68+
8924809397503602651 0 0 0
69+
8740403056911509777 0 0 0
70+
8156744413230856864 0 0 0
71+
8120543446287442873 0 0 0
72+
6864353419233967042 0 0 0
73+
6635790769678439148 0 0 0
74+
6308646140879811077 0 0 0
75+
5206346422301499756 0 0 0
76+
4894690465724379622 0 0 0
77+
78+
query III??
79+
SELECT "RegionID", "UserAgent", "OS", AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ResponseStartTiming")) as avg_response_time, AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ConnectTiming")) as avg_latency FROM hits GROUP BY "RegionID", "UserAgent", "OS" ORDER BY avg_latency DESC limit 10;
80+
----
81+
229 3 44 0 days 0 hours 0 mins 0.000000000 secs 0 days 0 hours 0 mins 0.000000000 secs
82+
197 7 39 0 days 0 hours 0 mins 0.000000000 secs 0 days 0 hours 0 mins 0.000000000 secs
83+
839 3 2 0 days 0 hours 0 mins 0.000000000 secs 0 days 0 hours 0 mins 0.000000000 secs
84+
839 0 0 0 days 0 hours 0 mins 0.000000000 secs 0 days 0 hours 0 mins 0.000000000 secs
85+
39 7 2 0 days 0 hours 0 mins 0.000000000 secs 0 days 0 hours 0 mins 0.000000000 secs
86+
87+
query I
88+
SELECT MAX(len) FROM (
89+
SELECT LENGTH(FIRST_VALUE("URL" ORDER BY "EventTime")) as len
90+
FROM hits
91+
GROUP BY "UserID"
92+
);
93+
----
94+
72
95+
96+
query I
97+
SELECT MAX(len) FROM (
98+
SELECT LENGTH(FIRST_VALUE("URL" ORDER BY "EventTime")) as len
99+
FROM hits
100+
GROUP BY "OS"
101+
);
102+
----
103+
57
104+
105+
query I
106+
SELECT MAX(fv) FROM (
107+
SELECT FIRST_VALUE("WatchID" ORDER BY "EventTime") as fv
108+
FROM hits
109+
GROUP BY "UserID"
110+
);
111+
----
112+
9110818468285196899
113+
114+
query I
115+
SELECT MAX(fv) FROM (
116+
SELECT FIRST_VALUE("WatchID" ORDER BY "EventTime") as fv
117+
FROM hits
118+
GROUP BY "OS"
119+
);
120+
----
121+
9110818468285196899
122+
123+
query I
124+
SELECT SUM("CounterID") AS counter_id_sum
125+
FROM hits
126+
WHERE "URL" < 'zzzz';
127+
----
128+
173
129+
130+
query TII
131+
SELECT "SearchPhrase", COUNT(*) AS c, COUNT(DISTINCT "MobilePhoneModel") AS models FROM hits WHERE "SearchPhrase" <> '' GROUP BY "SearchPhrase" ORDER BY c DESC LIMIT 10;
132+
----
133+
64134

65135
statement ok
66136
drop table hits;

0 commit comments

Comments
 (0)