无分区键WHERE子句的PostgreSQL分区表查询优化问题

问题背景 #

我们正尝试优化一张按created_at范围分区的PostgreSQL表的查询，当前查询存在两个问题：

• 尽管指定了ORDER BY created_at DESC，执行计划显示仍按时间正序扫描分区；
• 因WHERE子句未包含分区键created_at，查询会扫描所有预创建的未来空分区，即便已获取足够满足LIMIT的记录。

查询语句 #

SELECT col1, col2
FROM partitioned_table
WHERE profile_id = '00000000-0000-0000-0000-000000000000'
AND product_id = 'product_a'
ORDER BY created_at DESC
LIMIT 500;

父表/分区表定义 #

CREATE TABLE public.partitioned_table (
    trade_id integer NOT NULL,
    product_id character varying NOT NULL,
    settled boolean DEFAULT false NOT NULL,
    user_id public.mongo_id NOT NULL,
    profile_id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL
)
PARTITION BY RANGE (created_at);

索引信息 #

该索引定义在分区表上，后续创建的分区自动继承相同索引：

CREATE INDEX partitioned_profile_id_product_id_trade_id_idx ON ONLY public.partitioned_table USING btree (profile_id, product_id, trade_id) INCLUDE (created_at);

环境信息 #

• 分区策略：每个分区存储一天的数据，约1200万行；
• PostgreSQL版本：AWS RDS 14.5。

疑问解答 #

1. 能否改为按时间倒序扫描分区？ #

可以实现，需要调整索引和引导优化器逻辑：

• 重构索引：当前索引无法直接支持created_at倒序的高效扫描，建议创建以profile_id, product_id为前缀、created_at DESC为排序字段的覆盖索引，让每个分区内的数据按最新时间排列：

  CREATE INDEX partitioned_profile_product_created_idx ON ONLY public.partitioned_table USING btree (profile_id, product_id, created_at DESC) INCLUDE (col1, col2, trade_id);
  

• 引导分区扫描顺序：开启enable_partitionwise_sort参数（默认关闭），让优化器优先对单个分区内的数据排序，结合新索引，优化器会倾向于从最新的分区开始扫描。如果业务允许，还可以在WHERE子句中添加created_at <= CURRENT_TIMESTAMP，进一步帮助优化器锁定需要扫描的分区范围。

2. 如何让查询在获取足够LIMIT记录后停止扫描未来空分区？ #

有几种可行方案：

• 添加业务合理的时间范围过滤：如果业务无需查询未来数据，直接在WHERE子句中加入created_at <= CURRENT_TIMESTAMP，优化器会自动排除所有大于当前时间的未来空分区，无需扫描。
• 控制预创建未来分区的数量：如果必须预创建分区，只保留短周期的未来分区（比如仅预创建7天内的），减少无意义的扫描开销。
• 升级PostgreSQL版本：PostgreSQL 15及以上版本优化了分区扫描的终止逻辑，当查询获取到满足LIMIT的记录后，会自动停止扫描剩余分区，若有升级计划可考虑此方案。
• 动态筛选有数据的分区：通过查询pg_stat_user_tables获取有数据的分区列表，再生成仅扫描这些分区的动态SQL，不过这种方式实现复杂度较高，需结合业务场景调整。

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