如何让PostgreSQL为status=ANY多值查询使用索引？

问题背景 #

我有一张约3500万行的表，需定期查找并删除已处理的记录。该表包含14种合法状态值，其中10种为已处理状态。表结构如下：

id uuid default uuid_generate_v4() not null primary key,
fk_id uuid not null references fk_table,
-- ... 其他字段
created_date timestamptz default now() not null,
status varchar(128) not null

status的取值为a,b,c,d,e,f,g,h,i,j,k,l,m,n（共14个）。已创建(status, created_date)联合索引，但执行如下查询时：

select id from table
where created_date < 'somedate'
and status = ANY('{a,b,c,d,e,f,g,h,i,j}') -- 前10种已处理状态

查询规划器始终采用全表扫描（seq_scan）而非索引。

解决技巧 #

1. 调整联合索引顺序 #

当前(status, created_date)索引的前缀是status，而你要匹配的status占比高达71%（10/14），PostgreSQL会认为扫描大部分索引页的成本比全表扫描更高。可以将索引顺序改为(created_date, status)：

CREATE INDEX idx_table_created_date_status ON "table" (created_date, status);

这种索引先通过created_date < 'somedate'过滤出时间范围内的小批量数据，再匹配status，当时间范围足够窄时，规划器会优先选择索引扫描。

2. 更新统计信息 #

如果表的统计信息过时，规划器会误判数据分布。执行以下命令更新统计：

ANALYZE "table";

对于数据量极大的表，可提高status列的统计样本量，让规划器更准确判断数据分布：

ALTER TABLE "table" ALTER COLUMN status SET STATISTICS 1000;
ANALYZE "table";

3. 拆分查询分批次处理 #

将匹配10种status的大查询拆分为单个status的小查询，比如：

-- 分批次删除，每次处理一种状态
DELETE FROM "table" WHERE created_date < 'somedate' AND status = 'a';
DELETE FROM "table" WHERE created_date < 'somedate' AND status = 'b';
-- ... 依次处理剩余8种已处理状态

单个status仅占总数据的约7%，规划器会更倾向于使用(status, created_date)索引，同时分批次操作也能避免大事务锁表，降低数据库负载。

4. 强制使用索引（临时方案） #

如果确认索引更高效但规划器误判，可使用索引提示强制走索引：

SELECT id FROM "table"
WHERE created_date < 'somedate'
AND status = ANY('{a,b,c,d,e,f,g,h,i,j}')
INDEX idx_table_status_created_date; -- 替换为你的原索引名

注意：这是临时应急方案，长期依赖规划器自动判断更稳妥，避免因数据分布变化导致索引效率下降。

5. 改用范围查询（若status可排序） #

如果status的取值是连续可排序的（如a到j的字符串顺序连续），将ANY替换为范围查询：

SELECT id FROM "table"
WHERE created_date < 'somedate'
AND status BETWEEN 'a' AND 'j';

这种写法更符合联合索引(status, created_date)的前缀匹配逻辑，规划器更容易识别并使用索引。

6. 检查索引可用性 #

确认索引未损坏且被正确识别：

SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'table'; -- 替换为你的表名

若idx_scan为0，说明索引从未被使用，需排查索引是否匹配查询条件，或规划器是否认为其效率低于全表扫描。

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