now()導致分區不能剪枝問題優化
更新時間 2025-02-05 09:36:59
最近更新時間: 2025-02-05 09:36:59
分享文章
本文為您介紹now()導致分區不能剪枝問題優化案例。
now()函數是獲取當前時間，當SQL語句中分區表時間傳入now()時，由于SQL下推DN節點執行，優化器在CN節點生成執行計劃時，無法進行分區剪枝，導致掃描了所有分區，導致SQL性能下降，SQL并行執行時會引起嚴重的分區表鎖沖突。
針對此問題，建議傳入具體值代替now()，或定義一個穩定函數代替now()。
以下是關于該場景的測試情況：
-
準備測試數據：
teledb=# 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()時SQL執行計劃：
可以看到，SQL被下推到DN節點執行，優化器沒有剪枝，掃了所有分區。
teledb=# 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) -
自定義一個穩定函數
teledb=# create or replace function get_current_timestamp() returns timestamp as $$ begin return current_timestamp; end; $$ language plpgsql IMMUTABLE; CREATE FUNCTION -
用自定義的穩定函數代替now()后的執行計劃：
可以看到，SQL下推DN節點執行后，優化器成功剪枝，只掃描了2個分區。
teledb=# 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)
