pingcap/tidb

display row width in results of explain statements

Open

#20,676 opened on Oct 27, 2020

View on GitHub
 (6 comments) (0 reactions) (0 assignees)Go (40,090 stars) (6,186 forks)batch import
feature/discussinggood first issuehelp wantedsig/plannertype/feature-request

Description

Feature Request

Is your feature request related to a problem? Please describe:

Since one part of our cost model is estRows * rowWidth * scanFactor, the parameter rowWidth has an impact on the cost, but now TiDB only displays estRows when processing explain, which may confuse users in some cases, for example:

create table t (a int, b int, c int, d varchar(128), primary key(a), index(b, c));
insert into t values (1, 1, 1, space(128));
insert into t values (2, 2, 2, space(128));
insert into t values (3, 3, 3, space(128));
insert into t values (4, 4, 4, space(128));
analyze table t;

mysql> explain select count(*) from t where a<=1 and b>=2;
+-----------------------------+---------+-----------+------------------------+----------------------------------+
| id                          | estRows | task      | access object          | operator info                    |
+-----------------------------+---------+-----------+------------------------+----------------------------------+
| StreamAgg_10                | 1.00    | root      |                        | funcs:count(1)->Column#5         |
| └─IndexReader_22            | 0.75    | root      |                        | index:Selection_21               |
|   └─Selection_21            | 0.75    | cop[tikv] |                        | le(test.t.a, 1)                  |
|     └─IndexRangeScan_20     | 3.00    | cop[tikv] | table:t, index:b(b, c) | range:[2,+inf], keep order:false |
+-----------------------------+---------+-----------+------------------------+----------------------------------+

mysql> explain select count(*) from t use index(primary) where a<=1 and b>=2;
+-----------------------------+---------+-----------+---------------+----------------------------------+
| id                          | estRows | task      | access object | operator info                    |
+-----------------------------+---------+-----------+---------------+----------------------------------+
| StreamAgg_10                | 1.00    | root      |               | funcs:count(1)->Column#5         |
| └─TableReader_17            | 0.75    | root      |               | data:Selection_16                |
|   └─Selection_16            | 0.75    | cop[tikv] |               | ge(test.t.b, 2)                  |
|     └─TableRangeScan_15     | 1.00    | cop[tikv] | table:t       | range:[-inf,1], keep order:false |
+-----------------------------+---------+-----------+---------------+----------------------------------+

As shown above, PK has a better selectivity with estRows=1 than IDX with estRows=3, but the optimizer chooses IDX. This may confuse our users if they don't know the impact of rowWidth on cost, and also make it harder for optimizer maintainers to investigate index selection problems.

Describe the feature you'd like:

Like PG, display rowWidth in results of explain:

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

We can display this field in operator info:

mysql> explain select count(*) from t use index(primary) where a<=1 and b>=2;
+-----------------------------+---------+-----------+---------------+----------------------------------+
| id                          | estRows | task      | access object | operator info                    |
+-----------------------------+---------+-----------+---------------+----------------------------------+
| StreamAgg_10                | 1.00    | root      |               | funcs:count(1)->Column#5         |
| └─TableReader_17            | 0.75    | root      |               | data:Selection_16                |
|   └─Selection_16            | 0.75    | cop[tikv] |               | ge(test.t.b, 2)                  |
|     └─TableRangeScan_15     | 1.00    | cop[tikv] | table:t       | range:[-inf,1], keep order:false, row width:2333 |
+-----------------------------+---------+-----------+---------------+----------------------------------+

Contributor guide