The FTS estimates the number of rows incorrectly when the number of rows to be fts filtered is very large
- 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)
not incorrect
```sql
SELECT COUNT(*)
FROM obj_new o1
JOIN obj_relationship_new r
ON o1.workspace_id=r.workspace_id AND o1.id=r.object_id
WHERE o1.workspace_id='8a6526e6-cd57-4216-bac6-358a6177d221'
AND MATCH(o1.text_value_5) AGAINST('www' IN BOOLEAN MODE);
+--------------------------------------------+-------------+--------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------------+-------------+--------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_62 | 1.00 | root | | funcs:count(Column#515)->Column#512 |
| └─TableReader_64 | 1.00 | root | | MppVersion: 3, data:ExchangeSender_63 |
| └─ExchangeSender_63 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─HashAgg_14 | 1.00 | mpp[tiflash] | | funcs:count(1)->Column#515 |
| └─Projection_61 | 33895180.05 | mpp[tiflash] | | jsm_assets2.obj_new.id, jsm_assets2.obj_relationship_new.workspace_id, jsm_assets2.obj_relationship_new.object_id |
| └─HashJoin_60 | 33895180.05 | mpp[tiflash] | | inner join, equal:[eq(jsm_assets2.obj_new.workspace_id, jsm_assets2.obj_relationship_new.workspace_id) eq(jsm_assets2.obj_new.id, jsm_assets2.obj_relationship_new.object_id)], stream_count: 16 |
| ├─ExchangeReceiver_36(Build) | 3013422.29 | mpp[tiflash] | | stream_count: 16 |
| │ └─ExchangeSender_35 | 3013422.29 | mpp[tiflash] | | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: jsm_assets2.obj_new.workspace_id, collate: utf8mb4_bin], [name: jsm_assets2.obj_new.id, collate: binary], stream_count: 16 |
| │ └─Selection_34 | 3013422.29 | mpp[tiflash] | | eq(jsm_assets2.obj_new.workspace_id, "8a6526e6-cd57-4216-bac6-358a6177d221") |
| │ └─IndexRangeScan_33 | 1000.00 | mpp[tiflash] | table:o1, index:text_value_5(text_value_5) | range:["8a6526e6-cd57-4216-bac6-358a6177d221","8a6526e6-cd57-4216-bac6-358a6177d221"], search func:fts_match_phrase("www", jsm_assets2.obj_new.text_value_5), keep order:false |
| └─ExchangeReceiver_39(Probe) | 33682279.58 | mpp[tiflash] | | |
| └─ExchangeSender_38 | 33682279.58 | mpp[tiflash] | | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: jsm_assets2.obj_relationship_new.workspace_id, collate: utf8mb4_bin], [name: jsm_assets2.obj_relationship_new.object_id, collate: binary] |
| └─TableRangeScan_37 | 33682279.58 | mpp[tiflash] | table:r | range:["8a6526e6-cd57-4216-bac6-358a6177d221","8a6526e6-cd57-4216-bac6-358a6177d221"], keep order:false |
+--------------------------------------------+-------------+--------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> select count(*) from obj_new o1 where MATCH(o1.text_value_5) AGAINST('www' IN BOOLEAN MODE) and o1.workspace_id='8a6526e6-cd57-4216-bac6-358a6177d221';
+----------+
| count(*) |
+----------+
| 1500425 |
+----------+
1 row in set (1.43 sec)
```
incorrectly
```sql
mysql> explain SELECT COUNT(*) FROM obj_new o1 JOIN obj_relationship_new r ON o1.workspace_id=r.workspace_id AND o1.id=r.object_id WHERE o1.workspace_id='8a6526e6-cd57-4216-bac6-358a6177d221' AND MATCH(o1.text_value_7) AGAINST('Fagor' IN BOOLEAN MODE);
+--------------------------------------------+-------------+--------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------------+-------------+--------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_62 | 1.00 | root | | funcs:count(Column#515)->Column#512 |
| └─TableReader_64 | 1.00 | root | | MppVersion: 3, data:ExchangeSender_63 |
| └─ExchangeSender_63 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─HashAgg_14 | 1.00 | mpp[tiflash] | | funcs:count(1)->Column#515 |
| └─Projection_61 | 33895180.05 | mpp[tiflash] | | jsm_assets2.obj_new.id, jsm_assets2.obj_relationship_new.workspace_id, jsm_assets2.obj_relationship_new.object_id |
| └─HashJoin_60 | 33895180.05 | mpp[tiflash] | | inner join, equal:[eq(jsm_assets2.obj_new.workspace_id, jsm_assets2.obj_relationship_new.workspace_id) eq(jsm_assets2.obj_new.id, jsm_assets2.obj_relationship_new.object_id)], stream_count: 16 |
| ├─ExchangeReceiver_36(Build) | 3013422.29 | mpp[tiflash] | | stream_count: 16 |
| │ └─ExchangeSender_35 | 3013422.29 | mpp[tiflash] | | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: jsm_assets2.obj_new.workspace_id, collate: utf8mb4_bin], [name: jsm_assets2.obj_new.id, collate: binary], stream_count: 16 |
| │ └─Selection_34 | 3013422.29 | mpp[tiflash] | | eq(jsm_assets2.obj_new.workspace_id, "8a6526e6-cd57-4216-bac6-358a6177d221") |
| │ └─IndexRangeScan_33 | 129249.00 | mpp[tiflash] | table:o1, index:text_value_7(text_value_7) | range:["8a6526e6-cd57-4216-bac6-358a6177d221","8a6526e6-cd57-4216-bac6-358a6177d221"], search func:fts_match_word("Fagor", jsm_assets2.obj_new.text_value_7), keep order:false |
| └─ExchangeReceiver_39(Probe) | 33682279.58 | mpp[tiflash] | | |
| └─ExchangeSender_38 | 33682279.58 | mpp[tiflash] | | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: jsm_assets2.obj_relationship_new.workspace_id, collate: utf8mb4_bin], [name: jsm_assets2.obj_relationship_new.object_id, collate: binary] |
| └─TableRangeScan_37 | 33682279.58 | mpp[tiflash] | table:r | range:["8a6526e6-cd57-4216-bac6-358a6177d221","8a6526e6-cd57-4216-bac6-358a6177d221"], keep order:false |
+--------------------------------------------+-------------+--------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.08 sec)
mysql> SELECT COUNT(*) FROM obj_new o1 WHERE MATCH(o1.text_value_7) AGAINST('Fagor' IN BOOLEAN MODE) and o1.workspace_id='8a6526e6-cd57-4216-bac6-358a6177d221';
+----------+
| COUNT(*) |
+----------+
| 129249 |
+----------+
1 row in set (0.12 sec)
```
### 2. What did you expect to see? (Required)
### 3. What did you see instead (Required)
### 4. What is your TiDB version? (Required)
feature/fts branch
Contributor guide
Research direction
Start by reproducing the two SQL examples and comparing EXPLAIN estRows for the FTS IndexRangeScan with the COUNT(*) result. Trace the full-text search estimation path in the TiDB Go code; done means large FTS result sets receive materially accurate row estimates without regressing the shown query plans.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100