PostgreSQL分区表的分类
PostgreSQL10之前使用继承式分区，这里我们不再讨论，目前LightDB使用的是声明式分区，不再需要繁琐的触发器和继承
直奔主题创建测试表并生成测试数据
drop table partition_table;
CREATE TABLE partition_table (
id int not null,
tm timestamptz not null,
name varchar(10)
) PARTITION BY RANGE (tm);
create table ptab01_202201 partition of partition_table for values from ('2022-01-01') to ('2022-02-01');
create table ptab01_202202 partition of partition_table for values from ('2022-02-01') to ('2022-03-01');
create table ptab01_202203 partition of partition_table for values from ('2022-03-01') to ('2022-04-01');
create table ptab01_202204 partition of partition_table for values from ('2022-04-01') to ('2022-05-01');
create table ptab01_202205 partition of partition_table for values from ('2022-05-01') to ('2022-06-01');
create table ptab01_202206 partition of partition_table for values from ('2022-06-01') to ('2022-07-01');
create table ptab01_202207 partition of partition_table for values from ('2022-07-01') to ('2022-08-01');
create table ptab01_202208 partition of partition_table for values from ('2022-08-01') to ('2022-09-01');
create table ptab01_202209 partition of partition_table for values from ('2022-09-01') to ('2022-10-01');
create table ptab01_202210 partition of partition_table for values from ('2022-10-01') to ('2022-11-01');
create table ptab01_202211 partition of partition_table for values from ('2022-11-01') to ('2022-12-01');
create table ptab01_202212 partition of partition_table for values from ('2022-12-01') to ('2023-01-01');
insert into partition_table
select extract(epoch from seq), seq ,chr(19968+(random()*20901)::int)
from
generate_series('2022-01-01'::timestamptz, '2022-12-31 23:59:59'::timestamptz, interval '10 seconds') as seq;
添加主键
lightdb@postgres=# alter table partition_table ADD CONSTRAINT partition_table_pkey PRIMARY KEY (id); ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: PRIMARY KEY constraint on table "partition_table" lacks column "tm" which is part of the partition key.
可以看到主键必须包含分区键,这和MySQL一样,准确的说是唯一约束必须包含分区键,在 MySQL 数据库中，分区表的索引都是局部，而非全局。也就是说，索引在每个分区文件中都是独立的，所以分区表上的唯一索引必须包含分区列信息，否则创建会报错
CREATE TABLE t (
a INT,
b INT,
c DATETIME,
d VARCHAR(32),
e INT,
KEY idx_e (e)
)
partition by range columns(c) (
PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);
mysql> alter table t add primary key(a);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
mysql> alter table t add unique key(d);
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
mysql> alter table t add primary key(a,c);
Query OK, 0 rows affected (0.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t add unique key(d,c);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
而Oracle没有这方面的限制
CREATE TABLE orders (
o_orderkey number(20,2) NOT NULL PRIMARY KEY,
O_ORDERDATE DATE NOT NULL,
O_NAME varchar(79) NOT NULL) PARTITION BY RANGE (O_ORDERDATE) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t01 values less than(to_date('2022-01-01', 'yyyy-mm-dd')));
insert into orders values (1,to_date('2021-11-11','yyyy-mm-dd'),'xiaoming');
insert into orders values (2,to_date('2022-01-11','yyyy-mm-dd'),'xiaogang');
insert into orders values (3,to_date('2022-02-11','yyyy-mm-dd'),'xiaoju');
commit;
SQL> alter table orders drop primary key;
Table altered.
SQL> alter table orders add primary key(o_orderkey);
Table altered.
添加分区键，创建成功
lightdb@postgres=# alter table partition_table ADD CONSTRAINT partition_table_pkey PRIMARY KEY (id,tm); ALTER TABLE
LightDB分区裁剪功能
LightDB声明式分区参数是enable_partition_pruning，默认开启
lightdb@postgres=# show enable_partition_pruning; enable_partition_pruning -------------------------- on (1 row) lightdb@postgres=# explain (analyze,buffers) select * from partition_table where tm='2022-07-07'::timestamptz; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- --------------------- Index Scan using ptab01_202207_pkey on ptab01_202207 partition_table (cost=0.42..3043.23 rows=1 width=16) (actual time=0.941..4 .767 rows=1 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1030 Planning Time: 0.108 ms Execution Time: 4.794 ms (5 rows)
下面我们将参数关闭
lightdb@postgres=# set enable_partition_pruning = off; SET lightdb@postgres=# explain (analyze,buffers) select * from partition_table where tm='2022-07-07'::timestamptz; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.42..35833.24 rows=12 width=16) (actual time=28.395..55.075 rows=1 loops=1) Buffers: shared hit=12117 -> Index Scan using ptab01_202201_pkey on ptab01_202201 partition_table_1 (cost=0.42..3043.23 rows=1 width=16) (actual time=5.029..5.029 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1029 -> Index Scan using ptab01_202202_pkey on ptab01_202202 partition_table_2 (cost=0.42..2748.83 rows=1 width=16) (actual time=4.249..4.250 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=929 -> Index Scan using ptab01_202203_pkey on ptab01_202203 partition_table_3 (cost=0.42..3043.23 rows=1 width=16) (actual time=4.743..4.744 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1029 -> Index Scan using ptab01_202204_pkey on ptab01_202204 partition_table_4 (cost=0.42..2945.43 rows=1 width=16) (actual time=4.442..4.442 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=996 -> Index Scan using ptab01_202205_pkey on ptab01_202205 partition_table_5 (cost=0.42..3043.23 rows=1 width=16) (actual time=4.633..4.634 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1029 -> Index Scan using ptab01_202206_pkey on ptab01_202206 partition_table_6 (cost=0.42..2945.43 rows=1 width=16) (actual time=4.400..4.400 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=996 -> Index Scan using ptab01_202207_pkey on ptab01_202207 partition_table_7 (cost=0.42..3043.23 rows=1 width=16) (actual time=0.887..4.551 rows=1 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1030 -> Index Scan using ptab01_202208_pkey on ptab01_202208 partition_table_8 (cost=0.42..3043.23 rows=1 width=16) (actual time=4.818..4.819 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1029 -> Index Scan using ptab01_202209_pkey on ptab01_202209 partition_table_9 (cost=0.42..2945.43 rows=1 width=16) (actual time=4.420..4.421 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=996 -> Index Scan using ptab01_202210_pkey on ptab01_202210 partition_table_10 (cost=0.42..3043.23 rows=1 width=16) (actual time=4.594..4.595 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1029 -> Index Scan using ptab01_202211_pkey on ptab01_202211 partition_table_11 (cost=0.42..2945.43 rows=1 width=16) (actual time=4.453..4.454 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=996 -> Index Scan using ptab01_202212_pkey on ptab01_202212 partition_table_12 (cost=0.42..3043.23 rows=1 width=16) (actual time=4.699..4.699 rows=0 loops=1) Index Cond: (tm = '2022-07-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=1029 Planning Time: 0.293 ms Execution Time: 55.174 ms (40 rows)
可以看到关闭该参数，扫描所有分区
同时查询分区列和不同列如何创建索引
lightdb@postgres=# select * from partition_table where tm>'2022-11-07' and name = '坐'; id | tm | name ------------+------------------------+------ 1667797350 | 2022-11-07 13:02:30+08 | 坐 1667842780 | 2022-11-08 01:39:40+08 | 坐 1667860540 | 2022-11-08 06:35:40+08 | 坐 1668111830 | 2022-11-11 04:23:50+08 | 坐 1668120440 | 2022-11-11 06:47:20+08 | 坐 1668367000 | 2022-11-14 03:16:40+08 | 坐 1669046500 | 2022-11-22 00:01:40+08 | 坐 1669095670 | 2022-11-22 13:41:10+08 | 坐 1669228590 | 2022-11-24 02:36:30+08 | 坐 1669235780 | 2022-11-24 04:36:20+08 | 坐 1669536830 | 2022-11-27 16:13:50+08 | 坐 1669671070 | 2022-11-29 05:31:10+08 | 坐 1669703440 | 2022-11-29 14:30:40+08 | 坐 1670254170 | 2022-12-05 23:29:30+08 | 坐 1670342590 | 2022-12-07 00:03:10+08 | 坐 1670582230 | 2022-12-09 18:37:10+08 | 坐 1670632600 | 2022-12-10 08:36:40+08 | 坐 1671060810 | 2022-12-15 07:33:30+08 | 坐 1671463300 | 2022-12-19 23:21:40+08 | 坐 1671568700 | 2022-12-21 04:38:20+08 | 坐 1671765020 | 2022-12-23 11:10:20+08 | 坐 1671865830 | 2022-12-24 15:10:30+08 | 坐 1672242530 | 2022-12-28 23:48:50+08 | 坐 1672496430 | 2022-12-31 22:20:30+08 | 坐 (24 rows) lightdb@postgres=# explain (analyze,buffers) select * from partition_table where tm>'2022-11-07' and name = '坐'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.00..11262.72 rows=23 width=16) (actual time=3.893..46.873 rows=24 loops=1) Buffers: shared hit=3357 -> Seq Scan on ptab01_202211 partition_table_1 (cost=0.00..5539.00 rows=10 width=16) (actual time=3.890..22.586 rows=13 loops=1) Filter: ((tm > '2022-11-07 00:00:00+08'::timestamp with time zone) AND ((name)::text = '坐'::text)) Rows Removed by Filter: 259187 Buffers: shared hit=1651 -> Seq Scan on ptab01_202212 partition_table_2 (cost=0.00..5723.60 rows=13 width=16) (actual time=3.891..24.238 rows=11 loops=1) Filter: ((tm > '2022-11-07 00:00:00+08'::timestamp with time zone) AND ((name)::text = '坐'::text)) Rows Removed by Filter: 267829 Buffers: shared hit=1706 Planning Time: 0.157 ms Execution Time: 46.922 ms (12 rows)
是创建包含分区键的索引还是？
我们知道Oracle的分区索引分Global Index 和Local Index，查询条件有跨分区和不跨分区的情况，当查询跨分区的时候，且where条件中包含分区字段，我们可以只需要在非分区字段建立本地索引（本地的组合索引），分区字段会自行的进行分区裁剪，前提是分区字段在其中的几乎没有过滤掉数据，另外如果是那中按照月分的，举例: where t>'01-29’date and t < '0202’date 这种情况也是需要包含分区字段的,因为选择条件顾虑掉了大量数据，分区裁剪作用有限，需要时间字段索引配合；
如果查询不跨分区那么就需要建立包含分区字段的前缀索引，这时候能过滤掉大量的不必要的数据，当然具体情况还要根据分区的大小、系统中一般的查询条件、要求多长时间返回数据而定；
当查询条件中没有分区字段这时候就需要建立global索引。
是创建(name)的索引还是(name,time)的索引呢？
lightdb@postgres=# create index i_partition_table_name on partition_table(name); CREATE INDEX lightdb@postgres=# explain (analyze,buffers) select * from partition_table where tm >'2022-11-07' and name = '坐'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Append (cost=0.29..28.21 rows=23 width=16) (actual time=0.035..0.123 rows=24 loops=1) Buffers: shared hit=31 -> Index Scan using ptab01_202211_name_idx on ptab01_202211 partition_table_1 (cost=0.29..13.53 rows=10 width=16) (actual time=0.033..0.057 rows=13 loops=1) Index Cond: ((name)::text = '坐'::text) Filter: (tm > '2022-11-07 00:00:00+08'::timestamp with time zone) Rows Removed by Filter: 3 Buffers: shared hit=18 -> Index Scan using ptab01_202212_name_idx on ptab01_202212 partition_table_2 (cost=0.30..14.56 rows=13 width=16) (actual time=0.017..0.036 rows=11 loops=1) Index Cond: ((name)::text = '坐'::text) Filter: (tm > '2022-11-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=13 Planning Time: 0.202 ms Execution Time: 0.169 ms (13 rows) lightdb@postgres=# create index i_partition_table_nametm on partition_table(name,tm); CREATE INDEX lightdb@postgres=# explain (analyze,buffers) select * from partition_table where tm>'2022-11-07' and name = '坐'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.42..26.29 rows=23 width=16) (actual time=0.031..0.122 rows=24 loops=1) Buffers: shared hit=29 -> Index Scan using ptab01_202211_name_tm_idx on ptab01_202211 partition_table_1 (cost=0.42..11.62 rows=10 width=16) (actual time=0.029..0.053 rows=13 loops=1) Index Cond: (((name)::text = '坐'::text) AND (tm > '2022-11-07 00:00:00+08'::timestamp with time zone)) Buffers: shared hit=16 -> Index Scan using ptab01_202212_name_idx on ptab01_202212 partition_table_2 (cost=0.30..14.56 rows=13 width=16) (actual time=0.018..0.039 rows=11 loops=1) Index Cond: ((name)::text = '坐'::text) Filter: (tm > '2022-11-07 00:00:00+08'::timestamp with time zone) Buffers: shared hit=13 Planning Time: 0.207 ms Execution Time: 0.207 ms (11 rows)
LightDB没有local索引和global索引一说，你可以理解为LightDB的每个分区表都是一个单独的实体表，基于上面的情况，多数情况下查询条件涉及到分区字段还是要创建包含分区字段的索引的