pingcap / pingcap/tidb

Avoid unnecessary trigger loading statistics

Open
#46,374 0 comments 0 reactions 1 assignee Claimed by @time-and-fate View on GitHub
component/statistics sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

In the case below, the triggered stats loadings are unnecessary.
Also, the `stats:partial[ia:allEvicted, ib:allEvicted, iab:allEvicted]` in the execution plan is also unnecessary.
Because this query doesn't need the stats.

```sql
create table t(a int, b int, index ia(a), index ib(b), index iab(a,b));
insert into t value(1,1),(2,2);
analyze table t;
show stats_histograms;
explain select * from t;
-- wait for 3 seconds
show stats_histograms;
```
```
> show stats_histograms;
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+-------------+-----------------+----------------+----------------+---------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time | Distinct_count | Null_count | Avg_col_size | Correlation | Load_status | Total_mem_usage | Hist_mem_usage | Topn_mem_usage | Cms_mem_usage |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+-------------+-----------------+----------------+----------------+---------------+
| test | t | | a | 0 | 2023-08-24 06:50:38 | 2 | 0 | 8 | 1 | allEvicted | 0 | 0 | 0 | 0 |
| test | t | | b | 0 | 2023-08-24 06:50:38 | 2 | 0 | 8 | 1 | allEvicted | 0 | 0 | 0 | 0 |
| test | t | | ia | 1 | 2023-08-24 06:50:38 | 2 | 0 | 0 | 0 | allEvicted | 0 | 0 | 0 | 0 |
| test | t | | ib | 1 | 2023-08-24 06:50:38 | 2 | 0 | 0 | 0 | allEvicted | 0 | 0 | 0 | 0 |
| test | t | | iab | 1 | 2023-08-24 06:50:38 | 2 | 0 | 0 | 0 | allEvicted | 0 | 0 | 0 | 0 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+-------------+-----------------+----------------+----------------+---------------+

> explain select * from t;
+-----------------------+---------+-----------+--------------------------+-------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------+---------+-----------+--------------------------+-------------------------------------------------------------------------------+
| IndexReader_7 | 2.00 | root | | index:IndexFullScan_6 |
| └─IndexFullScan_6 | 2.00 | cop[tikv] | table:t, index:iab(a, b) | keep order:false, stats:partial[ia:allEvicted, ib:allEvicted, iab:allEvicted] |
+-----------------------+---------+-----------+--------------------------+-------------------------------------------------------------------------------+

> show stats_histograms;
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+-------------+-----------------+----------------+----------------+---------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time | Distinct_count | Null_count | Avg_col_size | Correlation | Load_status | Total_mem_usage | Hist_mem_usage | Topn_mem_usage | Cms_mem_usage |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+-------------+-----------------+----------------+----------------+---------------+
| test | t | | a | 0 | 2023-08-24 06:50:38 | 2 | 0 | 8 | 1 | allEvicted | 0 | 0 | 0 | 0 |
| test | t | | b | 0 | 2023-08-24 06:50:38 | 2 | 0 | 8 | 1 | allEvicted | 0 | 0 | 0 | 0 |
| test | t | | ia | 1 | 2023-08-24 06:50:38 | 2 | 0 | 0 | 0 | allLoaded | 114 | 0 | 114 | 0 |
| test | t | | ib | 1 | 2023-08-24 06:50:38 | 2 | 0 | 0 | 0 | allLoaded | 114 | 0 | 114 | 0 |
| test | t | | iab | 1 | 2023-08-24 06:50:38 | 2 | 0 | 0 | 0 | allLoaded | 132 | 0 | 132 | 0 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+-------------+-----------------+----------------+----------------+---------------+
```

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.