pingcap / pingcap/tidb

planner: push a substring pre-filter for multi-column MATCH ... AGAINST

Open
#70,505 0 comments 0 reactions 0 assignees View on GitHub
sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

Push a substring pre-filter for a multi-column `MATCH ... AGAINST`, which is currently skipped.

Follow-up to #70497, which added the pre-filter, and ref #70486.

### Problem

#70497 pushes the substring predicates a locally evaluated `MATCH` entails down to the storage layer, so most non-matching rows are discarded before TiDB applies the exact check. It does this only for a single-column `MATCH`.

A multi-column `MATCH` is satisfied by a token in **any** of its columns, but the pre-filter was built from the first column alone, which discarded rows the `MATCH` accepts. On a table where `storage` appears only in `body`, `match(title, body) against('+storage')` returned one row instead of two. The fix in #70497 was to skip the pre-filter entirely for that case.

That is correct but leaves value on the floor: `match(title, body)` is the canonical full-text shape, so the most common real query gets no pushdown at all.

### Proposed change

Build a disjunction per required token instead of one predicate on one column:

```sql
-- match(title, body) against('+storage +engine')
(LOWER(title) LIKE '%storage%' OR LOWER(body) LIKE '%storage%')
AND (LOWER(title) LIKE '%engine%' OR LOWER(body) LIKE '%engine%')
```

This is sound for the same reason the single-column form is: if a document matches, each required token appears in at least one matched column, so at least one disjunct holds. It over-approximates, and the `MATCH` still decides.

`BuildFTSLocalMatchPreFilters` takes one column today and would take the matched set; the OR-building code already exists for optional terms.

### Watch out

- **Binary columns.** If any matched column is binary the disjunction must be abandoned entirely, not merely built from the non-binary columns: `LOWER` is a no-op on binary, so a document matching through that column would fail every remaining disjunct and be discarded. This is the same unsoundness that #70497 already had to fix once.
- **Test by property, not by example.** Three wrong-result bugs have been found in this pre-filter - binary columns, multi-column, and a boolean term collapsing the query - and each was a case where the filter decided rather than narrowed. Hand-picked cases missed the first two. A test that generates column and token combinations and asserts every document the `MATCH` accepts also satisfies the generated predicate would catch this class directly.

Contributor guide

Open the contributing guide

Research direction

Start at BuildFTSLocalMatchPreFilters and trace the existing OR-building code for optional terms. Extend the matched-column handling while preserving the binary-column guard, then add a property-based test showing every document accepted by MATCH also satisfies the generated pre-filter.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases, performance
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.