分区键是带时区的日期如何裁剪
分区键是带时区的日期如何裁剪
分区键是带时区的日期如何裁剪
分区键是带时区的日期如何裁剪
有这样一张表：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
pagila=# \d payment;
Partitioned table "public.payment"
Column | Type | Collation | Nullable | Default | Storage |
--------------+--------------------------+-----------+----------+---------------------------------------------+---------+
payment_id | integer | | not null | nextval('payment_payment_id_seq'::regclass) | plain |
customer_id | integer | | not null | | plain |
staff_id | integer | | not null | | plain |
rental_id | integer | | not null | | plain |
amount | numeric(5,2) | | not null | | main |
payment_date | timestamp with time zone | | not null | | plain |
Partition key: RANGE (payment_date)
Indexes:
"payment_pkey" PRIMARY KEY, btree (payment_date, payment_id)
Not-null constraints:
"payment_payment_id_not_null" NOT NULL "payment_id"
"payment_customer_id_not_null" NOT NULL "customer_id"
"payment_staff_id_not_null" NOT NULL "staff_id"
"payment_rental_id_not_null" NOT NULL "rental_id"
"payment_amount_not_null" NOT NULL "amount"
"payment_payment_date_not_null" NOT NULL "payment_date"
Partitions: payment_p2022_01 FOR VALUES FROM ('2022-01-01 08:00:00+08') TO ('2022-02-01 08:00:00+08'),
payment_p2022_02 FOR VALUES FROM ('2022-02-01 08:00:00+08') TO ('2022-03-01 08:00:00+08'),
payment_p2022_03 FOR VALUES FROM ('2022-03-01 08:00:00+08') TO ('2022-04-01 08:00:00+08'),
payment_p2022_04 FOR VALUES FROM ('2022-04-01 08:00:00+08') TO ('2022-05-01 08:00:00+08'),
payment_p2022_05 FOR VALUES FROM ('2022-05-01 08:00:00+08') TO ('2022-06-01 08:00:00+08'),
payment_p2022_06 FOR VALUES FROM ('2022-06-01 08:00:00+08') TO ('2022-07-01 08:00:00+08'),
payment_p2022_07 FOR VALUES FROM ('2022-07-01 08:00:00+08') TO ('2022-08-01 08:00:00+08');
执行如下查询，查看执行计划：
1
2
3
4
5
6
7
8
9
10
11
pagila=# explain select * from payment where payment_date between '2022-04-01 00:00:00' and '2022-04-30 23:59:59';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Append (cost=4.60..95.78 rows=2548 width=30)
-> Bitmap Heap Scan on payment_p2022_03 payment_1 (cost=4.60..25.83 rows=31 width=30)
Recheck Cond: ((payment_date >= '2022-04-01 00:00:00+08'::timestamp with time zone) AND (payment_date <= '2022-04-30 23:59:59+08'::timestamp with time zone))
-> Bitmap Index Scan on payment_p2022_03_pkey (cost=0.00..4.59 rows=31 width=0)
Index Cond: ((payment_date >= '2022-04-01 00:00:00+08'::timestamp with time zone) AND (payment_date <= '2022-04-30 23:59:59+08'::timestamp with time zone))
-> Seq Scan on payment_p2022_04 payment_2 (cost=0.00..57.20 rows=2517 width=30)
Filter: ((payment_date >= '2022-04-01 00:00:00+08'::timestamp with time zone) AND (payment_date <= '2022-04-30 23:59:59+08'::timestamp with time zone))
(7 rows)
如果我们再过滤条件指定时区：
1
2
3
4
5
6
pagila=# explain select * from payment where payment_date between '2022-04-01 00:00:00+00' and '2022-04-30 23:59:59+00';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Seq Scan on payment_p2022_04 payment (cost=0.00..57.20 rows=2547 width=30)
Filter: ((payment_date >= '2022-04-01 08:00:00+08'::timestamp with time zone) AND (payment_date <= '2022-05-01 07:59:59+08'::timestamp with time zone))
(2 rows)
[!IMPORTANT]
✔ 1. timestamptz 的本质
🔥 永远存 UTC 🔥 输入时转换 🔥 输出按 session 时区显示
✔ 2. 执行计划里的时间
🔥 不是你写的原始字面量 🔥 而是“标准化后的值 + session 时区展示”
✔ 3. 分区裁剪判断依据
❗看的是“时间点是否落在分区范围”，不是字符串。
本文由作者按照
CC BY 4.0
进行授权