PostgreSQL哈希分区表查询扫描全部分区，如何仅扫描相关分区？

当前查询无法触发分区裁剪的核心原因是：包含DELETE/INSERT操作的可写CTE属于优化栅栏，PostgreSQL查询规划器无法在规划阶段提前推导ids_to_process返回的asset_id取值，因此无法对基于asset_id的哈希分区做裁剪，只能扫描全部分区。
同时原有查询中使用两次ANY子查询的写法，不仅存在逻辑隐患（可能匹配到id和asset_id不属于同一条记录的无效行），也会进一步阻碍规划器做值推导。

方法1：提前物化待处理集合（最稳妥，兼容所有支持分区的PG版本） #

拆分原有单SQL逻辑，先完成数据迁移并把待处理的id、asset_id存入临时表，更新统计信息后再做分区表查询：

-- 完成数据迁移并将待处理记录存入临时表
CREATE TEMP TABLE tmp_ids_to_process AS
WITH deleted_unprocessed_data AS (
    DELETE FROM foundation.unprocessed_ids d
    WHERE id = ANY(SELECT id FROM foundation.unprocessed_ids up ORDER BY up.asset_id, up.data_point_timestamp ASC LIMIT 1000)
    RETURNING id, asset_id, data_point_timestamp
)
INSERT INTO foundation.processed_ids SELECT * FROM deleted_unprocessed_data
RETURNING id, asset_id;

-- 更新临时表统计信息，帮助规划器做值推导
ANALYZE tmp_ids_to_process;

-- 执行最终查询，此时可以触发分区裁剪
SELECT fd.asset_id,
    MIN(fd.data_point_timestamp) AS minborder,
    MAX(fd.data_point_timestamp) AS maxborder
FROM foundation.DATA fd
INNER JOIN tmp_ids_to_process tip 
    ON fd.id = tip.id 
    AND fd.asset_id = tip.asset_id
GROUP BY fd.asset_id;

方法2：单SQL优化（适配PG12+版本） #

如果必须使用单条SQL执行，可以将ANY子查询替换为IN+去重的写法，尽可能让规划器拿到明确的asset_id集合：

WITH deleted_unprocessed_data AS (
    DELETE FROM foundation.unprocessed_ids d
    WHERE id = ANY(SELECT id FROM foundation.unprocessed_ids up ORDER BY up.asset_id, up.data_point_timestamp ASC LIMIT 1000)
    RETURNING id, asset_id, data_point_timestamp
),
ids_to_process AS (
    INSERT INTO foundation.processed_ids SELECT * FROM deleted_unprocessed_data
    RETURNING id, asset_id
),
distinct_assets AS (
    SELECT DISTINCT asset_id FROM ids_to_process
)
SELECT fd.asset_id,
    MIN(fd.data_point_timestamp) AS minborder,
    MAX(fd.data_point_timestamp) AS maxborder
FROM foundation.DATA fd
WHERE 
    fd.asset_id IN (SELECT asset_id FROM distinct_assets)
    AND EXISTS (SELECT 1 FROM ids_to_process tip WHERE tip.id = fd.id AND tip.asset_id = fd.asset_id)
GROUP BY fd.asset_id;

执行查询前开启执行计划输出：

EXPLAIN ANALYZE [你的查询语句];

查看Append节点下的扫描分区数量，确认只扫描和待处理asset_id匹配的分区即可。 #

内容的提问来源于stack exchange，提问作者C.Tom