pingcap / pingcap/tidb

FTS line count estimation is not used in the optimizer

Open
#68,618 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

### 1. Minimal reproduce step (Required)

On the `feature/fts` branch, run `EXPLAIN` for a TiCI FTS query where the TiCI scan is consumed by upper operators such as `Selection`, `Projection`, or `HashJoin`.

#### Case 1: FTS predicate in a join query

```sql
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 MATCH(o1.text_value_7) AGAINST('Fagor' IN BOOLEAN MODE);
```

Key estimates from the plan:

```text
IndexRangeScan_33 602077.00
Projection_61 141217512.37
HashJoin_60 141217512.37
```

For comparison, the same join without the FTS predicate has the same upper row estimate:

```sql
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;
```

Key estimates from the plan:

```text
TableFullScan_33 14000000.00
Projection_100 141217512.37
HashJoin_99 141217512.37
```

The TiCI FTS estimate is reflected by the `IndexRangeScan` row count, but the upper `Projection` and `HashJoin` still use the same row estimate as the plan without the FTS predicate.

#### Case 2: FTS predicate plus an additional table filter

```sql
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);
```

Key estimates from the plan:

```text
IndexRangeScan_33 129249.00
Selection_34 3013422.29
Projection_61 33895180.05
HashJoin_60 33895180.05
```

The row count becomes larger after the `Selection` above the TiCI scan. This indicates that the upper operators are not deriving their estimates from the TiCI FTS access path row count.

### 2. What did you expect to see? (Required)

After TiCI returns the FTS row count estimate, the planner should propagate that estimate to the final `DataSource` stats. Upper operators above the TiCI scan, including `Selection`, `Projection`, and `HashJoin`, should derive their row estimates from the TiCI FTS row count plus any residual filter selectivity.

### 3. What did you see instead (Required)

The TiCI access path updates `CountAfterAccess`, so the `IndexRangeScan` row count uses the TiCI FTS estimate. However, the corresponding `DataSource.StatsInfo` is not refreshed with that count. As a result, upper operators still use the row count derived from the original table statistics, which can make the optimizer cost the plan as if the FTS predicate had not reduced the input rows.

### 4. What is your TiDB version? (Required)

`feature/fts` branch.

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.