Aurora PostgreSQL全文检索含停用词无结果，本地PostgreSQL正常

问题现象 #

本地PostgreSQL 14数据库与AWS Aurora Serverless V2（PostgreSQL兼容）数据完全一致，使用Postgres内置全文检索时出现差异：

• 搜索包含停用词（如"the"）的短语（例如"The Big Bang"），本地实例能正常返回标题为"Big Bang"的条目
• Aurora实例无搜索结果，仅移除"the"后才能找到匹配行
• 已确认两个实例中预生成的tsvector列完全一致

查询语句示例（实际使用带GIN索引的预生成向量列）：

select * from some_table 
where to_tsvector(title) @@ plainto_tsquery('The Big Bang') 

预生成向量的触发器及函数：

CREATE FUNCTION vector_column_trigger() RETURNS trigger AS $$
begin
  new.vector_column :=
     setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
     setweight(to_tsvector('pg_catalog.english', coalesce(new.description,'')), 'C');
  return new;
end
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_vector_column BEFORE INSERT OR UPDATE
    on some_table FOR EACH ROW EXECUTE FUNCTION vector_column_trigger();

排查思路 #

1. 对比全文检索默认配置

   • 在两个实例分别执行以下命令，查看default_text_search_config是否一致：

     SHOW default_text_search_config;
     

   • 若Aurora使用了非pg_catalog.english的默认配置，会导致plainto_tsquery解析规则不同。

2. 验证plainto_tsquery的解析结果

   • 在两个实例中执行相同解析命令，对比输出：

     SELECT plainto_tsquery('english', 'The Big Bang');
     

   • 正常情况下输出应为'big' & 'bang'，若Aurora输出包含'the'，则说明停用词规则未生效。

3. 检查全文检索配置的细节

   • 查看english配置的停用词表与词典映射：

     SELECT cfgname, cfgstop, cfgdict FROM pg_ts_config WHERE cfgname = 'english';
     

   • 对比本地与Aurora的结果，确认停用词表（cfgstop）是否为默认的english_stem，词典映射是否一致。

4. 验证GIN索引有效性

   • 重建Aurora的GIN索引，排除索引损坏导致的问题：

     REINDEX INDEX vector_column_gin_idx; -- 替换为实际索引名
     

   • 重建后重新测试搜索。

5. 检查数据库参数差异

   • 对比两个实例的全文检索相关参数：

     SELECT name, setting FROM pg_settings WHERE name LIKE '%text_search%';
     

   • 重点关注text_search_config、text_search_debug等参数是否存在差异。

解决方案 #

1. 显式指定全文检索配置

   • 修改查询语句，显式指定使用pg_catalog.english配置，避免依赖默认值：

     select * from some_table 
     where vector_column @@ plainto_tsquery('pg_catalog.english', 'The Big Bang');
     

2. 统一默认文本搜索配置

   • 在Aurora实例中修改数据库默认配置，与本地保持一致：

     ALTER DATABASE your_database_name SET default_text_search_config = 'pg_catalog.english';
     

   • 修改后需重新连接数据库生效。

3. 修复全文检索配置的停用词规则

   • 若Aurora的english配置使用了错误的停用词表，执行以下命令修正：

     ALTER TEXT SEARCH CONFIGURATION pg_catalog.english
     ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word, hword, hword_part
     WITH english_stem;
     

   • 该操作需超级用户权限，修改后重新生成向量列或重建索引。

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