我的书签
添加书签
移除书签Rollup and query
As a polymer view in Doris, Rollup can play two roles in queries:
- Index
- Aggregate data (only for aggregate models, aggregate key)
However, in order to hit Rollup, certain conditions need to be met, and the value of PreAggregation of ScanNdo node in the execution plan can be used to determine whether Rollup can be hit or not, and the Rollup field can be used to determine which Rollup table is hit.
Noun Interpretation
Base: Base table.
Rollup: Generally, it refers to the Rollup tables created based on Base tables, but in some scenarios, it includes Base and Rollup tables.
Index
Doris’s prefix index has been introduced in the previous query practice, that is, Doris will generate the first 36 bytes in the Base/Rollup table separately in the underlying storage engine (with varchar type, the prefix index may be less than 36 bytes, varchar will truncate the prefix index, and use up to 20 bytes of varchar). A sorted sparse index data (data is also sorted, positioned by index, and then searched by dichotomy in the data), and then matched each Base/Rollup prefix index according to the conditions in the query, and selected a Base/Rollup that matched the longest prefix index.
---> matching from left to right+----+----+----+----+----+----+| c1 | c2 | c3 | c4 | c5 |... |
As shown in the figure above, the conditions of where and on in the query are pushed up and down to ScanNode and matched from the first column of the prefix index. Check if there are any of these columns in the condition, and then accumulate the matching length until the matching cannot match or the end of 36 bytes (columns of varchar type can only match 20 bytes and match less than 36 words). Section truncates prefix index, and then chooses a Base/Rollup with the longest matching length. The following example shows how to create a Base table and four rollups:
+---------------+-------+--------------+------+-------+---------+-------+| IndexName | Field | Type | Null | Key | Default | Extra |+---------------+-------+--------------+------+-------+---------+-------+| test | k1 | TINYINT | Yes | true | N/A | || | k2 | SMALLINT | Yes | true | N/A | || | k3 | INT | Yes | true | N/A | || | k4 | BIGINT | Yes | true | N/A | || | k5 | DECIMAL(9,3) | Yes | true | N/A | || | k6 | CHAR(5) | Yes | true | N/A | || | k7 | DATE | Yes | true | N/A | || | k8 | DATETIME | Yes | true | N/A | || | k9 | VARCHAR(20) | Yes | true | N/A | || | k10 | DOUBLE | Yes | false | N/A | MAX || | k11 | FLOAT | Yes | false | N/A | SUM || | | | | | | || rollup_index1 | k9 | VARCHAR(20) | Yes | true | N/A | || | k1 | TINYINT | Yes | true | N/A | || | k2 | SMALLINT | Yes | true | N/A | || | k3 | INT | Yes | true | N/A | || | k4 | BIGINT | Yes | true | N/A | || | k5 | DECIMAL(9,3) | Yes | true | N/A | || | k6 | CHAR(5) | Yes | true | N/A | || | k7 | DATE | Yes | true | N/A | || | k8 | DATETIME | Yes | true | N/A | || | k10 | DOUBLE | Yes | false | N/A | MAX || | k11 | FLOAT | Yes | false | N/A | SUM || | | | | | | || rollup_index2 | k9 | VARCHAR(20) | Yes | true | N/A | || | k2 | SMALLINT | Yes | true | N/A | || | k1 | TINYINT | Yes | true | N/A | || | k3 | INT | Yes | true | N/A | || | k4 | BIGINT | Yes | true | N/A | || | k5 | DECIMAL(9,3) | Yes | true | N/A | || | k6 | CHAR(5) | Yes | true | N/A | || | k7 | DATE | Yes | true | N/A | || | k8 | DATETIME | Yes | true | N/A | || | k10 | DOUBLE | Yes | false | N/A | MAX || | k11 | FLOAT | Yes | false | N/A | SUM || | | | | | | || rollup_index3 | k4 | BIGINT | Yes | true | N/A | || | k5 | DECIMAL(9,3) | Yes | true | N/A | || | k6 | CHAR(5) | Yes | true | N/A | || | k1 | TINYINT | Yes | true | N/A | || | k2 | SMALLINT | Yes | true | N/A | || | k3 | INT | Yes | true | N/A | || | k7 | DATE | Yes | true | N/A | || | k8 | DATETIME | Yes | true | N/A | || | k9 | VARCHAR(20) | Yes | true | N/A | || | k10 | DOUBLE | Yes | false | N/A | MAX || | k11 | FLOAT | Yes | false | N/A | SUM || | | | | | | || rollup_index4 | k4 | BIGINT | Yes | true | N/A | || | k6 | CHAR(5) | Yes | true | N/A | || | k5 | DECIMAL(9,3) | Yes | true | N/A | || | k1 | TINYINT | Yes | true | N/A | || | k2 | SMALLINT | Yes | true | N/A | || | k3 | INT | Yes | true | N/A | || | k7 | DATE | Yes | true | N/A | || | k8 | DATETIME | Yes | true | N/A | || | k9 | VARCHAR(20) | Yes | true | N/A | || | k10 | DOUBLE | Yes | false | N/A | MAX || | k11 | FLOAT | Yes | false | N/A | SUM |+---------------+-------+--------------+------+-------+---------+-------+
The prefix indexes of the three tables are
Base(k1 ,k2, k3, k4, k5, k6, k7)rollup_index1(k9)，rollup_index2(k9)rollup_index3(k4, k5, k6, k1, k2, k3, k7)rollup_index4(k4, k6, k5, k1, k2, k3, k7)
Conditions on columns that can be indexed with the prefix need to be = < > <= >= in between, and these conditions are side-by-side and the relationship uses and connections’, which cannot be hit for or、!= and so on. Then look at the following query:
SELECT * FROM test WHERE k1 = 1 AND k2 > 3;
With the conditions on K1 and k2, check that only the first column of Base contains K1 in the condition, so match the longest prefix index, test, explain:
| 0:OlapScanNode| TABLE: test| PREAGGREGATION: OFF. Reason: No AggregateInfo| PREDICATES: `k1` = 1, `k2` > 3| partitions=1/1| rollup: test| buckets=1/10| cardinality=-1| avgRowSize=0.0| numNodes=0| tuple ids: 0
Look again at the following queries:
SELECT * FROM test WHERE k4 =1 AND k5 > 3;
With K4 and K5 conditions, check that the first column of rollup_index3 and rollup_index4 contains k4, but the second column of rollup_index3 contains k5, so the matching prefix index is the longest.
| 0:OlapScanNode| TABLE: test| PREAGGREGATION: OFF. Reason: No AggregateInfo| PREDICATES: `k4` = 1, `k5` > 3| partitions=1/1| rollup: rollup_index3| buckets=10/10| cardinality=-1| avgRowSize=0.0| numNodes=0| tuple ids: 0
Now we try to match the conditions on the column containing varchar, as follows:
SELECT * FROM test WHERE k9 IN ("xxx", "yyyy") AND k1 = 10;
There are K9 and K1 conditions. The first column of rollup_index1 and rollup_index2 contains k9. It is reasonable to choose either rollup here to hit the prefix index and randomly select the same one (because there are just 20 bytes in varchar, and the prefix index is truncated in less than 36 bytes). The current strategy here will continue to match k1, because the second rollup_index1 is listed as k1, so rollup_index1 is chosen, in fact, the latter K1 condition will not play an accelerating role. (If the condition outside the prefix index needs to accelerate the query, it can be accelerated by establishing a Bloom Filter filter. Typically for string types, because Doris has a Block level for columns, a Min/Max index for shaping and dates.) The following is the result of explain.
| 0:OlapScanNode| TABLE: test| PREAGGREGATION: OFF. Reason: No AggregateInfo| PREDICATES: `k9` IN ('xxx', 'yyyy'), `k1` = 10| partitions=1/1| rollup: rollup_index1| buckets=1/10| cardinality=-1| avgRowSize=0.0| numNodes=0| tuple ids: 0
Finally, look at a query that can be hit by more than one Rollup:
Select * from test where K4 < 1000 and K5 = 80 and K6 = 10000;
There are three conditions: k4, K5 and k6. The first three columns of rollup_index3 and rollup_index4 contain these three columns respectively. So the prefix index length matched by them is the same. Both can be selected. The current default strategy is to select a rollup created earlier. Here is rollup_index3.
| 0:OlapScanNode| TABLE: test| PREAGGREGATION: OFF. Reason: No AggregateInfo| PREDICATES: `k4` < 1000, `k5` = 80, `k6` >= 10000.0| partitions=1/1| rollup: rollup_index3| buckets=10/10| cardinality=-1| avgRowSize=0.0| numNodes=0| tuple ids: 0
If you modify the above query slightly as follows:
SELECT * FROM test WHERE k4 < 1000 AND k5 = 80 OR k6 >= 10000;
The query here cannot hit the prefix index. (Even any Min/Max in the Doris storage engine, the BloomFilter index doesn’t work.)
Aggregate data
Of course, the function of aggregated data is indispensable for general polymer views. Such materialized views are very helpful for aggregated queries or report queries. To hit the polymer views, the following prerequisites are needed:
- There is a separate Rollup for all columns involved in a query or subquery.
- If there is Join in a query or sub-query, the type of Join needs to be Inner join.
The following are some types of aggregated queries that can hit Rollup.
| Column type Query type | Sum | Distinct/Count Distinct | Min | Max | APPROX_COUNT_DISTINCT |
|---|---|---|---|---|---|
| Key | false | true | true | true | true |
| Value(Sum) | true | false | false | false | false |
| Value(Replace) | false | false | false | false | false |
| Value(Min) | false | false | true | false | false |
| Value(Max) | false | false | false | true | false |
If the above conditions are met, there will be two stages in judging the hit of Rollup for the aggregation model:
- Firstly, the Rollup table with the longest index hit by prefix index is matched by conditions. See the index strategy above.
- Then compare the rows of Rollup and select the smallest Rollup.
The following Base table and Rollup:
+-------------+-------+--------------+------+-------+---------+-------+| IndexName | Field | Type | Null | Key | Default | Extra |+-------------+-------+--------------+------+-------+---------+-------+| test_rollup | k1 | TINYINT | Yes | true | N/A | || | k2 | SMALLINT | Yes | true | N/A | || | k3 | INT | Yes | true | N/A | || | k4 | BIGINT | Yes | true | N/A | || | k5 | DECIMAL(9,3) | Yes | true | N/A | || | k6 | CHAR(5) | Yes | true | N/A | || | k7 | DATE | Yes | true | N/A | || | k8 | DATETIME | Yes | true | N/A | || | k9 | VARCHAR(20) | Yes | true | N/A | || | k10 | DOUBLE | Yes | false | N/A | MAX || | k11 | FLOAT | Yes | false | N/A | SUM || | | | | | | || rollup2 | k1 | TINYINT | Yes | true | N/A | || | k2 | SMALLINT | Yes | true | N/A | || | k3 | INT | Yes | true | N/A | || | k10 | DOUBLE | Yes | false | N/A | MAX || | k11 | FLOAT | Yes | false | N/A | SUM || | | | | | | || rollup1 | k1 | TINYINT | Yes | true | N/A | || | k2 | SMALLINT | Yes | true | N/A | || | k3 | INT | Yes | true | N/A | || | k4 | BIGINT | Yes | true | N/A | || | k5 | DECIMAL(9,3) | Yes | true | N/A | || | k10 | DOUBLE | Yes | false | N/A | MAX || | k11 | FLOAT | Yes | false | N/A | SUM |+-------------+-------+--------------+------+-------+---------+-------+
See the following queries:
SELECT SUM(k11) FROM test_rollup WHERE k1 = 10 AND k2 > 200 AND k3 in (1,2,3);
Firstly, it judges whether the query can hit the aggregated Rolup table. After checking the graph above, it is possible. Then the condition contains three conditions: k1, K2 and k3. The first three columns of test_rollup, rollup1 and rollup2 contain all the three conditions. So the prefix index length is the same. Then, it is obvious that the aggregation degree of rollup2 is the highest when comparing the number of rows. Row 2 is selected because of the minimum number of rows.
| 0:OlapScanNode || TABLE: test_rollup || PREAGGREGATION: ON || PREDICATES: `k1` = 10, `k2` > 200, `k3` IN (1, 2, 3) || partitions=1/1 || rollup: rollup2 || buckets=1/10 || cardinality=-1 || avgRowSize=0.0 || numNodes=0 || tuple ids: 0 |
