PostgreSQL：优化大分区表UPDATE语句，避免全分区扫描

我有一个按row_insert_time字段做每日分区的events表，表结构及分区创建语句如下：

CREATE TABLE events
(
    record_id       BIGSERIAL NOT NULL,
    events          TEXT,
    status          int DEFAULT 0 NOT NULL,
    insert_time     bigint DEFAULT date_part('epoch'::text, timezone('utc'::text, now())),
    event_source    varchar(255),
    row_insert_time timestamp not null default current_timestamp
) PARTITION BY RANGE (row_insert_time);


create table IF NOT EXISTS events_2023_12_21 PARTITION OF events FOR VALUES FROM ('2023-12-21 00:00:00') TO ('2023-12-22 00:00:00');
create table IF NOT EXISTS events_2023_12_22 PARTITION OF events FOR VALUES FROM ('2023-12-22 00:00:00') TO ('2023-12-23 00:00:00');
create table IF NOT EXISTS events_2023_12_23 PARTITION OF events FOR VALUES FROM ('2023-12-23 00:00:00') TO ('2023-12-24 00:00:00');
create table IF NOT EXISTS events_2023_12_24 PARTITION OF events FOR VALUES FROM ('2023-12-24 00:00:00') TO ('2023-12-25 00:00:00');
create table IF NOT EXISTS events_2023_12_25 PARTITION OF events FOR VALUES FROM ('2023-12-25 00:00:00') TO ('2023-12-26 00:00:00');
create table IF NOT EXISTS events_2023_12_26 PARTITION OF events FOR VALUES FROM ('2023-12-26 00:00:00') TO ('2023-12-27 00:00:00');

执行以下UPDATE语句的执行计划时，发现它会扫描所有分区：

explain UPDATE events SET status = 1 WHERE row_insert_time > NOW() - INTERVAL '1 HOUR' and row_insert_time < NOW() and record_id = 1282695;

我原本以为WHERE子句中的row_insert_time条件会限制扫描的分区数量，请问如何优化该UPDATE语句，避免扫描所有分区？

1. 提前固化时间范围值，消除动态函数影响 #

PostgreSQL的分区裁剪依赖常量或可提前确定的范围值，NOW()是动态函数，执行计划生成时无法锁定具体值，导致数据库无法判断符合条件的分区，只能全量扫描。

可以先计算出时间范围的具体值，用常量形式代入语句：

-- 通过CTE提前计算时间范围
WITH time_range AS (
    SELECT 
        (NOW() - INTERVAL '1 HOUR')::timestamp AS start_time,
        NOW()::timestamp AS end_time
)
UPDATE events 
SET status = 1 
WHERE row_insert_time > (SELECT start_time FROM time_range) 
  AND row_insert_time < (SELECT end_time FROM time_range) 
  AND record_id = 1282695;

如果业务允许提前确定时间，也可以直接写静态时间值：

UPDATE events 
SET status = 1 
WHERE row_insert_time > '2023-12-26 14:00:00' 
  AND row_insert_time < '2023-12-26 15:00:00' 
  AND record_id = 1282695;

2. 给分区表添加全局主键或分区主键 #

record_id是BIGSERIAL，理论上全局唯一，但分区表默认不会自动继承全局主键。给主表定义全局复合主键（PostgreSQL 11+支持），或给每个分区单独添加主键，数据库可以通过record_id快速定位记录所在分区，结合时间条件进一步裁剪范围：

-- 给主表添加全局复合主键（需包含分区键row_insert_time）
ALTER TABLE events ADD PRIMARY KEY (record_id, row_insert_time);

-- 或给每个分区单独添加主键
ALTER TABLE events_2023_12_21 ADD PRIMARY KEY (record_id);
ALTER TABLE events_2023_12_22 ADD PRIMARY KEY (record_id);
-- 其他分区同理执行

添加主键后，数据库可利用主键索引快速定位记录，避免全分区扫描。

3. 直接指定目标分区（适用于明确记录所在分区的场景） #

如果能通过业务逻辑确定record_id=1282695所在的分区，直接对分区表执行更新：

UPDATE events_2023_12_26 
SET status = 1 
WHERE row_insert_time > NOW() - INTERVAL '1 HOUR' 
  AND row_insert_time < NOW() 
  AND record_id = 1282695;

这种方式最直接，但需要业务层能准确判断分区，灵活性较低。

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