Overview
The cardinality_estimation_model parameter specifies the correlation assumption used by the optimizer for cardinality estimation.
Notice
Before introducing this option into the production environment, ensure it has been thoroughly tested.
Privilege requirements
Query variables
Global level
The
systenant and all user tenants can use theSHOW VARIABLESstatement and 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 and 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 expiration
The
systenant and all user tenants can directly modify the values of session system variables for their own tenants.
Attributes
Attribute |
Description |
|---|---|
| Parameter type | Enum |
| Default Value | PARTIAL |
| Value range |
NoteIn most scenarios, the estimated row sizes for the three correlations increase in order: INDEPENDENT < PARTIAL < FULL. |
| Effective Scope |
|
| Modifiable | Yes, you can use theSETStatement modification. |
Configuration example
Under the three correlation assumptions, the estimated row counts for t1 after filtering with the base table predicate are 10, 31, and 98 respectively.
Create table
t1.obclient [test]> create table t1(c1 int, c2 int); Query OK, 0 rows affected (0.103 sec)Insert data into table
t1.obclient [test]> insert into t1 select row_number() over () % 10, row_number() over () % 100 from table(generator(10000)); Query OK, 10000 rows affected (0.077 sec) Records: 10000 Duplicates: 0 Warnings: 0Analyze table
t1.obclient [test]> analyze table t1; Query OK, 0 rows affected (0.073 sec)Set the correlation assumption to
INDEPENDENTand perform cardinality estimation.obclient [test]> set cardinality_estimation_model = 'independent'; Query OK, 0 rows affected (0.001 sec)Query the cardinality estimation result.
obclient [test]> explain select * from t1 where c1 = 1 and c2 = 1;The query result is as follows:
+------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------+ | =============================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ----------------------------------------------- | | |0 |TABLE FULL SCAN|t1 |10 |734 | | | =============================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.c1], [t1.c2]), filter([t1.c2 = 1], [t1.c1 = 1]), rowset=16 | | access([t1.c1], [t1.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------------+ 11 rows in set (0.011 sec)Set the correlation assumption to
PARTIALand perform cardinality estimation.obclient [test]> set cardinality_estimation_model = 'partial'; Query OK, 0 rows affected (0.001 sec)Query the cardinality estimation results.
obclient [test]> explain select * from t1 where c1 = 1 and c2 = 1;The query result is as follows:
+------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------+ | =============================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ----------------------------------------------- | | |0 |TABLE FULL SCAN|t1 |31 |735 | | | =============================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.c1], [t1.c2]), filter([t1.c2 = 1], [t1.c1 = 1]), rowset=256 | | access([t1.c1], [t1.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------------+ 11 rows in set (0.004 sec)Set the correlation assumption to
FULLand perform cardinality estimation.obclient [test]> set cardinality_estimation_model = 'full'; Query OK, 0 rows affected (0.000 sec)Query the cardinality estimation results.
obclient [test]> explain select * from t1 where c1 = 1 and c2 = 1;The query result is as follows:
+------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------+ | =============================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ----------------------------------------------- | | |0 |TABLE FULL SCAN|t1 |98 |738 | | | =============================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.c1], [t1.c2]), filter([t1.c2 = 1], [t1.c1 = 1]), rowset=256 | | access([t1.c1], [t1.c2]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false], | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------------+ 11 rows in set (0.003 sec)