如何在使用子查询返回的日期时实现高效分区裁剪？

问题现象 #

我有一张按open_date范围分区的表tab，主键为(id, open_date)。当用子查询返回的日期做关联时，查询计划会扫描所有分区，无法触发分区裁剪；但直接指定日期范围条件时，就能正常筛选出目标分区。

关联子查询的执行计划（全分区扫描） #

EXPLAIN analyze SELECT * 
FROM calendar wc, tab t 
WHERE t.open_date =wc.wrk_day  
 AND wc.on_date ='2024-10-30'

对应的执行计划：

QUERY PLAN                                                                                                                                       |
-------------------------------------------------------------------------------------------------------------------------------------------------+
Hash JOIN  (cost=2.38..9602.67 ROWS=1099 width=85) (actual TIME=85.080..89.473 ROWS=41 loops=1)                                                  |
  Hash Cond: (t.open_date = wc.wrk_day)                                                                                                |
  ->  Append  (cost=0.00..8758.21 ROWS=316133 width=69) (actual TIME=0.590..54.404 ROWS=315957 loops=1)                                          |
        ->  Seq Scan ON t_2024_02_01 t_1  (cost=0.00..0.00 ROWS=1 width=596) (actual TIME=0.060..0.061 ROWS=0 loops=1)           |
        ->  Seq Scan ON t_2024_02_02 t_2  (cost=0.00..0.00 ROWS=1 width=596) (actual TIME=0.012..0.012 ROWS=0 loops=1)           |
        ->  Seq Scan ON t_2024_02_03 t_3  (cost=0.00..0.00 ROWS=1 width=596) (actual TIME=0.008..0.008 ROWS=0 loops=1)           |
        ->  Seq Scan ON t_2024_02_04 t_4  (cost=0.00..0.00 ROWS=1 width=596) (actual TIME=0.007..0.007 ROWS=0 loops=1)           |
...
...
->  Hash  (cost=2.37..2.37 ROWS=1 width=16) (actual TIME=0.021..0.021 ROWS=1 loops=1)                                                         |
        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                            |
        ->  INDEX Scan USING calendar_pkey ON calendar wc  (cost=0.15..2.37 ROWS=1 width=16) (actual TIME=0.008..0.009 ROWS=1 loops=1)|
              INDEX Cond: (on_date = '2024-10-30'::DATE)

直接指定日期范围的执行计划（分区裁剪生效） #

EXPLAIN analyze 
SELECT * 
FROM calendar wc, tab t 
WHERE t.open_date=wc.wrk_day  
 AND wc.on_date ='2024-10-30' AND t.open_date>='2024-10-29' AND t.open_date<'2024-11-01'

对应的执行计划：

QUERY PLAN                                                                                                                                      |
------------------------------------------------------------------------------------------------------------------------------------------------+
Hash JOIN  (cost=2.38..33.53 ROWS=3 width=85) (actual TIME=0.037..0.320 ROWS=41 loops=1)                                                        |
  Hash Cond: (t.open_date = wc.wrk_day)                                                                                               |
  ->  Append  (cost=0.00..28.90 ROWS=845 width=69) (actual TIME=0.012..0.225 ROWS=845 loops=1)                                                  |
        ->  Seq Scan ON t_2024_10_29 t_1  (cost=0.00..1.61 ROWS=41 width=69) (actual TIME=0.011..0.016 ROWS=41 loops=1)         |
              FILTER: ((open_date >= '2024-10-29'::DATE) AND (open_date < '2024-11-01'::DATE))                                            |
        ->  Seq Scan ON t_2024_10_30 t_2  (cost=0.00..1.87 ROWS=58 width=69) (actual TIME=0.005..0.011 ROWS=58 loops=1)         |
              FILTER: ((open_date >= '2024-10-29'::DATE) AND (open_date < '2024-11-01'::DATE))                                            |
        ->  Seq Scan ON t_2024_10_31 t_3  (cost=0.00..21.19 ROWS=746 width=69) (actual TIME=0.006..0.135 ROWS=746 loops=1)      |
              FILTER: ((open_date >= '2024-10-29'::DATE) AND (open_date < '2024-11-01'::DATE))                                            |
  ->  Hash  (cost=2.37..2.37 ROWS=1 width=16) (actual TIME=0.015..0.016 ROWS=1 loops=1)                                                         |
        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                            |
        ->  INDEX Scan USING calendar_pkey ON calendar wc  (cost=0.15..2.37 ROWS=1 width=16) (actual TIME=0.012..0.013 ROWS=1 loops=1)|
              INDEX Cond: (on_date = '2024-10-30'::DATE)                                                                                        |
