Repository navigation
planner: FTS alternative logical plan can choose a higher-cost ILIKE fallback plan over native FTS #68487
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
This was found while testing the feature/fts branch with the Amazon review dataset. This does not affect master.
explain SELECT count(*)
FROM amazon_review
WHERE MATCH(`review_body`, `review_headline`, `product_title`)
AGAINST('stainles' IN BOOLEAN MODE);
SELECT count(*)
FROM amazon_review
WHERE MATCH(`review_body`, `review_headline`, `product_title`)
AGAINST('stainles' IN BOOLEAN MODE);
set @@tidb_opt_enable_alternative_logical_plans = on;
explain SELECT count(*)
FROM amazon_review
WHERE MATCH(`review_body`, `review_headline`, `product_title`)
AGAINST('stainles' IN BOOLEAN MODE);With alternative logical plans disabled, the plan uses TiFlash native FTS:
+-------------------------------+---------+--------------+-------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+--------------+-------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_18 | 1.00 | root | | funcs:count(Column#22)->Column#18 |
| └─TableReader_20 | 1.00 | root | | MppVersion: 3, data:ExchangeSender_19 |
| └─ExchangeSender_19 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─HashAgg_10 | 1.00 | mpp[tiflash] | | funcs:count(1)->Column#22 |
| └─IndexRangeScan_17 | 1000.00 | mpp[tiflash] | table:amazon_review, index:review_body(review_body, review_headline, product_title) | range:[-inf,+inf], search func:fts_match_word("stainles", fts.amazon_review.review_body, fts.amazon_review.review_headline, fts.amazon_review.product_title), keep order:false |
+-------------------------------+---------+--------------+-------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The query returned:
count(*) = 31884
With alternative logical plans enabled, the plan switches to a TiKV table full scan with root-side ILIKE filters:
+----------------------------+---------------+-----------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------+---------------+-----------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_7 | 1.00 | root | | funcs:count(1)->Column#18 |
| └─Selection_15 | 6789422775.52 | root | | or(ifnull(ilike(fts.amazon_review.review_body, "%stainles%", 92), 0), or(ifnull(ilike(fts.amazon_review.review_headline, "%stainles%", 92), 0), ifnull(ilike(fts.amazon_review.product_title, "%stainles%", 92), 0))) |
| └─TableReader_14 | 6844176185.00 | root | | data:TableFullScan_13 |
| └─TableFullScan_13 | 6844176185.00 | cop[tikv] | table:amazon_review | keep order:false |
+----------------------------+---------------+-----------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The native FTS plan estimates 1000.00 rows at the FTS index scan and runs in TiFlash MPP, while the chosen fallback plan estimates around 6.8B rows and scans the whole table from TiKV with root-side ILIKE evaluation.
2. What did you expect to see? (Required)
The alternative logical plan framework should keep the native FTS plan when it is executable and cheaper than the ILIKE fallback plan.
If the fallback plan has much higher estimated cost, it should not replace the native FTS plan.
3. What did you see instead (Required)
After enabling tidb_opt_enable_alternative_logical_plans, TiDB chose the ILIKE fallback plan over the native FTS plan, even though the fallback plan is a huge full table scan and has much higher estimated rows.
This defeats the intended cost-based selection behavior for alternative logical plans.
4. What is your TiDB version? (Required)
feature/fts branch after PR #68383.
Activity
AilinKid commentedon May 22, 2026
should be closed by #68525