PostgreSQL timestamp索引为何未在大于等于查询中生效？

我有一个PostgreSQL表public."Impressions"，包含at（带时区的时间戳，即timestamptz）字段，并为该字段创建了B-tree索引at_idx。执行查询Select * from public."Impressions" I where I."at" >= '2024-01-01'时，执行计划显示为全表扫描（Seq Scan）；但执行等值查询Select * from public."Impressions" I where I."at" = '2024-01-01'时，会使用at_idx进行索引扫描。我知道B-tree索引支持大于等于这类比较运算符，请问我遗漏了什么？

PostgreSQL优化器选择全表扫描而非索引扫描，本质是基于成本估算的决策，以下是最常见的几个原因：

1. 查询返回数据占比过高 #

如果I."at" >= '2024-01-01'会返回表中大部分数据（通常阈值在20%-30%左右，具体取决于表结构和硬件），优化器会认为全表扫描的IO成本更低——因为索引扫描需要先查索引再回表读取实际数据，两次IO的总开销反而比直接扫表更大。

可以用以下语句估算返回行数占比：

SELECT 
  (COUNT(*) FILTER (WHERE "at" >= '2024-01-01')::numeric / COUNT(*)) * 100 AS percentage
FROM public."Impressions";

如果占比确实很高，这属于正常优化行为，无需调整；如果业务上必须用索引，可以尝试添加排序（B-tree索引是有序的，优化器可能倾向于用索引避免额外排序）：

SELECT * FROM public."Impressions" I 
WHERE I."at" >= '2024-01-01'
ORDER BY I."at";

2. 统计信息过时 #

PostgreSQL依赖表的统计信息来估算查询返回行数，如果统计信息不是最新的，优化器可能误判范围查询的行数，从而选择错误的执行计划。

执行以下语句更新统计信息：

ANALYZE public."Impressions";

更新后重新查看执行计划，确认是否使用索引。

3. 时区转换导致的隐式类型转换 #

你的at字段是timestamptz（带时区的时间戳），而查询条件中的'2024-01-01'是不带时区的字符串，PostgreSQL会使用当前会话的时区将其转换为timestamptz。如果转换后的时间范围实际涵盖了大部分数据（比如会话时区是UTC-8，'2024-01-01'会被转换为2024-01-01 00:00:00-08，对应的UTC时间是2024-01-01 08:00:00，若表中大部分数据在这个时间之后），就会触发全表扫描。

可以明确指定时区来避免歧义：

SELECT * FROM public."Impressions" I 
WHERE I."at" >= '2024-01-01 00:00:00+00'::timestamptz;

4. 索引有效性问题 #

虽然你说索引已创建，但还是可以确认索引状态：

• 用\d public."Impressions"（psql命令）查看索引列表，确认at_idx存在且关联at字段；
• 若怀疑索引损坏，可重建索引：

REINDEX INDEX public.at_idx;

5. 优化器参数影响 #

个别情况下，优化器的成本参数设置可能影响决策：

• random_page_cost：默认值为4，表示随机IO的成本是顺序IO的4倍。如果你的存储是SSD，可以适当降低这个值（比如设为1.1），让优化器更倾向于索引扫描：

SET random_page_cost = 1.1; -- 会话级生效
-- 或者全局生效（需重启PostgreSQL）
ALTER SYSTEM SET random_page_cost = 1.1;

注意：这个调整要谨慎，仅在确认硬件适配的情况下进行。

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