范围分区类型
最近更新时间: 2025-02-18 16:02:00
- 创建主分区
postgres=# create table t_native_range (f1 bigint,f2 timestamp default now(), f3 integer) partition by range ( f2 )distribute by shard(f1) to group default_group;
CREATE TABLE- 建立两个子表
postgres=# create table t_native_range_201709 partition of t_native_range (f1 ,f2 , f3 ) for values from ('2017-09-01') to ('2017-10-01');
CREATE TABLE
postgres=# create table t_native_range_201710 partition of t_native_range (f1 ,f2 , f3 ) for values from ('2017-10-01') to ('2017-11-01');
CREATE TABLE
- 默认分区表
--没有默认分区表时插入会出错
postgres=# insert into t_native_range values(2,'2017-08-01',2);
ERROR: node:dn001, backend_pid:32123, nodename:dn001,backend_pid:32123,message:no partition of relation "t_native_range" found for row
DETAIL: Partition key of the failing row contains (f2) = (2017-08-01 00:00:00).
--添加默认分区表
postgres=# CREATE TABLE t_native_range_default PARTITION OF t_native_range DEFAULT;
CREATE TABLE
postgres=# insert into t_native_range values(2,'2017-08-01',2);
INSERT 0 1- MAXVALUE分区
postgres=# CREATE TABLE t_native_range_maxvalue PARTITION OF t_native_range for values from ('2017-11-01') to (maxvalue);
CREATE TABLE
postgres=# insert into t_native_range values(1,'2017-11-01',1);
INSERT 0 1
postgres=# select * from t_native_range where f2='2017-11-01';
f1 | f2 | f3
----+---------------------+----
1 | 2017-11-01 00:00:00 | 1
(1 row)
postgres=# explain select * from t_native_range where f2='2017-11-01';
QUERY PLAN
-------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001, dn002
-> Append (cost=0.00..24.12 rows=6 width=20)
-> Seq Scan on t_native_range_maxvalue (cost=0.00..24.12 rows=6 width=20)
Filter: (f2 = '2017-11-01 00:00:00'::timestamp without time zone)
(5 rows)
postgres=# insert into t_native_range values(1,'2018-1-01',1);
INSERT 0 1
postgres=# explain select * from t_native_range where f2='2018-1-01';
QUERY PLAN
-------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001, dn002
-> Append (cost=0.00..24.12 rows=6 width=20)
-> Seq Scan on t_native_range_maxvalue (cost=0.00..24.12 rows=6 width=20)
Filter: (f2 = '2018-01-01 00:00:00'::timestamp without time zone)
(5 rows)
postgres=#
所有比2017-11-1大的数据都存储到子表t_native_range_maxvalue- MINVALUE分区
postgres=# CREATE TABLE t_native_range_minvalue PARTITION OF t_native_range for values from (minvalue) to ('2017-09-01');
CREATE TABLE
postgres=# insert into t_native_range values(1,'2017-08-01',1);
INSERT 0 1
postgres=# explain SELECT * FROM t_native_range where f2='2017-08-01';
QUERY PLAN
-------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001, dn002
-> Append (cost=0.00..24.12 rows=6 width=20)
-> Seq Scan on t_native_range_minvalue (cost=0.00..24.12 rows=6 width=20)
Filter: (f2 = '2017-08-01 00:00:00'::timestamp without time zone)
(5 rows)- 查看表结构
postgres=# \d+ t_native_range
Table"tbase_pg_proc.t_native_range"
Column | Type | Collation | Nullable | Default |Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
f1 |bigint | | | | plain | |
f2 |timestamp without time zone | | | now() | plain | |
f3 |integer | | | | plain | |
Partition key: RANGE (f2)
Partitions: t_native_range_201709 FORVALUES FROM ('2017-09-01 00:00:00') TO ('2017-10-01 00:00:00'),
t_native_range_201710 FOR VALUES FROM ('2017-10-01 00:00:00') TO('2017-11-01 00:00:00'),
t_native_range_maxvalue FOR VALUES FROM ('2017-11-01 00:00:00') TO(MAXVALUE),
t_native_range_minvalue FOR VALUES FROM (MINVALUE) TO ('2017-09-0100:00:00'),
t_native_range_default DEFAULT
Distribute By: SHARD(f1)
Location Nodes: ALL DATANODES创建主分区
postgres=# create table t_native_range (f1 bigint,f2 timestamp default now(), f3 integer) partition by range ( f2 )distribute by shard(f1) to group default_group;
CREATE TABLE