pingcap / pingcap/tidb

The FTS estimates the number of rows incorrectly when the number of rows to be fts filtered is very large

Open
#68,619 2 comments 0 reactions 0 assignees View on GitHub
feature/developing severity/moderate sig/planner type/bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.