PostgreSQL 10近乎相同查询出现差异巨大执行计划问题咨询

PostgreSQL优化器基于成本估算选择执行路径，两个查询的核心差异是匹配关键词不同导致的行数估算偏差：

• 第一个查询%abc%：优化器估算匹配行数为82654条，占总数据量比例较高，判定按visittime倒序索引扫描更优：从最新记录开始逐个过滤符合address匹配条件的行，找到10条就停止。实际执行中仅过滤了6663条记录就拿到了10个符合条件的结果，所以耗时极短。
• 第二个查询%xyz%：优化器错误估算匹配行数仅为818条，判定先通过GIN Trigram索引捞出所有匹配行、再排序取前10的成本更低。但实际匹配行数达到18926条，远高于估算值，且这些行在堆表中存储分散，产生了大量随机IO读取18243个数据块，最终导致耗时飙升到25秒。

• 修正统计信息偏差
  首先更新表的统计信息，让优化器拿到更准确的匹配行数估算：

  -- 先更新全表统计
  ANALYZE http_requests;
  -- 如果还是估算不准，调高address列的统计采样规模，最高可设为10000
  ALTER TABLE http_requests ALTER COLUMN address SET STATISTICS 2000;
  ANALYZE http_requests;
  

  如果使用SSD存储，还可以调整成本参数让优化器对随机IO的成本估算更准确：

  -- 会话级生效，可写入postgresql.conf全局生效
  SET random_page_cost = 1.1;
  

• 强制优先走时间索引
  如果你这类查询的固定模式都是取最新的N条匹配记录，第一种执行计划的稳定性更高，可以通过以下方式固定执行路径：
  安装pg_hint_plan插件后给查询加索引hint：

  /*+ IndexScan(http_requests md_visittime_idx) */
  select * from http_requests where address ilike '%xyz%' order by visittime desc limit 10;
  

• 场景适配优化
  如果你的业务允许先取匹配结果再按时间排序，且模糊匹配的结果量级普遍较小，可以保留现有索引；如果绝大多数场景都是取最新的N条匹配记录，也可以考虑业务侧做冷热数据分离，将近期的访问记录单独存放在小表中查询，进一步降低扫描成本。

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