PostgreSQL如何在谓词中使用计算值触发索引扫描？

表结构与数据准备 #

首先创建测试表并插入数据：

create table foo (
  id integer primary key generated by default as identity,
  id_mod_7 int generated always as (id % 7) stored
);

create index on foo (id_mod_7, id);

insert into foo (id) select generate_series(1, 10000);

现象对比 #

1. 使用函数作为谓词：触发顺序扫描 #

当谓词条件为extract(dow from current_date)时，查询执行全表顺序扫描：

explain analyze 
select count(1) from foo where id_mod_7 = extract(dow from current_date);

执行计划：

QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Aggregate  (cost=245.12..245.13 rows=1 width=8) (actual time=7.218..7.219 rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..245.00 rows=50 width=0) (actual time=0.020..7.028 rows=1428 loops=1)
         Filter: ((id_mod_7)::numeric = EXTRACT(dow FROM CURRENT_DATE))
         Rows Removed by Filter: 8572
 Planning Time: 0.178 ms
 Execution Time: 7.281 ms

2. 使用字面常量作为谓词：触发仅索引扫描 #

当谓词条件为字面常量时，查询使用预先创建的索引执行仅索引扫描：

explain analyze 
select count(1) from foo where id_mod_7 = 6;

执行计划：

QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=48.84..48.85 rows=1 width=8) (actual time=0.321..0.322 rows=1 loops=1)
   ->  Index Only Scan using foo_id_mod_7_id_idx on foo  (cost=0.29..45.27 rows=1428 width=0) (actual time=0.022..0.214 rows=1428 loops=1)
         Index Cond: (id_mod_7 = 6)
         Heap Fetches: 0
 Planning Time: 0.106 ms
 Execution Time: 0.397 ms

3. 尝试CTE未生效 #

尝试用CTE缓存函数结果，仍触发顺序扫描：

explain analyze 
with param as (select extract(dow from current_date) as dow)
select count(1) from foo join param on id_mod_7 = dow;

执行计划：

QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Aggregate  (cost=245.12..245.13 rows=1 width=8) (actual time=5.830..5.831 rows=1 loops=1)
   ->  Seq Scan on foo  (cost=0.00..245.00 rows=50 width=0) (actual time=0.025..5.668 rows=1428 loops=1)
         Filter: ((id_mod_7)::numeric = EXTRACT(dow FROM CURRENT_DATE))
         Rows Removed by Filter: 8572
 Planning Time: 0.234 ms
 Execution Time: 5.894 ms

原因分析 #

1. 不稳定函数的规划限制 #

extract(dow from current_date)属于不稳定（volatile）函数，其返回值随时间动态变化（每天的星期数不同）。PostgreSQL查询规划器在生成执行计划阶段，无法提前确定这类函数的返回值，因此无法准确估算通过索引过滤后的匹配行数。执行计划中规划器估算匹配行数仅为50，远低于实际的1428，导致规划器判断顺序扫描的成本更低。

2. 隐式类型转换的干扰 #

id_mod_7是integer类型，而extract函数返回numeric类型，谓词中会触发id_mod_7到numeric的隐式转换。即使规划器能获取函数返回值，这种类型转换也会让索引无法直接匹配（索引存储的是int原始值，转换后的列无法利用索引）。

3. CTE的优化特性限制 #

PostgreSQL默认将CTE作为优化屏障，规划器不会将CTE内部的表达式逻辑外推到主查询中。同时，由于current_date是不稳定函数，即使放在CTE中，规划器仍会认为其返回值是动态的，无法将其视为固定常量来生成索引扫描计划。

可行的解决方式 #

若要触发索引扫描，需让规划器在执行计划阶段确定谓词值，或消除类型转换：

1. 显式转换类型并使用物化CTE：

explain analyze 
with param as materialized (select extract(dow from current_date)::int as dow)
select count(1) from foo join param on id_mod_7 = dow;

2. 预先计算常量值：
   使用PL/pgSQL将函数结果存入变量后执行查询，让规划器基于常量生成索引扫描计划：

do $$
declare
    dow_val int := extract(dow from current_date)::int;
begin
    execute format('select count(1) from foo where id_mod_7 = %L', dow_val);
end $$;

内容的提问来源于stack exchange，提问作者David A. Ventimiglia