PostgreSQL使用COALESCE函数时索引被忽略问题排查

我有一张约400万行的members表，表结构如下：

CREATE TABLE members (
  id                INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  created_at        TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at        TIMESTAMP WITH TIME ZONE,
  -- other columns...
);

我使用以下查询提取最近更新的行：

SELECT *
FROM members
WHERE COALESCE(updated_at, created_at) > current_timestamp - interval '24 hours'

该查询速度很慢，因此我创建了对应的索引，但PostgreSQL并未使用它：

CREATE INDEX members_updated_or_created_at ON members(COALESCE(updated_at, created_at));

执行计划如下：

Seq Scan on members  (cost=0.00..171792.01 rows=1326991 width=1826) (actual time=62.663..22064.805 rows=1 loops=1)
  Filter: (COALESCE(updated_at, created_at) > (CURRENT_TIMESTAMP - '48:00:00'::interval))
  Rows Removed by Filter: 3980971
Planning Time: 0.123 ms
JIT:
  Functions: 2
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 7.481 ms, Inlining 0.000 ms, Optimization 8.067 ms, Emission 35.308 ms, Total 50.857 ms
Execution Time: 22072.906 ms

我不理解为何执行全表扫描而非索引扫描，尝试减少查询字段、添加limit也无效果。

编辑补充：
似乎索引未被使用是因为我查询了索引中不存在的多列（SELECT *）。
我尝试单独使用updated_at列测试：仅查询updated_at列时会触发Index Only Scan，但查询其他列则不会。
但我不理解的是，为何使用COALESCE函数时无法获得相同行为？

以下查询触发全表扫描：

SELECT coalesce(updated_at, created_at)
FROM members
WHERE coalesce(updated_at, created_at) > current_timestamp - interval '7 days';

以下查询触发Index Only Scan（基于updated_at列的索引）：

SELECT updated_at
FROM members
WHERE updated_at > current_timestamp - interval '7 days';

恳请解答该问题的原因。

1. 统计信息失真导致优化器判断失误 #

PostgreSQL查询优化器依赖表的统计数据选择执行计划。从执行计划可见，优化器预估返回132万行，但实际仅返回1行，说明统计信息严重不准。优化器会认为返回数据占表的比例极高（通常超过30%时），全表扫描的成本比索引扫描更低，因此选择了全表扫描。

2. 函数索引无法触发Index Only Scan的核心原因 #

普通单列索引（如updated_at的索引）能触发Index Only Scan，是因为PostgreSQL可以通过**可见性映射（Visibility Map）**快速判断数据页是否所有行都对当前事务可见，无需回表验证。但对于COALESCE(updated_at, created_at)这类函数索引：

• 函数索引存储的是计算后的结果，无法直接关联原表数据页的可见性映射信息。优化器无法确定索引中的数据是否全部可见，必须回表验证，因此不会选择Index Only Scan。
• 即使仅查询函数计算结果，优化器也会因为无法确认可见性，再加上统计信息不准的影响，最终选择全表扫描。

3. 验证与解决方向 #

先手动更新表的统计信息，再查看执行计划是否变化：

ANALYZE members;

更新后如果预估行数接近实际值，优化器会重新评估成本，大概率会选择使用函数索引。

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