pingcap / pingcap/tidb

planner: consider missing Index stats in `stats-healthy` (how to indicate missing index-stats?)

Open
#61,632 2 comments 0 reactions 1 assignee Claimed by @AilinKid View on GitHub
affects-8.5 component/statistics sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
Our `stats-healthy` indicator only consider the number of changed rows since the last `Analyze`.
In some cases, users might add new indexes without collecting their stats. In this scenario, the `index-stats` is missing, but our `stats-healthy` is still high, which might mislead our users.
And some of our users rely on `stats-healthy` to decide whether to collect stats manually, if `stats-healthy` cannot indicate the missing index-stats, these users might be at risk.

```
mysql> create table t (a int, b int);
Query OK, 0 rows affected (0.013 sec)

mysql> insert into t values (1, 1), (2, 2), (3, 3);
Query OK, 3 rows affected (0.001 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> analyze table t all columns;
Query OK, 0 rows affected, 1 warning (0.007 sec)

mysql> show stats_healthy;
+---------+------------+----------------+---------+
| Db_name | Table_name | Partition_name | Healthy |
+---------+------------+----------------+---------+
| test | t | | 100 |
+---------+------------+----------------+---------+
1 row in set (0.006 sec)

mysql> alter table t add index ab(a, b);
Query OK, 0 rows affected (0.024 sec)

mysql> show stats_healthy;
+---------+------------+----------------+---------+
| Db_name | Table_name | Partition_name | Healthy |
+---------+------------+----------------+---------+
| test | t | | 100 |
+---------+------------+----------------+---------+
1 row in set (0.006 sec)

mysql> explain select a from t where a=1 and b=1;
+--------------------------+---------+-----------+-------------------------+--------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------+---------+-----------+-------------------------+--------------------------------------------------------------+
| IndexReader_9 | 1.00 | root | | index:Projection_5 |
| └─Projection_5 | 1.00 | cop[tikv] | | test.t.a |
| └─IndexRangeScan_8 | 1.25 | cop[tikv] | table:t, index:ab(a, b) | range:[1 1,1 1], keep order:false, stats:partial[ab:missing] |
+--------------------------+---------+-----------+-------------------------+--------------------------------------------------------------+
```

In the above case, you can see after adding a new index, the `stats-healthy` is still `100`, but when executing the query, we got `stats:ab:missing`.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.