PostgreSQL单词全文搜索性能低下的原因及问题排查

问题现象 #

在250万行的posts表中：

• 多关键词（含重复词，如yorkie yorkie）全文搜索带ORDER BY created_at DESC LIMIT 100时，查询规划器使用posts_title_simple_gin索引，执行时间约3.7ms；
• 单关键词（如yorkie）搜索时，规划器放弃GIN索引，改用index_posts_on_created_at并行扫描后过滤，执行时间超33秒；
• 移除ORDER BY后，单关键词搜索正常使用GIN索引，性能良好；
• 两种查询实际匹配行数均为56条。

问题根源 #

PostgreSQL查询规划器的行数估算偏差：
规划器错误估算单关键词匹配的行数远高于实际值（虽然实际只有56条），认为按created_at排序后过滤的成本更低；而多关键词查询时，规划器估算匹配行数更少，因此选择GIN索引路径。

解决方案 #

1. 强制使用GIN索引 #

通过索引提示强制规划器选用全文索引，直接解决执行计划选择错误的问题：

EXPLAIN ANALYZE
SELECT * FROM "posts" INDEX USING posts_title_simple_gin
WHERE to_tsvector('simple', posts.title) @@ websearch_to_tsquery('simple', 'yorkie')
ORDER BY "posts"."created_at" DESC
LIMIT 100 OFFSET 0;

2. 优化统计信息，修正行数估算 #

更新表统计信息，让规划器更准确判断匹配行数：

-- 更新全表统计
ANALYZE posts;

-- 若仍无改善，提高title字段的统计目标（默认100，可设为1000）
ALTER TABLE posts ALTER COLUMN title SET STATISTICS 1000;
ANALYZE posts;

更高的统计目标会让PostgreSQL收集更详细的字段数据分布，从而更准确估算全文搜索的匹配行数。

3. 子查询拆分查询逻辑 #

先通过GIN索引获取所有匹配的记录ID，再关联原表并排序，避免规划器选错执行计划：

EXPLAIN ANALYZE
SELECT p.*
FROM posts p
JOIN (
    SELECT id FROM posts
    WHERE to_tsvector('simple', title) @@ websearch_to_tsquery('simple', 'yorkie')
) AS matches ON p.id = matches.id
ORDER BY p.created_at DESC
LIMIT 100;

子查询仅返回匹配的56条ID，后续关联排序的成本极低，性能接近多关键词查询。

临时方案说明 #

重复关键词（如yorkie yorkie）的临时方案之所以有效，是因为多关键词的tsquery会让规划器估算匹配行数更少，从而选择GIN索引，但该方法不规范，建议使用上述正式方案替代。

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