`select` very slow on an empty table from `delete from xx`
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
- create cluster with `tiup playground`
- do the following, we can see query from the empty table takes about `60ms`. also test another table with `524288` rows, then empty it with `delete from`, it takes `440ms` to query it.
```sql
mysql> create table t(a int);
Query OK, 0 rows affected (0.10 sec)
.... insert data
mysql> delete from t;
Query OK, 65536 rows affected (0.38 sec)
mysql> select count(1) from t;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.06 sec)
mysql> show table t regions;
+-----------+-----------+--------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS | SCHEDULING_CONSTRAINTS | SCHEDULING_STATE |
+-----------+-----------+--------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
| 14 | t_107_ | t_281474976710649_ | 15 | 1 | 15 | 0 | 23118061 | 16450774 | 24 | 133569 | | |
+-----------+-----------+--------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2024-04-26 11:34:15 |
+---------------------+
1 row in set (0.00 sec)
```
- at first, i thought it related to MVCC, so wait GC and query again, still very slow
```sql
...... wait gc finished on table t
mysql> select count(1) from t;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.07 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2024-04-26 14:18:50 |
+---------------------+
1 row in set (0.00 sec)
```
- for a new created empty table, query time < 10ms
```sql
mysql> create table tt(a int);
Query OK, 0 rows affected (0.11 sec)
mysql> select count(1) from tt;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
```
### 2. What did you expect to see? (Required)
very fast to query
### 3. What did you see instead (Required)
slow compared to a new created empty table
### 4. What is your TiDB version? (Required)
master
Contributor guide
Assessment
This issue has not been assessed yet.