如何让PostgreSQL在查询条件引用子查询时使用索引？

我有一张包含970万行数据的analytics_event表，需要查询created时间晚于指定值的记录。该表已有索引，但查询时却选择全表扫描（Seq Scan）而非使用索引。以下是简化后的对比查询：

不使用索引的慢查询（耗时约40秒） #

explain with batch_params as (
  select 
    (now() - '1 minute'::interval)::timestamptz as created
) select * from private.analytics_event
  where
    created > (select created from batch_params);

执行计划：

Seq Scan on analytics_event  (cost=0.01..1620760.63 rows=3251510 width=1025)
  Filter: (created > $0)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..0.01 rows=1 width=8)
          ->  Result  (cost=0.00..0.01 rows=1 width=8)

使用索引的快查询（耗时不足1毫秒） #

explain with batch_params as (
  select 
    (now() - '1 minute'::interval)::timestamptz as created
) select * from private.analytics_event
  where
    created > (now() - '1 minute'::interval)::timestamptz;

执行计划：

Index Scan using analytics_event_payload_event on analytics_event  (cost=0.44..7.33 rows=1 width=1025)
  Index Cond: (created > (now() - '00:01:00'::interval))

表与索引定义 #

CREATE TABLE private.analytics_event (
    id uuid NOT NULL,
    environment_id varchar NOT NULL,
    payload jsonb NULL,
    created timestamptz(3) DEFAULT now() NOT NULL,
    CONSTRAINT analytics_event_pkey PRIMARY KEY (environment_id, id)
);

CREATE INDEX analytics_event_payload_event ON private.analytics_event USING btree (created, ((payload ->> 'foobar'::text)));

背景说明 #

原始复杂查询中，batch_params是从特定游标表获取参数值。

已尝试的无效方案 #

• 在CTE表达式中添加limit 1，无效果。
• 将batch_params封装为STABLE函数，无效果，函数示例：

CREATE OR REPLACE FUNCTION get_created_date() RETURNS timestamptz AS $BODY$ 
  select created from cursor_table;
$BODY$ LANGUAGE SQL stable

• 将整个查询封装到带有声明变量created的函数中，虽能使用索引，但可维护性差，且无法用explain analyze分析查询。

补充信息 #

• PostgreSQL版本：15.6
• 索引大小：214M
• last_autoanalyze今日已执行
• 详细执行计划：

慢查询详细执行计划 #

explain (analyze, verbose, buffers, settings) ... <the slow query with sub-query>.

输出：

Seq Scan on private.analytics_event  (cost=0.01..1620760.63 rows=3251510 width=1025) (actual time=52571.409..52571.410 rows=0 loops=1)
  Output: analytics_event.id, analytics_event.environment_id, analytics_event.payload, analytics_event.created
  Filter: (analytics_event.created > $0)
  Rows Removed by Filter: 9798348
  Buffers: shared hit=473037 read=1025792
  I/O Timings: shared read=49404.088
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)
          Output: (now() - '00:01:00'::interval)
Settings: effective_cache_size = '7959688kB', jit = 'off', search_path = 'public, public, "$user"'
Query Identifier: -2998777079014550499
Planning Time: 0.087 ms
Execution Time: 52571.433 ms

快查询详细执行计划 #

explain (analyze, verbose, buffers, settings) ... <the fast/indexed query with inline condition>.

输出：

Index Scan using analytics_event_payload_event on private.analytics_event  (cost=0.44..7.33 rows=1 width=1025) (actual time=0.006..0.006 rows=0 loops=1)
  Output: id, environment_id, payload, created
  Index Cond: (analytics_event.created > (now() - '00:01:00'::interval))
  Buffers: shared hit=3
Settings: effective_cache_size = '7959688kB', jit = 'off', search_path = 'public, public, "$user"'
Query Identifier: -1698698247486258523
Planning:
  Buffers: shared read=5
  I/O Timings: shared read=3.082
Planning Time: 3.208 ms
Execution Time: 0.023 ms

优化方案 #

方法1：使用物化CTE（MATERIALIZED CTE） #

将CTE标记为MATERIALIZED，强制PostgreSQL提前计算参数值，让优化器能基于实际值评估索引扫描成本：

EXPLAIN
WITH batch_params AS MATERIALIZED (
  SELECT (now() - '1 minute'::interval)::timestamptz(3) AS created
)
SELECT * FROM private.analytics_event
WHERE created > (SELECT created FROM batch_params);

注意：显式转换为与表中created字段一致的timestamptz(3)类型，避免隐式类型转换导致索引失效

方法2：使用交叉连接（CROSS JOIN）固化参数 #

通过交叉连接将参数子查询与主表关联，让优化器先解析参数值再执行主查询：

EXPLAIN
SELECT ae.*
FROM private.analytics_event ae
CROSS JOIN (
  SELECT (now() - '1 minute'::interval)::timestamptz(3) AS created
  -- 原始场景替换为：SELECT created FROM cursor_table
) params
WHERE ae.created > params.created;

这种方式既保留参数的动态性，又能让优化器选择索引扫描。

方法3：使用会话变量传递参数（适合批量场景） #

先将参数值存入会话变量，再执行查询，确保优化器能获取到明确的参数值：

-- 第一步：获取参数并存入会话变量
SELECT created INTO @created_threshold FROM cursor_table;
-- 第二步：执行查询
SELECT * FROM private.analytics_event WHERE created > @created_threshold;

或使用PostgreSQL自定义会话变量：

SET LOCAL app.created_threshold = (SELECT created FROM cursor_table);
SELECT * FROM private.analytics_event 
WHERE created > current_setting('app.created_threshold')::timestamptz(3);

问题根源 #

PostgreSQL优化器处理CTE或函数返回的参数时，会将其视为未知常量（执行计划中的$0），无法预估匹配行数，因此默认选择全表扫描。而直接写在条件中的now()表达式属于可优化的常量表达式，优化器能计算出近似值并评估索引扫描的成本，从而选择更优计划。

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