如何基于当前时间使用Index Scan而非Seq Scan查询时间范围

问题背景 #

当appointment表的starttime列存在索引时，硬编码时间值的查询能正常使用Index Scan：

EXPLAIN 
SELECT * FROM appointment WHERE appointment.starttime BETWEEN 1620133200 AND 1620207000

但使用now()动态生成时间范围时，无论直接写条件还是用CTE，查询都会退化为Seq Scan（全表扫描），比如：

直接动态计算写法 #

EXPLAIN 
SELECT * FROM appointment 
WHERE appointment.starttime BETWEEN 
extract(epoch from now()) - 3600 AND
extract(epoch from now()) + 3600

CTE写法 #

EXPLAIN
WITH range AS (
  SELECT
    extract(epoch from now()) - 3600  AS _from,
    extract(epoch from now()) + 3600  AS _to
)
SELECT
  *
FROM appointment
CROSS JOIN range
WHERE appointment.starttime BETWEEN _from AND _to

数十万条数据的全表扫描会严重影响查询性能，尤其是高频并行执行的场景。

可行解决方案 #

1. 用子查询包装时间计算，让PostgreSQL识别为常量 #

把动态时间计算放到独立子查询中，PostgreSQL会将其视为查询执行期间的常量，从而触发索引扫描：

EXPLAIN
SELECT * FROM appointment
WHERE starttime BETWEEN
  (SELECT extract(epoch from now()) - 3600) AND
  (SELECT extract(epoch from now()) + 3600)

2. 封装为STABLE类型函数 #

创建一个标记为STABLE的函数，因为now()在单个事务内是稳定不变的，PostgreSQL会信任函数返回值的稳定性，进而使用索引：

CREATE OR REPLACE FUNCTION get_time_range()
RETURNS TABLE(_from bigint, _to bigint)
LANGUAGE sql STABLE AS $$
SELECT
  extract(epoch from now()) - 3600::bigint,
  extract(epoch from now()) + 3600::bigint;
$$;

调用函数查询：

EXPLAIN
SELECT a.*
FROM appointment a, get_time_range() r
WHERE a.starttime BETWEEN r._from AND r._to;

3. 应用层提前计算时间参数 #

在应用代码中先算出时间范围的具体数值，再作为参数传入SQL，这种方式和硬编码值效果完全一致，必然走索引：

-- 示例：应用层计算后传入参数$1和$2
EXPLAIN
SELECT * FROM appointment
WHERE starttime BETWEEN $1 AND $2;

4. 强制使用索引（仅作为最后手段） #

如果以上方法都不生效，可以通过指定索引或配合排序强制触发索引扫描，但这种方式不够灵活，数据分布变化时可能导致性能问题：

EXPLAIN
SELECT * FROM appointment
WHERE starttime BETWEEN extract(epoch from now()) - 3600 AND extract(epoch from now()) + 3600
ORDER BY starttime; -- 利用索引排序特性触发扫描

-- 或者直接指定索引名称
-- EXPLAIN
-- SELECT * FROM appointment INDEX idx_appointment_starttime
-- WHERE starttime BETWEEN extract(epoch from now()) - 3600 AND extract(epoch from now()) + 3600;

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