PostgreSQL 1600万行表全文检索加排序性能优化求助

问题根源 #

从执行计划可以看到，添加ORDER BY updated_utc DESC后，PostgreSQL选择了从products_updated_utc_idx倒序扫描全表并过滤全文条件——因为查询的关键词dsdsdsd不存在，数据库扫描了近1500万行却一无所获，最终耗时极长。而无排序查询时，数据库通过GIN索引快速判定无匹配结果，所以执行很快。

对于存在的关键词，GIN索引能快速定位匹配行，排序成本低，速度正常；但对于无匹配/匹配极少的关键词，错误的执行计划会导致全表扫描。

可行优化方案 #

1. 强制使用GIN索引后排序（临时应急） #

通过查询提示强制数据库优先使用GIN索引获取匹配结果，再排序取前1000条：

SELECT * FROM products 
WHERE to_tsvector('english', iid) @@ plainto_tsquery('english', 'dsdsdsd') 
ORDER BY updated_utc DESC LIMIT 1000;
-- 或者添加查询提示强制走GIN索引的Bitmap扫描
SELECT /*+ BitmapScan(products iid_gin_idx) */ * 
FROM products 
WHERE to_tsvector('english', iid) @@ plainto_tsquery('english', 'dsdsdsd') 
ORDER BY updated_utc DESC LIMIT 1000;

这种方式能让数据库先通过GIN索引快速确认是否有匹配，避免全表扫描。如果匹配结果较多，排序的内存开销可能会增加，但work_mem已调至5GB，足以支撑大部分场景。

2. 创建带updated_utc的GIN复合索引（长期最优） #

使用btree_gin扩展创建同时包含全文检索向量和updated_utc的复合GIN索引，让数据库能直接在索引内完成过滤+排序，无需回表后再排序：

-- 先确保btree_gin扩展已安装
CREATE EXTENSION IF NOT EXISTS btree_gin;
-- 创建复合GIN索引
CREATE INDEX IF NOT EXISTS iid_updated_gin_idx 
ON products USING gin (to_tsvector('english', iid::text), updated_utc);

这个索引可以同时满足全文检索条件和排序需求，数据库会直接从索引中筛选匹配行并按updated_utc倒序取出前1000条，大幅降低IO和计算开销。

3. 物化CTE强制执行顺序 #

通过物化CTE强制数据库先执行全文检索获取所有匹配结果，再进行排序：

WITH matched AS MATERIALIZED (
    SELECT * FROM products 
    WHERE to_tsvector('english', iid) @@ plainto_tsquery('english', 'dsdsdsd')
)
SELECT * FROM matched 
ORDER BY updated_utc DESC LIMIT 1000;

MATERIALIZED关键字会让PostgreSQL先执行CTE中的查询并将结果存入临时表，再对临时表排序，避免优化器选择错误的执行路径。

4. 调整查询计划参数（全局/会话级） #

临时调整优化器参数，让数据库更倾向于选择GIN索引而非B-tree索引：

-- 会话级生效，仅对当前连接有效
SET enable_indexscan = off;
SET enable_parallel_indexscan = off;

-- 执行查询
SELECT * FROM products 
WHERE to_tsvector('english', iid) @@ plainto_tsquery('english', 'dsdsdsd') 
ORDER BY updated_utc DESC LIMIT 1000;

-- 恢复默认参数（可选）
SET enable_indexscan = on;
SET enable_parallel_indexscan = on;

这种方式适合临时验证，不建议长期全局修改，避免影响其他查询的执行计划。

验证效果 #

执行优化后的查询后，查看EXPLAIN ANALYZE结果，确认执行计划变为：

1. 先通过GIN索引获取匹配行（Bitmap Index Scan）
2. 再对匹配行进行排序（Sort）并取前1000条
   此时即使查询无匹配的关键词，执行时间也会和无排序查询一致（毫秒级）。

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