Skip to content

Stats of multi-column mv index isn't fully utilized for estimation #56915

@time-and-fate

Description

@time-and-fate

Enhancement

create table t(a int, b int, j json, index ia(a), index mvi( (cast(j as signed array)), a, b) );
insert into t value(1,1,'[1,2,3,4,5]');
insert into t value(1,1,'[1,2,3,4,5]');
insert into t value(1,1,'[1,2,3,4,5]');
insert into t value(1,1,'[1,2,3,4,5]');
insert into t value(1,1,'[6]');
analyze table t all columns;
explain select * from t where a = 1 and 6 member of (j);

The actual row count is 1.

Expected

> explain select * from t where a = 1 and 6 member of (j);
+--------------------------------+---------+-----------+-----------------------------------------------------+-------------------------------------------------------------------+
| id                             | estRows | task      | access object                                       | operator info                                                     |
+--------------------------------+---------+-----------+-----------------------------------------------------+-------------------------------------------------------------------+
| IndexMerge_14                  | 1.00    | root      |                                                     | type: union                                                       |
| ├─IndexRangeScan_12(Build)     | 1.00    | cop[tikv] | table:t, index:mvi(cast(`j` as signed array), a, b) | range:[6 1,6 1], keep order:false, stats:partial[j:unInitialized] |
| └─TableRowIDScan_13(Probe)     | 1.00    | cop[tikv] | table:t                                             | keep order:false, stats:partial[j:unInitialized]                  |
+--------------------------------+---------+-----------+-----------------------------------------------------+-------------------------------------------------------------------+

Actual

> explain select * from t where a = 1 and 6 member of (j);
+-------------------------+---------+-----------+---------------+----------------------------------------------------------------+
| id                      | estRows | task      | access object | operator info                                                  |
+-------------------------+---------+-----------+---------------+----------------------------------------------------------------+
| TableReader_7           | 5.00    | root      |               | data:Selection_6                                               |
| └─Selection_6           | 5.00    | cop[tikv] |               | eq(test.t.a, 1), json_memberof(cast(6, json BINARY), test.t.j) |
|   └─TableFullScan_5     | 5.00    | cop[tikv] | table:t       | keep order:false, stats:partial[j:unInitialized]               |
+-------------------------+---------+-----------+---------------+----------------------------------------------------------------+
3 rows in set (0.031 sec)

> explain select * from t use index (mvi) where a = 1 and 6 member of (j);
+-------------------------------+---------+-----------+-----------------------------------------------------+-------------------------------------------------------------------+
| id                            | estRows | task      | access object                                       | operator info                                                     |
+-------------------------------+---------+-----------+-----------------------------------------------------+-------------------------------------------------------------------+
| IndexMerge_7                  | 5.00    | root      |                                                     | type: union                                                       |
| ├─IndexRangeScan_5(Build)     | 21.00   | cop[tikv] | table:t, index:mvi(cast(`j` as signed array), a, b) | range:[6 1,6 1], keep order:false, stats:partial[j:unInitialized] |
| └─TableRowIDScan_6(Probe)     | 5.00    | cop[tikv] | table:t                                             | keep order:false, stats:partial[j:unInitialized]                  |
+-------------------------------+---------+-----------+-----------------------------------------------------+-------------------------------------------------------------------+

Please see the PR for the specific reason.

Metadata

Metadata

Assignees

Labels

sig/plannerSIG: Plannertype/enhancementThe issue or PR belongs to an enhancement.

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions