1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
# name: test/optimizer/perfect_ht.test
# description: Test aggregates that can trigger a perfect HT
# group: [optimizer]
statement ok
CREATE TABLE timeseries(year INTEGER, val INTEGER);
statement ok
INSERT INTO timeseries VALUES (1996, 10), (1997, 12), (1996, 20), (2001, 30), (NULL, 1), (1996, NULL);
# this query uses a perfect aggregate HT
query II
EXPLAIN SELECT year, SUM(val), COUNT(val), COUNT(*) FROM timeseries GROUP BY year ORDER BY year;
----
physical_plan <REGEX>:.*PERFECT_HASH_GROUP_BY.*
statement ok
PRAGMA perfect_ht_threshold=0;
# if we set the threshold to 0, the perfect HT is not used anymore
query II
EXPLAIN SELECT year, SUM(val), COUNT(val), COUNT(*) FROM timeseries GROUP BY year ORDER BY year;
----
physical_plan <!REGEX>:.*PERFECT_HASH_GROUP_BY.*
statement ok
PRAGMA perfect_ht_threshold=1;
# if we set it too small, it is not used still
query II
EXPLAIN SELECT year, SUM(val), COUNT(val), COUNT(*) FROM timeseries GROUP BY year ORDER BY year;
----
physical_plan <!REGEX>:.*PERFECT_HASH_GROUP_BY.*
# we can also use it with many columns, as long as the threshold is high enough
statement ok
create table manycolumns as select i a, i b, i c, i d, i e, i f, i g, i h, i, i j from range(0,2) tbl(i);
statement ok
PRAGMA perfect_ht_threshold=30;
query II
explain select a, b, c, d, e, f, g, h, i, j FROM manycolumns GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
----
physical_plan <REGEX>:.*PERFECT_HASH_GROUP_BY.*
# the threshold has to be in range
statement error
PRAGMA perfect_ht_threshold=-1;
----
<REGEX>:.*out of range.*
statement error
PRAGMA perfect_ht_threshold=100;
----
<REGEX>:.*out of range.*
|
