Overview
partition_index_dive_limit specifies the maximum number of partitions that the optimizer uses for row estimation at the storage layer.
Privilege requirements
Query variables
Global level
The
systenant and all user tenants can use theSHOW VARIABLESstatement or theinformation_schema.GLOBAL_VARIABLESview to view the values of global system variables.Session level
Both the
systenant and all user tenants can use theSHOW VARIABLESstatement or theinformation_schema.SESSION_VARIABLESview to view the values of session system variables.
Modify variables
Global effective
The
systenant can directly modify the values of global system variables.The user tenant must have the
SUPERorALTER SYSTEMprivilege to modify the value of a global system variable.
Session takes effect
The
systenant and all user tenants can directly modify the values of session system variables for their own tenants.
Attributes
Attribute |
Description |
|---|---|
| Parameter type | Int |
| Default Value | 10 |
| Value range | [0, INT64_MAX]
Note
|
| Effective Scope |
|
| Modifiable | Yes, you can use theSETStatement modification. |
Usage instructions
Modify this variable to control the maximum number of partitions allowed during row estimation for storage layer optimization:
When the number of partitions is less than this variable, all partitions are always selected for storage-layer row estimation.
When the number of partitions is greater than or equal to this variable, randomly select
partition_index_dive_limitpartitions for row estimation at the storage layer and scale the estimation results proportionally.When the variable is
0, all partitions are always selected for storage-layer row estimation.When both
range_index_dive_limitandpartition_index_dive_limitare negative, the storage-layer row estimation strategy reverts to the old version's behavior. It performs storage-layer row estimation only within the first 10 ranges of a single partition and does not sample beyond that.To disable row cardinality estimation at the storage layer, you can use the system variable
enable_storage_cardinality_estimation.
The differences from MySQL are as follows:
- MySQL controls the number of equal-range indexes only through the system variable
eq_range_index_dive_limitand does not limit the number of partitions. OceanBase AI Database uses this variable to limit the number of partitions for row estimation at the storage layer.
Configuration example
Create partitioned table
t1and insert data into it.obclient [test]> CREATE TABLE t1(c1 int, c2 int, index i1(c2)) PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN(10), PARTITION p1 VALUES LESS THAN(20), PARTITION p2 VALUES LESS THAN(30)); Query OK, 0 rows affected (0.457 sec) obclient [test]> INSERT INTO t1 SELECT 1,1 FROM table(generator(10)); Query OK, 10 rows affected (0.130 sec) Records: 10 Duplicates: 0 Warnings: 0 obclient [test]> INSERT INTO t1 SELECT 11,11 FROM table(generator(20)); Query OK, 20 rows affected (0.008 sec) Records: 20 Duplicates: 0 Warnings: 0 obclient [test]> INSERT INTO t1 SELECT 21,21 FROM table(generator(30)); Query OK, 30 rows affected (0.009 sec) Records: 30 Duplicates: 0 Warnings: 0View the execution plan. By default, storage-layer row estimation is used for all partitions that are not the first 10.
obclient [test]> EXPLAIN SELECT c1 FROM t1 WHERE c2 IN (1,11,21);The query result is as follows:
+---------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------+ | ============================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | --------------------------------------------- | | |0 |PX COORDINATOR | |60 |253 | | | |1 |└─EXCHANGE OUT DISTR |:EX10000|60 |234 | | | |2 | └─PX PARTITION ITERATOR| |60 |193 | | | |3 | └─TABLE RANGE SCAN |t1(i1) |60 |193 | | | ============================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([INTERNAL_FUNCTION(t1.c1)]), filter(nil), rowset=256 | | 1 - output([INTERNAL_FUNCTION(t1.c1)]), filter(nil), rowset=256 | | dop=1 | | 2 - output([t1.c1]), filter(nil), rowset=256 | | force partition granule | | 3 - output([t1.c1]), filter(nil), rowset=256 | | access([t1.__pk_increment], [t1.c1]), partitions(p[0-2]) | | is_index_back=true, is_global_index=false, | | range_key([t1.c2], [t1.__pk_increment]), range(1,MIN ; 1,MAX), (11,MIN ; 11,MAX), (21,MIN ; 21,MAX), | | range_cond([t1.c2 IN (1, 11, 21)]) | +---------------------------------------------------------------------------------------------------------------+ 20 rows in set (0.063 sec)Modify the variables to select only one partition for row estimation at the storage layer.
obclient [test]> SET partition_index_dive_limit = 1; Query OK, 0 rows affected (0.002 sec)Check the execution plan again and find that only one partition is selected for row estimation at the storage layer, with the estimated row count being 10 * 3 = 30.
obclient [test]> EXPLAIN SELECT c1 FROM t1 WHERE c2 IN (1,11,21);The query result is as follows:
+---------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------+ | ============================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | --------------------------------------------- | | |0 |PX COORDINATOR | |30 |253 | | | |1 |└─EXCHANGE OUT DISTR |:EX10000|30 |234 | | | |2 | └─PX PARTITION ITERATOR| |30 |193 | | | |3 | └─TABLE RANGE SCAN |t1(i1) |30 |193 | | | ============================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([INTERNAL_FUNCTION(t1.c1)]), filter(nil), rowset=256 | | 1 - output([INTERNAL_FUNCTION(t1.c1)]), filter(nil), rowset=256 | | dop=1 | | 2 - output([t1.c1]), filter(nil), rowset=256 | | force partition granule | | 3 - output([t1.c1]), filter(nil), rowset=256 | | access([t1.__pk_increment], [t1.c1]), partitions(p[0-2]) | | is_index_back=true, is_global_index=false, | | range_key([t1.c2], [t1.__pk_increment]), range(1,MIN ; 1,MAX), (11,MIN ; 11,MAX), (21,MIN ; 21,MAX), | | range_cond([t1.c2 IN (1, 11, 21)]) | +---------------------------------------------------------------------------------------------------------------+ 20 rows in set (0.063 sec)