PostgreSQL查询计划器未选用pgroonga索引致多表文本查询缓慢

针对多表查询中PostgreSQL查询计划器错误选择索引导致性能低下的问题，可尝试以下解决方案：

1. 强制指定索引（查询提示） #

直接在子查询中指定目标pgroonga索引，强制查询计划器走预期的索引路径：

explain(analyze, verbose, buffers, settings) 
select * from products_locations
where product_id in (
  SELECT product_id FROM search."en" 
  WHERE text &@ 'varza' 
  INDEX (search_en_text_idx) -- 强制使用pgroonga复合索引
);

若你的PostgreSQL版本不支持SQL标准的INDEX提示，可改用PostgreSQL特定语法：

explain(analyze, verbose, buffers, settings) 
select * from products_locations
where product_id in (
  SELECT /*+ IndexScan(search."en" search_en_text_idx) */ product_id 
  FROM search."en" 
  WHERE text &@ 'varza'
);

2. 物化子查询，强制优先执行文本搜索 #

通过MATERIALIZED关键字让子查询先执行并缓存结果，避免查询计划器将子查询与主表的连接逻辑优化为不合适的路径：

explain(analyze, verbose, buffers, settings) 
select * from products_locations
where product_id in (
  SELECT product_id FROM search."en" 
  WHERE text &@ 'varza' 
  MATERIALIZED
);

3. 重构查询为JOIN语句 #

将IN子查询改写为JOIN形式，帮助查询计划器更清晰地识别最优执行路径：

explain(analyze, verbose, buffers, settings) 
select pl.* 
from products_locations pl
join search."en" se on pl.product_id = se.product_id
where se.text &@ 'varza';

4. 优化统计信息准确性 #

PostgreSQL查询计划器依赖统计信息判断索引选择性，可针对search.en表的text字段提高统计目标，再重新收集统计信息：

-- 提高text字段的统计目标值
ALTER TABLE search."en" ALTER COLUMN "text" SET STATISTICS 1000;
-- 重新收集表统计信息
ANALYZE search."en";

5. 调整查询计划器成本参数 #

除random_page_cost外，可尝试调整以下参数，让计划器更倾向于选择索引扫描：

-- 临时设置，仅对当前会话有效
SET enable_seqscan = off;
SET enable_parallel_seqscan = off;
SET cpu_tuple_cost = 0.01; -- 降低处理行的成本权重，凸显索引扫描优势

注意：生产环境需先在测试环境验证参数影响，再考虑全局配置。

6. 检查索引有效性 #

确认pgroonga索引状态正常，未被禁用或损坏：

-- 查看索引定义
SELECT schemaname, tablename, indexname, indexdef 
FROM pg_indexes 
WHERE schemaname = 'search' AND tablename = 'en';

-- 检查索引可用性
SELECT relname, indisvalid 
FROM pg_index i
JOIN pg_class c ON i.indexrelid = c.oid
WHERE c.relname IN ('search_en_text_idx', 'search_en_char_based_text_idx');

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