PostgreSQL RDS相同查询不同companyId索引选择不同性能差异问题

• 执行计划差异核心来自PostgreSQL基于成本的优化器的估算偏差：
  1. 慢查询对应的company_id = 9130347227057236，优化器估算符合过滤条件的行有92855条，错误判定「按主键id顺序扫描，很快就能找到第一条符合条件的记录」的成本更低，实际该公司符合条件的记录id分布极靠后，扫描过程中过滤掉了5.3亿条无效记录，导致耗时超过6分钟。
  2. 快查询对应的company_id = 9130348260181756，优化器估算符合条件的行仅2242条，判定先走items_1_primary_company_id_item_type_id联合索引捞出符合条件的行，再做topN排序取第一条的成本更低，实际执行仅需不到10ms。
• 现有索引缺陷：当前的(company_id, item_type_id)联合索引未包含hidden过滤字段和id排序字段，走该索引后仍需要回表过滤hidden，且需要额外排序，也是优化器容易误判走主键索引的原因之一。

• 最优方案：创建适配查询的覆盖联合索引，彻底消除执行计划误判的可能，生产环境建议加CONCURRENTLY参数避免锁表：

CREATE INDEX CONCURRENTLY idx_items_1_primary_cmpy_itemtype_hidden_id 
ON items_1_primary (company_id, item_type_id, hidden, id);

该索引完全匹配查询的过滤条件和排序逻辑，符合条件的记录在索引中已经按id有序排列，查询时可以直接定位到第一条符合条件的记录，无需额外过滤、排序，所有company_id的查询都能稳定在毫秒级返回。

• 临时应急方案：可通过查询Hint强制指定走现有联合索引，避免优化器误判：

select /*+ IndexScan(items_1_primary items_1_primary_company_id_item_type_id) */ *
from items_1_primary
WHERE items_1_primary.item_type_id IN (1,2)
  AND items_1_primary.hidden IS NULL
  AND items_1_primary.company_id = 9130347227057236
ORDER BY items_1_primary.id
LIMIT 1 OFFSET 0;

• 辅助优化：执行全表统计信息更新，修正优化器的行估算偏差，减少后续执行计划误判概率：

ANALYZE items_1_primary;

• 查询改写替代方案：也可以通过子查询先取最小id再关联拿行数据，引导优化器走正确路径：

SELECT * FROM items_1_primary 
WHERE id = (
  SELECT MIN(id) FROM items_1_primary 
  WHERE company_id = 9130347227057236 
  AND item_type_id IN (1,2) 
  AND hidden IS NULL
);

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