分区表now()剪枝问题
最近更新时间: 2025-02-18 16:02:00
postgres=# create table t_time_range
(f1 bigint, f2 timestamp ,f3 bigint)
partition by range (f2) begin (timestamp without time zone '2021-06-01 0:0:0')
step (interval '1 month')
partitions (12) distribute by shard(f1)
to group default_group;
CREATE TABLE- 使用原生的now()函数是剪不了枝。
postgres=# explain select * from t_time_range where f2<now();
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001, dn002, dn003
-> Append (cost=0.00..0.00 rows=0 width=0)
-> Seq Scan on t_time_range (partition sequence: 0, name: t_time_range_part_0) (cost=0.00..2.17 rows=30 width=24)
Filter: (f2 < now())
-> Seq Scan on t_time_range (partition sequence: 1, name: t_time_range_part_1) (cost=0.00..2.17 rows=30 width=24)
Filter: (f2 < now())
-> Seq Scan on t_time_range (partition sequence: 2, name: t_time_range_part_2) (cost=0.00..2.17 rows=30 width=24)
Filter: (f2 < now())
-> Seq Scan on t_time_range (partition sequence: 3, name: t_time_range_part_3) (cost=0.00..2.17 rows=30 width=24)
Filter: (f2 < now())
-> Seq Scan on t_time_range (partition sequence: 4, name: t_time_range_part_4) (cost=0.00..2.17 rows=30 width=24)
Filter: (f2 < now())
-> Seq Scan on t_time_range (partition sequence: 5, name: t_time_range_part_5) (cost=0.00..2.17 rows=30 width=24)
Filter: (f2 < now())
-> Seq Scan on t_time_range (partition sequence: 6, name: t_time_range_part_6) (cost=0.00..2.17 rows=30 width=24)
Filter: (f2 < now())
-> Seq Scan on t_time_range (partition sequence: 7, name: t_time_range_part_7) (cost=0.00..2.17 rows=30 width=24)
Filter: (f2 < now())
-> Seq Scan on t_time_range (partition sequence: 8, name: t_time_range_part_8) (cost=0.00..2.17 rows=30 width=24)
Filter: (f2 < now())
-> Seq Scan on t_time_range (partition sequence: 9, name: t_time_range_part_9) (cost=0.00..2.17 rows=30 width=24)
Filter: (f2 < now())
-> Seq Scan on t_time_range (partition sequence: 10, name: t_time_range_part_10) (cost=0.00..2.17 rows=30 width=24)
Filter: (f2 < now())
-> Seq Scan on t_time_range (partition sequence: 11, name: t_time_range_part_11) (cost=0.00..2.17 rows=30 width=24)
Filter: (f2 < now())
(27 rows)- 定义一个稳定函数。
postgres=# create or replace function get_current_timestamp() returns timestamp as
$$
begin
return current_timestamp;
end;
$$
language plpgsql IMMUTABLE;
CREATE FUNCTION
postgres=# explain select * from t_time_range where f2<get_current_timestamp();
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001, dn002, dn003
-> Append (cost=0.00..0.00 rows=0 width=0)
-> Seq Scan on t_time_range (partition sequence: 0, name: t_time_range_part_0) (cost=0.00..11.69 rows=178 width=24)
Filter: (f2 < '2021-07-12 11:31:33.639119'::timestamp without time zone)
-> Seq Scan on t_time_range (partition sequence: 1, name: t_time_range_part_1) (cost=0.00..11.69 rows=178 width=24)
Filter: (f2 < '2021-07-12 11:31:33.639119'::timestamp without time zone)
(7 rows)
postgres=# 这样优化后剪枝就正常了。