Note
This variable is available starting with V4.2.4.
Description
cardinality_estimation_model specifies the correlation assumption used by the optimizer for cardinality estimation.
Notice
Before you introduce this option to a production environment, make sure to thoroughly test it.
Privilege requirements
Query variables
systenants and all user tenants can execute theSHOW VARIABLESstatement or query theSYS.TENANT_VIRTUAL_GLOBAL_VARIABLEview (Oracle mode) or theinformation_schema.GLOBAL_VARIABLESview (MySQL mode) to obtain the values of global system variables.Modify variables
systenants can directly modify the values of global system variables.MySQL user tenants must have the
SUPERorALTER SYSTEMprivilege to modify the values of global system variables.Oracle user tenants must have the
ALTER SYSTEMprivilege to modify the values of global system variables.
Attributes
| Attribute | Description |
|---|---|
| Parameter type | Enum |
| Default value | PARTIAL |
| Value range |
NoteIn most scenarios, the cardinality estimation results for the three correlation assumptions increase in the following order: INDEPENDENT < PARTIAL < FULL. |
| Scope |
|
| Modifiable | Yes. You can execute the SET statement to modify the value. |
Examples
In the three correlation assumptions, the cardinality estimation results for the t1 table after filtering by the base table predicate are 10, 31, and 98, respectively.
Create the
t1table.obclient [test]> create table t1(c1 int, c2 int); Query OK, 0 rows affected (0.103 sec)Insert data into the
t1table.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 the
t1table.obclient [test]> analyze table t1; Query OK, 0 rows affected (0.073 sec)Set the correlation assumption to
INDEPENDENTand execute 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 execute cardinality estimation.obclient [test]> set cardinality_estimation_model = 'partial'; 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 |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 execute cardinality estimation.obclient [test]> set cardinality_estimation_model = 'full'; Query OK, 0 rows affected (0.000 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 |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)