PostgreSQL新增未被使用的索引致查询性能骤降问题排查

问题背景 #

作为PostgreSQL新手，新增索引后查询执行时间从个位数毫秒骤增至数百毫秒甚至秒级，但EXPLAIN结果显示该索引并未被使用。

核心表结构 #

classes表 #

> \d+ classes
                                                        Table "public.classes"
    Column    |            Type             | Collation | Nullable |         Default          | Storage  | Stats target | Description
--------------+-----------------------------+-----------+----------+--------------------------+----------+--------------+-------------
 classid      | bigint                      |           | not null | gen_random_js_safe_int() | plain    |              |
 schoolid     | bigint                      |           |          |                          | plain    |              |
 classname    | character varying(255)      |           | not null |                          | extended |              |
Indexes:
    "classes_classname_schoolid_idx" UNIQUE, btree (classname, schoolid) WHERE schoolid IS NOT NULL
Foreign-key constraints:
    "classes_schoolid_fkey" FOREIGN KEY (schoolid) REFERENCES schools(schoolid)
Referenced by:
    TABLE "classtoschool" CONSTRAINT "classtoschool_classid_fkey" FOREIGN KEY (classid) REFERENCES classes(classid) ON DELETE CASCADE

classtoschool表 #

> \d+ classtoschool
                                                     Table "public.classtoschool"
      Column       |            Type             | Collation | Nullable |           Default           | Storage  | Stats target | Description
-------------------+-----------------------------+-----------+----------+-----------------------------+----------+--------------+-------------
 classid           | bigint                      |           |          |                             | plain    |              |
 schoolid          | bigint                      |           | not null |                             | plain    |              |
Foreign-key constraints:
    "classtoschool_classid_fkey" FOREIGN KEY (classid) REFERENCES classes(classid) ON DELETE CASCADE
    "classtoschool_schoolid_fkey" FOREIGN KEY (schoolid) REFERENCES schools(schoolid) ON DELETE CASCADE

schools表 #

> \d+ schools
                                                                  Table "public.schools"
               Column                |            Type             | Collation | Nullable |         Default          | Storage  | Stats target | Description
-------------------------------------+-----------------------------+-----------+----------+--------------------------+----------+--------------+-------------
 schoolid                            | bigint                      |           | not null | gen_random_js_safe_int() | plain    |              |
 schoolStatus                        | character varying(100)      |           | not null |                          | extended |              |
Referenced by:
    TABLE "classes" CONSTRAINT "classes_schoolid_fkey" FOREIGN KEY (schoolid) REFERENCES schools(schoolid)
    TABLE "classtoschool" CONSTRAINT "classtoschool_schoolid_fkey" FOREIGN KEY (schoolid) REFERENCES school(schoolid) ON DELETE CASCADE

涉事索引 #

"classes_classname_schoolid_idx" UNIQUE, btree (classname, schoolid) WHERE schoolid IS NOT NULL

性能对比 #

• 新增索引前：查询执行时间为个位数毫秒
• 新增索引后：执行时间达数百毫秒，高负载下甚至秒级

查询语句及执行分析 #

查询语句：

EXPLAIN ANALYZE VERBOSE SELECT
  schools.ommitedColumnA,
  classes.classId,
  classes.classname,
  classes.omittedColumnB,
  classes.omittedColumnC,
  classes.omittedColumnD,
  classes.omittedColumnE
FROM
  classes
  LEFT JOIN classtoschool ON (classes.classId = classtoschool.classid)
  LEFT JOIN schools ON (classtoschool.schoolid = schools.schoolid)
WHERE
  (classes.classname = 'maths') AND
  (schools.schoolid = '12345678') AND
  ((schools.banned = false OR schools.banned IS NULL) AND schools.schoolStatus = 'RUNNING') AND
  (schools.schoolStatus != 'BREAK' OR schools.schoolStatus IS NULL);

新增索引后的EXPLAIN结果显示未使用该索引，但执行时间长达3191.475ms；移除索引后执行时间仅190.426ms。查询pg_stat_user_tables发现，有索引的数据库idx_tup_fetch值为30亿，无索引的为30万。

疑问解答 #

1. 为何未被使用的索引会影响查询执行？其作用机制是什么？ #

未被直接调用的索引仍可能通过以下途径干扰查询性能：

• 统计信息偏差：PostgreSQL优化器依赖表和索引的统计信息生成执行计划。新增索引后，统计信息的更新或重新计算可能出现偏差，导致优化器错误选择了低效执行计划——比如原本走全表扫描或其他高效索引，现在错误触发嵌套循环、哈希连接的低效组合，或是对匹配行数的估算严重失真，最终拉高执行成本。
• 间接索引扫描触发：idx_tup_fetch的数值差异说明，有索引时优化器实际选择了大量通过索引抓取数据的路径，哪怕不是你新增的这个索引。新增索引可能改变了优化器对表访问路径的判断逻辑，引发不必要的索引扫描和数据抓取，拖慢整体查询。
• 缓存竞争：新增索引会占用额外的缓存空间，在高负载场景下，可能挤压其他关键数据的缓存命中率，间接导致查询性能下降。

2. 为何该索引仅在这个数据库中影响性能，其他数据量更大的数据库却无此问题？ #

这种差异源于不同数据库的数据分布、统计状态、参数配置的区别：

• 数据分布差异：该数据库中classes表的classname = 'maths'记录数、schoolid非空比例，可能和其他库差异极大。新增的带WHERE schoolid IS NOT NULL的部分索引，刚好触发了优化器对该条件下行数的严重估算错误，而其他库的数据分布不会引发这个问题。
• 统计信息时效性：如果该库的统计信息长期未更新，新增索引后优化器基于过时数据生成了错误计划；而其他库的统计信息是最新的，优化器能正确判断索引价值。
• 参数配置差异：不同库的default_statistics_target、random_page_cost等优化器参数可能不同，导致该库中优化器认为某个低效计划的成本更低，而其他库不会触发这个选择。
• 负载模式差异：该库的高负载场景下，索引带来的缓存竞争、内存占用问题被放大；而其他库的负载模式或缓存命中率更高，抵消了索引的负面影响。

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