PostgreSQL 12索引使用异常：为何未选用过滤索引？

问题描述 #

在PostgreSQL 12环境下执行以下查询语句：

SELECT MIN("id"), MAX("id") FROM "public"."tablename" 
WHERE ( "updated_at" >= '2022-07-24 09:08:05.926533' AND "updated_at" < '2022-07-28 09:16:54.95459' );

查询计划显示使用tablename_pkey主键索引进行扫描，而非已存在的仅包含小部分记录的过滤索引tablename_updated_at_incl_id_partial_idx。该表为堆表而非聚簇表，为何会出现这种索引选用异常？

当前查询计划及索引信息 #

查询计划 #

QUERY PLAN                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=128.94..128.95 rows=1 width=16)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.57..64.47 rows=1 width=8)
           ->  Index Scan using tablename_pkey on tablename  (cost=0.57..416250679.26 rows=6513960 width=8)
                 Index Cond: (id IS NOT NULL)
                 Filter: ((updated_at >= '2022-07-24 09:08:05.926533'::timestamp without time zone) AND (updated_at < '2022-07-28 09:16:54.95459'::timestamp without time zone))
   InitPlan 2 (returns $1)
     ->  Limit  (cost=0.57..64.47 rows=1 width=8)
           ->  Index Scan Backward using tablename_pkey on tablename tablename_1  (cost=0.57..416250679.26 rows=6513960 width=8)
                 Index Cond: (id IS NOT NULL)
                 Filter: ((updated_at >= '2022-07-24 09:08:05.926533'::timestamp without time zone) AND (updated_at < '2022-07-28 09:16:54.95459'::timestamp without time zone))
(11 rows)

索引信息 #

Indexes:
    "tablename_pkey" PRIMARY KEY, btree (id)
    "tablename_updated_at_incl_id_partial_idx" btree (updated_at) INCLUDE (id) WHERE updated_at >= '2022-07-01 00:00:00'::timestamp without time zone

原因分析 #

• 索引结构无法直接满足极值查询需求
  过滤索引tablename_updated_at_incl_id_partial_idx是按updated_at排序的B树索引，虽然包含id字段，但无法直接通过该索引快速定位到id的最小值和最大值。如果使用这个索引，PostgreSQL需要遍历所有符合时间条件的记录来计算MIN/MAX，优化器预估这个成本远高于主键索引的扫描方式。

• 优化器成本预估偏差
  优化器选择主键索引的逻辑是：通过正向扫描主键索引找最小id，反向扫描找最大id，一旦找到第一条满足时间过滤条件的记录就停止（对应计划中的Limit 1）。但这里优化器的成本预估明显不准确——它预估要扫描6513960条记录才找到符合条件的，而实际过滤后的记录占比极低，导致这个路径的实际成本远高于使用过滤索引。

• 统计信息过时
  成本预估偏差的核心原因通常是表的统计信息不准确。PostgreSQL依赖统计信息判断索引的选择性和扫描成本，如果统计信息没有反映出updated_at在目标时间范围内的记录占比极低，优化器就会错误认为主键索引的路径成本更低。

• 过滤索引的设计不符合当前查询的最优需求
  当前的过滤索引排序键是updated_at，如果要让它能高效支持MIN(id)/MAX(id)查询，需要将id设为索引的排序键，同时包含updated_at字段，这样索引本身就是按id排序的，优化器可以直接扫描索引的首尾获取极值，同时利用过滤条件筛选时间范围。

解决建议 #

1. 更新统计信息：执行ANALYZE "public"."tablename";，让优化器获取准确的数据分布，重新评估索引成本。
2. 调整索引结构：将过滤索引修改为按id排序并包含updated_at，例如：

   CREATE INDEX tablename_id_incl_updated_at_partial_idx ON "public"."tablename" (id) INCLUDE (updated_at) 
   WHERE updated_at >= '2022-07-01 00:00:00'::timestamp without time zone;
   

3. 临时测试验证：可以通过SET enable_indexscan = off;强制优化器不使用主键索引，测试过滤索引的实际性能，但不建议在生产环境长期使用这类强制参数。

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