PostgreSQL中GIN索引未生效，全文搜索无法使用问题求助

问题重现 #

执行了以下操作：

• 创建表：

CREATE TABLE test_table (id SERIAL PRIMARY KEY, timestamp bigint, data TEXT);

• 插入数据：

INSERT INTO test_table (timestamp, data) VALUES (1710240978464, 'how are u');
INSERT INTO test_table (timestamp, data) VALUES (1710240982533, 'where are u');

• 创建GIN索引：

CREATE INDEX search_fulltext_idx ON test_table USING GIN(to_tsvector('english', data::text));

执行查询时，触发顺序扫描（Seq Scan）且未命中索引，同时返回0行并提示停用词问题：

EXPLAIN ANALYZE SELECT * FROM test_table WHERE data @@ to_tsquery('english','how');

执行结果及提示：

NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored
QUERY PLAN #

Seq Scan on test_table (cost=10000000000.00..10000000001.52 rows=1 width=44) (actual time=51.640..51.641 rows=0 loops=1)
Filter: (data @@ ''::tsquery)
Rows Removed by Filter: 2
Planning Time: 0.144 ms
JIT:
Functions: 2
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.423 ms, Inlining 8.558 ms, Optimization 31.044 ms, Emission 11.861 ms, Total 51.886 ms
Execution Time: 52.107 ms
(9 rows)

错误原因 #

1. 停用词导致查询条件无效：how属于英文全文搜索默认的停用词（stop word），这类无实际检索价值的词汇会被自动忽略，to_tsquery('english','how')返回空的tsquery，导致过滤条件data @@ ''::tsquery无法匹配任何数据，直接返回0行。
2. 查询表达式与索引不匹配：索引是基于to_tsvector('english', data)创建的，但查询条件用的是data @@ to_tsquery(...)，PostgreSQL无法将该条件与索引的表达式关联，因此无法触发索引扫描。
3. 数据量过小：表中仅2行数据，PostgreSQL优化器会判定顺序扫描比索引扫描更高效，即便表达式匹配，也可能优先选择Seq Scan。

修复步骤 #

1. 对齐查询与索引的表达式定义 #

修改查询条件，让左边的表达式和索引创建时的逻辑完全一致，这样优化器才能识别并使用GIN索引：

EXPLAIN ANALYZE SELECT * FROM test_table 
WHERE to_tsvector('english', data) @@ to_tsquery('english', 'where');

（注：若where也属于停用词，可先插入含非停用词的数据：INSERT INTO test_table (timestamp, data) VALUES (1710240990000, 'hello world');，再查询to_tsquery('english', 'hello')）

2. 使用非停用词验证索引效果 #

英文停用词包含常见代词、介词、副词等（如how、are、where、u），选择有实际语义的词汇测试：

-- 插入含非停用词的数据
INSERT INTO test_table (timestamp, data) VALUES (1710241000000, 'PostgreSQL full text search');
-- 使用非停用词查询
EXPLAIN ANALYZE SELECT * FROM test_table 
WHERE to_tsvector('english', data) @@ to_tsquery('english', 'PostgreSQL');

此时执行计划会显示Bitmap Index Scan on search_fulltext_idx，说明索引已生效。

3. （可选）自定义停用词表 #

若业务需要保留停用词的搜索能力，可以创建自定义停用词表替换默认配置：

-- 创建自定义停用词表
CREATE TEXT SEARCH DICTIONARY my_english_stop (
    TEMPLATE = pg_catalog.simple,
    STOPWORDS = my_stopwords
);
-- 创建自定义文本搜索配置
CREATE TEXT SEARCH CONFIGURATION my_english (COPY = english);
ALTER TEXT SEARCH CONFIGURATION my_english
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word, hword, hword_part
WITH my_english_stop, english_stem;
-- 使用自定义配置创建索引并查询
CREATE INDEX search_fulltext_idx_custom ON test_table USING GIN(to_tsvector('my_english', data));
SELECT * FROM test_table WHERE to_tsvector('my_english', data) @@ to_tsquery('my_english', 'how');

内容的提问来源于stack exchange，提问作者Divakar V