[Tech Request]: Fulltext index BloomFilter pre-filter pushdown optimization

[ck89119]

Is there an existing issue for the same tech request?

• I have checked the existing issues.

Does this tech request not affect user experience?

• This tech request doesn't affect user experience.

What would you like to be added ?

When a fulltext index query has additional WHERE filters on the source table (e.g. WHERE category='news' AND match(content) against('keyword')), push down a BloomFilter of the filtered PKs to the fulltext index table scan, so that irrelevant doc_id rows are skipped at the reader level.

Plan structure (when pushdown is enabled):

outerJoin(scanNode, innerJoin(ft_func_chain, secondScanProject))

• secondScanProject: scans the source table with the non-fulltext filters, outputs only PK
• BloomFilter runtime filter: secondScan(build) → ft_func(probe) — pushes filtered PKs into fulltext index table scan
• IN-list runtime filter: innerJoin(build) → scanNode(probe) — pushes fulltext match results back to source table scan

Why is this needed ?

Currently, fulltext index queries scan the entire index table regardless of additional WHERE conditions on the source table. For example:

SELECT * FROM articles
WHERE category = 'news'
AND match(content) against('database' in natural language mode);

The fulltext index scan processes all doc_id entries, then JOINs with the source table where category='news' filters out most rows. This wastes significant I/O when the WHERE condition is highly selective.

With BloomFilter pre-filter pushdown:

1. First scan the source table with category='news' to collect matching PKs
2. Build a BloomFilter from these PKs
3. Push the BloomFilter down to the fulltext index table reader
4. Skip blocks/rows whose doc_id doesn't pass the BloomFilter check

This significantly reduces I/O for fulltext queries with selective non-fulltext filters.

[ck89119]

Add a switch (default false) to control this optimization.
TODO: Run benchmarks to obtain performance data before and after optimization.

[ck89119]

Background

数据集

Wikipedia dump, 下载链接：https://dumps.wikimedia.org/enwiki/latest/enwiki-latest-pages-articles.xml.bz2

table schema

create table articles (
    id bigint primary key,
    title varchar(500) not null,
    body text not null
);
create fulltext index ftidx_articles on articles(title, body);

query sql

SELECT * FROM articles
WHERE id < {id_limit}
  AND match(title, body) against({query});

测试结果

10W篇文章

id 范围：[10, 137615]

against('is')

id_limit	disable(ms)	enable(ms)
10	379.4	5.9
100	373.6	5.18
1000	721.4	62.6
10000	1268.7	532.6
100000	5909.0	5694.7
1000000	7148.0	7457.4

against('machine learning')

id_limit	disable(ms)	enable(ms)
10	22.7	6.4
100	22.6	21.6
1000	24.6	18.7
10000	369.3	27.4
100000	1843.4	1841.5
1000000	2268.3	2285.7

[ck89119]

when id is highly selective, performance is significantly improved;
when id selectivity is low (id < 100000), performance is almost as same as not enabling the pre-filter;
when id has no selectivity (id < 1000000), performance is a little worse, due to the overhead of building a big bloom filter.