PostgreSQL 10索引未被使用导致查询耗时超30秒的问题排查求助

首先，针对你遇到的两个慢查询问题，我来逐一分析索引设计的合理性，并给出具体的排查和优化方案：

一、针对vulns_historicalvuln表的慢查询分析 #

查询语句 #

SELECT * FROM vulns_historicalvuln WHERE history_type <> '+' order by id desc fetch first 10000 rows only;

索引设计问题 #

你创建的btree_varchar索引使用了varchar_pattern_ops，这完全没必要——varchar_pattern_ops仅适用于前缀匹配的LIKE查询（如history_type LIKE 'A%'），而你的查询是简单的不等于比较（<> '+'），普通的BTREE索引就足够了。这也是该索引从未被使用的核心原因之一。

另外从执行计划能看出：

• 数据库选择了vulns_historicalvuln_id_773f2af7（id的BTREE索引）进行反向扫描，仅过滤掉296条不符合条件的行
• 但你执行了SELECT *，数据库需要频繁回表读取完整行数据（每行宽度达1736字节），这带来了大量IO开销，直接导致30秒的耗时

优化方案 #

1. 替换无效索引：先删除现有的btree_varchar索引，创建适配当前查询的普通BTREE索引：

   DROP INDEX IF EXISTS btree_varchar;
   CREATE INDEX CONCURRENTLY idx_vulns_historicalvuln_history_type ON vulns_historicalvuln (history_type);
   

2. 创建复合索引优化排序+过滤：从执行计划的rows=8264960来看，history_type <> '+'的结果集接近全表，单独的history_type索引依然不会被选中。此时建议创建覆盖排序和过滤的复合索引：

   CREATE INDEX CONCURRENTLY idx_vulns_historicalvuln_id_history_type ON vulns_historicalvuln (id DESC, history_type);
   

   这个索引可以让数据库在扫描id倒序的同时，直接过滤掉history_type='+'的行，无需额外回表判断，大幅减少IO开销。
3. 精简查询字段：如果业务不需要所有字段，尽量只查询需要的列，减少回表的数据量。

二、针对vulns表的慢查询分析 #

查询语句 #

SELECT DISTINCT "vulns"."id", "vulns"."uuid", ... ORDER BY "vulns"."updated_at" DESC LIMIT 10

索引设计问题 #

你创建的score索引未被使用，原因是score >=0 AND score <=100的过滤条件几乎匹配全表（返回169846行，接近全表数据），数据库认为顺序扫描比索引扫描更高效。

执行计划的瓶颈在于：

• 先全表扫描，再进行外部磁盘排序（Sort Method: external merge Disk: 277648kB），这是导致3秒耗时的主要原因
• DISTINCT和ORDER BY的组合进一步增加了排序成本

优化方案 #

1. 创建updated_at排序索引：针对ORDER BY updated_at DESC的核心需求，创建单列索引：

   CREATE INDEX CONCURRENTLY idx_vulns_updated_at_desc ON vulns (updated_at DESC);
   

2. 重写查询避免全表排序：先通过索引获取前10个最新的updated_at对应的id，再关联查询完整数据，彻底避免全表排序的开销：

   SELECT DISTINCT v.*, false AS "monitored", '42' AS "org"
   FROM vulns v
   JOIN (
       SELECT id FROM vulns 
       WHERE score BETWEEN 0 AND 100
       ORDER BY updated_at DESC LIMIT 10
   ) top10 ON v.id = top10.id
   ORDER BY v.updated_at DESC;
   

   这个子查询可以利用idx_vulns_updated_at_desc快速获取前10条id，再回表查询数据，性能会有明显提升。
3. 临时调整内存参数：增加work_mem的值（比如设置为64MB），让排序可以在内存中完成，减少磁盘IO：

   SET work_mem = '64MB'; -- 会话级临时设置，可根据需要调整全局参数
   

三、通用排查与调试方法 #

1. 更新统计信息：数据库的查询计划依赖准确的统计信息，执行以下命令更新表统计：

   ANALYZE vulns_historicalvuln;
   ANALYZE vulns;
   

2. 检查索引选择性：判断索引是否有用，需要看过滤条件的选择性（即符合条件的行数占总行数的比例）：

   -- 查看history_type='+'的行数占比
   SELECT COUNT(*) FILTER (WHERE history_type = '+')*100.0/COUNT(*) AS plus_ratio FROM vulns_historicalvuln;
   -- 查看score在0-100的行数占比
   SELECT COUNT(*) FILTER (WHERE score BETWEEN 0 AND 100)*100.0/COUNT(*) AS score_ratio FROM vulns;
   

   如果比例超过80%，数据库通常会选择顺序扫描而非索引扫描，此时索引的价值不大。
3. 监控索引使用情况：持续监控索引的扫描次数，确认优化后的索引是否被使用：

   SELECT relname, indexrelname, idx_scan FROM pg_catalog.pg_stat_user_indexes;
   

4. 调整PostgreSQL配置参数：根据你的硬件配置（4核/16GB内存），可以适当调整以下参数：
   • shared_buffers：建议设置为内存的25%（比如4GB）
   • work_mem：增大到32MB或64MB，减少磁盘排序
   • maintenance_work_mem：增大到1GB，加速索引创建

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