Planning TIME: 0.408 ms                                                                                                                         |
Execution TIME: 0.356 ms                                                                                                                        |
 
15 ROW(s) fetched.

表结构 #

tab表的DDL：

CREATE TABLE usr.tab (
    id int8 NOT NULL,
    open_date date NOT NULL,
    system_date timestamp NULL,
    amount numeric(19, 2) NULL
    CONSTRAINT tab_pkey PRIMARY KEY (id, open_date)
)
PARTITION BY RANGE (open_date);
CREATE INDEX tab_on_banking_date ON ONLY usr.tab USING btree (open_date);

分区表示例：

CREATE TABLE usr.tab_2024_02 PARTITION OF usr.tab FOR VALUES FROM ('2024-02-01') TO ('2024-03-01')
PARTITION BY RANGE (open_date);
CREATE TABLE tab_2024_02_01 PARTITION OF tab_2024_02  FOR VALUES FROM ('2024-02-01') TO ('2024-02-02');

尝试无效的查询（仍全扫描） #

即使添加关联条件，依然无法触发分区裁剪：

explain analyze 
    select
    wc.prev_work_day d_f_w 
    from work_calendar wc 
    left join account_document ad on ad.banking_date = wc.prev_work_day
    where wc.on_date = '2024-10-30' 

执行计划：

QUERY PLAN                                                                                                                                                                                                |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Hash Right Join  (cost=2.38..7957.26 rows=1099 width=4) (actual time=71.377..75.512 rows=41 loops=1)                                                                                                      |
  Hash Cond: (ad.banking_date = wc.prev_work_day)                                                                                                                                                         |
  ->  Append  (cost=0.00..7112.81 rows=316133 width=4) (actual time=0.540..54.097 rows=315957 loops=1)                                                                                                    |
        ->  Seq Scan on account_document_2024_02_01 ad_1  (cost=0.00..0.00 rows=1 width=4) (actual time=0.046..0.046 rows=0 loops=1)                                                                      |
        ->  Seq Scan on account_document_2024_02_02 ad_2  (cost=0.00..0.00 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1)                                                                      |
        ->  Seq Scan on account_document_2024_02_03 ad_3  (cost=0.00..0.00 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)                                                                      |
        
etc...

解决方法 #

要让子查询返回的日期能触发分区裁剪，需要让优化器在查询规划阶段就能确定分区字段的取值范围，可通过以下几种方式实现：

1. 使用物化CTE提取日期范围 #

将子查询结果物化，同时显式提取日期的最小/最大值，帮助优化器识别分区条件：

WITH calendar_dates AS MATERIALIZED (
    SELECT wrk_day
    FROM calendar
    WHERE on_date = '2024-10-30'
), date_range AS (
    SELECT MIN(wrk_day) AS min_date, MAX(wrk_day) AS max_date
    FROM calendar_dates
)
SELECT *
FROM calendar_dates wc
JOIN tab t ON t.open_date = wc.wrk_day
WHERE t.open_date BETWEEN (SELECT min_date FROM date_range) AND (SELECT max_date FROM date_range);

2. 标量子查询直接匹配 #

如果子查询仅返回单个日期，可将其转为标量子查询，直接用于分区字段的等值条件：

SELECT *
FROM calendar wc
JOIN tab t ON t.open_date = wc.wrk_day
WHERE wc.on_date = '2024-10-30'
  AND t.open_date = (SELECT wrk_day FROM calendar WHERE on_date = '2024-10-30');

3. 封装为IMMUTABLE函数 #

将子查询逻辑封装成IMMUTABLE函数，让优化器能提前计算出结果，从而触发分区裁剪：

CREATE OR REPLACE FUNCTION get_target_work_date(p_on_date DATE)
RETURNS DATE AS $$
SELECT wrk_day FROM calendar WHERE on_date = p_on_date;
$$ LANGUAGE sql IMMUTABLE;

SELECT *
FROM tab t
WHERE t.open_date = get_target_work_date('2024-10-30');

4. 确认分区裁剪参数开启 #

确保数据库已启用分区裁剪参数（默认开启，可手动确认）：

SET enable_partition_pruning = on;

原理说明 #

分区裁剪需要优化器在查询规划阶段就能确定分区字段的明确取值范围，普通关联查询中，子查询的结果在规划阶段无法被预判，因此会扫描所有分区。通过物化CTE、标量子查询、函数封装等方式，能让优化器提前获取到日期的范围，从而触发分区裁剪逻辑。

内容的提问来源于stack exchange，提问作者Айрат