1use std::collections::HashMap;
2
3#[derive(Debug, Clone, PartialEq)]
4pub enum QueryCategory {
5 BasicOperations,
6 Aggregations,
7 SortingAndLimits,
8 WindowFunctions,
9 ComplexQueries,
10}
11
12impl QueryCategory {
13 pub fn as_str(&self) -> &str {
14 match self {
15 QueryCategory::BasicOperations => "basic",
16 QueryCategory::Aggregations => "aggregation",
17 QueryCategory::SortingAndLimits => "sorting",
18 QueryCategory::WindowFunctions => "window",
19 QueryCategory::ComplexQueries => "complex",
20 }
21 }
22}
23
24#[derive(Debug, Clone)]
25pub struct BenchmarkQuery {
26 pub name: String,
27 pub category: QueryCategory,
28 pub sql: String,
29 pub description: String,
30 pub table_type: String,
31}
32
33impl BenchmarkQuery {
34 pub fn new(
35 name: impl Into<String>,
36 category: QueryCategory,
37 sql: impl Into<String>,
38 description: impl Into<String>,
39 table_type: impl Into<String>,
40 ) -> Self {
41 BenchmarkQuery {
42 name: name.into(),
43 category,
44 sql: sql.into(),
45 description: description.into(),
46 table_type: table_type.into(),
47 }
48 }
49}
50
51pub struct QuerySuite;
52
53impl QuerySuite {
54 pub fn get_basic_queries() -> Vec<BenchmarkQuery> {
55 vec![
56 BenchmarkQuery::new(
57 "full_scan",
58 QueryCategory::BasicOperations,
59 "SELECT * FROM bench",
60 "Full table scan",
61 "all",
62 ),
63 BenchmarkQuery::new(
64 "simple_filter",
65 QueryCategory::BasicOperations,
66 "SELECT * FROM bench WHERE id > 50000",
67 "Simple filter on indexed column",
68 "all",
69 ),
70 BenchmarkQuery::new(
71 "multi_condition",
72 QueryCategory::BasicOperations,
73 "SELECT * FROM bench WHERE category = 'Electronics' AND price > 500",
74 "Multiple conditions",
75 "mixed",
76 ),
77 BenchmarkQuery::new(
78 "string_equality",
79 QueryCategory::BasicOperations,
80 "SELECT * FROM bench WHERE status = 'Active'",
81 "String equality filter",
82 "mixed",
83 ),
84 BenchmarkQuery::new(
85 "like_pattern",
86 QueryCategory::BasicOperations,
87 "SELECT * FROM bench WHERE status LIKE 'Act%'",
88 "LIKE pattern matching",
89 "mixed",
90 ),
91 BenchmarkQuery::new(
92 "in_operator",
93 QueryCategory::BasicOperations,
94 "SELECT * FROM bench WHERE category IN ('Electronics', 'Books', 'Clothing')",
95 "IN operator with multiple values",
96 "mixed",
97 ),
98 BenchmarkQuery::new(
99 "between_range",
100 QueryCategory::BasicOperations,
101 "SELECT * FROM bench WHERE price BETWEEN 100 AND 500",
102 "BETWEEN range filter",
103 "mixed",
104 ),
105 BenchmarkQuery::new(
106 "projection",
107 QueryCategory::BasicOperations,
108 "SELECT id, price, quantity FROM bench",
109 "Column projection",
110 "mixed",
111 ),
112 BenchmarkQuery::new(
113 "calculated_column",
114 QueryCategory::BasicOperations,
115 "SELECT id, price * quantity AS total FROM bench",
116 "Calculated column",
117 "mixed",
118 ),
119 ]
120 }
121
122 pub fn get_aggregation_queries() -> Vec<BenchmarkQuery> {
123 vec![
124 BenchmarkQuery::new(
125 "simple_aggregates",
126 QueryCategory::Aggregations,
127 "SELECT COUNT(*), SUM(value1), AVG(value2), MIN(value3), MAX(value3) FROM bench",
128 "Simple aggregate functions",
129 "aggregation",
130 ),
131 BenchmarkQuery::new(
132 "group_by_single",
133 QueryCategory::Aggregations,
134 "SELECT group_id, COUNT(*), SUM(value1) FROM bench GROUP BY group_id",
135 "GROUP BY single column",
136 "aggregation",
137 ),
138 BenchmarkQuery::new(
139 "group_by_multiple",
140 QueryCategory::Aggregations,
141 "SELECT group_id, sub_group, SUM(value1), AVG(value2) FROM bench GROUP BY group_id, sub_group",
142 "GROUP BY multiple columns",
143 "aggregation",
144 ),
145 BenchmarkQuery::new(
146 "having_clause",
147 QueryCategory::Aggregations,
148 "SELECT group_id, AVG(value1) AS avg_val FROM bench GROUP BY group_id HAVING AVG(value1) > 500",
149 "GROUP BY with HAVING",
150 "aggregation",
151 ),
152 BenchmarkQuery::new(
153 "count_distinct",
154 QueryCategory::Aggregations,
155 "SELECT COUNT(DISTINCT group_id), COUNT(DISTINCT sub_group) FROM bench",
156 "COUNT DISTINCT",
157 "aggregation",
158 ),
159 BenchmarkQuery::new(
160 "filtered_aggregation",
161 QueryCategory::Aggregations,
162 "SELECT COUNT(*), SUM(value1) FROM bench WHERE group_id > 10",
163 "Aggregation with WHERE filter",
164 "aggregation",
165 ),
166 ]
167 }
168
169 pub fn get_sorting_queries() -> Vec<BenchmarkQuery> {
170 vec![
171 BenchmarkQuery::new(
172 "order_by_single",
173 QueryCategory::SortingAndLimits,
174 "SELECT * FROM bench ORDER BY price DESC",
175 "ORDER BY single column",
176 "mixed",
177 ),
178 BenchmarkQuery::new(
179 "order_by_multiple",
180 QueryCategory::SortingAndLimits,
181 "SELECT * FROM bench ORDER BY category, price DESC",
182 "ORDER BY multiple columns",
183 "mixed",
184 ),
185 BenchmarkQuery::new(
186 "top_n",
187 QueryCategory::SortingAndLimits,
188 "SELECT * FROM bench ORDER BY price DESC LIMIT 100",
189 "TOP-N query",
190 "mixed",
191 ),
192 BenchmarkQuery::new(
193 "offset_pagination",
194 QueryCategory::SortingAndLimits,
195 "SELECT * FROM bench ORDER BY id LIMIT 100 OFFSET 1000",
196 "OFFSET pagination",
197 "mixed",
198 ),
199 BenchmarkQuery::new(
200 "sorted_aggregation",
201 QueryCategory::SortingAndLimits,
202 "SELECT category, SUM(price) AS total FROM bench GROUP BY category ORDER BY total DESC",
203 "Sorted aggregation results",
204 "mixed",
205 ),
206 ]
207 }
208
209 pub fn get_window_queries() -> Vec<BenchmarkQuery> {
210 vec![
211 BenchmarkQuery::new(
212 "row_number",
213 QueryCategory::WindowFunctions,
214 "SELECT *, ROW_NUMBER() OVER (ORDER BY sales DESC) AS rank FROM bench",
215 "ROW_NUMBER window function",
216 "window",
217 ),
218 BenchmarkQuery::new(
219 "running_total",
220 QueryCategory::WindowFunctions,
221 "SELECT *, SUM(sales) OVER (ORDER BY timestamp) AS running_total FROM bench",
222 "Running total with SUM OVER",
223 "window",
224 ),
225 BenchmarkQuery::new(
226 "lag_lead",
227 QueryCategory::WindowFunctions,
228 "SELECT *, LAG(sales, 1) OVER (ORDER BY timestamp) AS prev_sales FROM bench",
229 "LAG window function",
230 "window",
231 ),
232 BenchmarkQuery::new(
233 "partitioned_rank",
234 QueryCategory::WindowFunctions,
235 "SELECT *, RANK() OVER (PARTITION BY department ORDER BY sales DESC) AS dept_rank FROM bench",
236 "Partitioned RANK",
237 "window",
238 ),
239 BenchmarkQuery::new(
240 "moving_average",
241 QueryCategory::WindowFunctions,
242 "SELECT *, AVG(sales) OVER (ORDER BY timestamp ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS moving_avg FROM bench",
243 "Moving average window",
244 "window",
245 ),
246 BenchmarkQuery::new(
247 "percent_rank",
248 QueryCategory::WindowFunctions,
249 "SELECT *, PERCENT_RANK() OVER (ORDER BY sales) AS pct_rank FROM bench",
250 "PERCENT_RANK window function",
251 "window",
252 ),
253 ]
254 }
255
256 pub fn get_complex_queries() -> Vec<BenchmarkQuery> {
257 vec![
258 BenchmarkQuery::new(
259 "simple_cte",
260 QueryCategory::ComplexQueries,
261 "WITH summary AS (SELECT group_id, AVG(value1) as avg_val FROM bench GROUP BY group_id) SELECT * FROM summary WHERE avg_val > 500",
262 "Simple CTE",
263 "aggregation",
264 ),
265 BenchmarkQuery::new(
266 "nested_cte",
267 QueryCategory::ComplexQueries,
268 "WITH level1 AS (SELECT * FROM bench WHERE group_id IN (1,2,3)), level2 AS (SELECT * FROM level1 WHERE value1 > 100) SELECT COUNT(*) FROM level2",
269 "Nested CTEs",
270 "aggregation",
271 ),
272 BenchmarkQuery::new(
273 "complex_expression",
274 QueryCategory::ComplexQueries,
275 "SELECT id, price * quantity * 1.1 AS total_with_tax, CASE WHEN price > 1000 THEN 'Premium' WHEN price > 500 THEN 'Standard' ELSE 'Budget' END AS tier FROM bench",
276 "Complex expressions with CASE",
277 "mixed",
278 ),
279 BenchmarkQuery::new(
280 "multiple_aggregates",
281 QueryCategory::ComplexQueries,
282 "SELECT category, COUNT(*) as cnt, SUM(price) as total_price, AVG(quantity) as avg_qty, MIN(price) as min_price, MAX(price) as max_price FROM bench GROUP BY category ORDER BY total_price DESC",
283 "Multiple aggregates with sorting",
284 "mixed",
285 ),
286 BenchmarkQuery::new(
287 "complex_filter",
288 QueryCategory::ComplexQueries,
289 "SELECT * FROM bench WHERE (category = 'Electronics' AND price > 500) OR (category = 'Books' AND quantity > 10) OR (status = 'Active' AND price BETWEEN 100 AND 300)",
290 "Complex OR/AND conditions",
291 "mixed",
292 ),
293 ]
294 }
295
296 pub fn get_all_queries() -> Vec<BenchmarkQuery> {
297 let mut queries = Vec::new();
298 queries.extend(Self::get_basic_queries());
299 queries.extend(Self::get_aggregation_queries());
300 queries.extend(Self::get_sorting_queries());
301 queries.extend(Self::get_window_queries());
302 queries.extend(Self::get_complex_queries());
303 queries
304 }
305
306 pub fn get_queries_for_table_type(table_type: &str) -> Vec<BenchmarkQuery> {
307 Self::get_all_queries()
308 .into_iter()
309 .filter(|q| q.table_type == "all" || q.table_type == table_type)
310 .collect()
311 }
312
313 pub fn get_progressive_queries() -> HashMap<usize, Vec<String>> {
314 let mut progressive = HashMap::new();
315
316 progressive.insert(
317 100,
318 vec![
319 "SELECT * FROM bench".to_string(),
320 "SELECT * FROM bench WHERE id > 50".to_string(),
321 "SELECT COUNT(*) FROM bench".to_string(),
322 ],
323 );
324
325 progressive.insert(
326 1000,
327 vec![
328 "SELECT * FROM bench".to_string(),
329 "SELECT * FROM bench WHERE id > 500".to_string(),
330 "SELECT COUNT(*), SUM(price) FROM bench".to_string(),
331 "SELECT category, COUNT(*) FROM bench GROUP BY category".to_string(),
332 ],
333 );
334
335 progressive.insert(
336 10000,
337 vec![
338 "SELECT * FROM bench LIMIT 100".to_string(),
339 "SELECT * FROM bench WHERE id > 5000".to_string(),
340 "SELECT category, COUNT(*), AVG(price) FROM bench GROUP BY category".to_string(),
341 "SELECT * FROM bench ORDER BY price DESC LIMIT 100".to_string(),
342 "SELECT *, ROW_NUMBER() OVER (ORDER BY price) AS rn FROM bench LIMIT 100"
343 .to_string(),
344 ],
345 );
346
347 for size in &[
348 20000, 30000, 40000, 50000, 60000, 70000, 80000, 90000, 100000,
349 ] {
350 progressive.insert(
351 *size,
352 vec![
353 format!("SELECT * FROM bench WHERE id > {}", size / 2),
354 format!("SELECT COUNT(*) FROM bench WHERE id <= {}", size / 2),
355 "SELECT category, COUNT(*), SUM(price * quantity) FROM bench GROUP BY category".to_string(),
356 "SELECT * FROM bench ORDER BY price DESC LIMIT 1000".to_string(),
357 "SELECT *, RANK() OVER (PARTITION BY category ORDER BY price DESC) FROM bench LIMIT 1000".to_string(),
358 ],
359 );
360 }
361
362 progressive
363 }
364}