溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

pg 10多級(jí)分區(qū)表（range_list）配置查詢

發(fā)布時(shí)間：2020-07-03 02:42:18 來(lái)源：網(wǎng)絡(luò) 閱讀：1228 作者：pgmia 欄目：數(shù)據(jù)庫(kù)

-- define partitioned table: t_range_list

create table t_range_list(id bigserial, tenant_id int, crt_time timestamp) partition by list ( mod(tenant_id, 10));



-- level 1 ： list-- list ( mod(tenant_id, 10))

create table t_range_list_0 partition of t_range_list (id, tenant_id, crt_time ) for values in (0) partition by range (crt_time);

create table t_range_list_1 partition of t_range_list (id, tenant_id, crt_time ) for values in (1) partition by range (crt_time); 

create table t_range_list_2 partition of t_range_list (id, tenant_id, crt_time ) for values in (2) partition by range (crt_time); 

create table t_range_list_3 partition of t_range_list (id, tenant_id, crt_time ) for values in (3) partition by range (crt_time);

create table t_range_list_4 partition of t_range_list (id, tenant_id, crt_time ) for values in (4) partition by range (crt_time);

create table t_range_list_5 partition of t_range_list (id, tenant_id, crt_time ) for values in (5) partition by range (crt_time);

create table t_range_list_6 partition of t_range_list (id, tenant_id, crt_time ) for values in (6) partition by range (crt_time);

create table t_range_list_7 partition of t_range_list (id, tenant_id, crt_time ) for values in (7) partition by range (crt_time);

create table t_range_list_8 partition of t_range_list (id, tenant_id, crt_time ) for values in (8) partition by range (crt_time);

create table t_range_list_9 partition of t_range_list (id, tenant_id, crt_time ) for values in (9) partition by range (crt_time);



-- level 2 ： range-- range (crt_time)

-- t_range_list_0

create table t_range_list_0_201701 partition of t_range_list_0 (id  primary key, tenant_id , crt_time ) for values from ('2017-01-01') to ('2017-02-01');

create table t_range_list_0_201702 partition of t_range_list_0 (id  primary key, tenant_id , crt_time ) for values from ('2017-02-01') to ('2017-03-01');

create table t_range_list_0_201703 partition of t_range_list_0 (id  primary key, tenant_id , crt_time ) for values from ('2017-03-01') to ('2017-04-01');

create table t_range_list_0_201704 partition of t_range_list_0 (id  primary key, tenant_id , crt_time ) for values from ('2017-04-01') to ('2017-05-01');

create table t_range_list_0_201705 partition of t_range_list_0 (id  primary key, tenant_id , crt_time ) for values from ('2017-05-01') to ('2017-06-01');

create table t_range_list_0_201706 partition of t_range_list_0 (id  primary key, tenant_id , crt_time ) for values from ('2017-06-01') to ('2017-07-01');

create table t_range_list_0_201707 partition of t_range_list_0 (id  primary key, tenant_id , crt_time ) for values from ('2017-07-01') to ('2017-08-01');

create table t_range_list_0_201708 partition of t_range_list_0 (id  primary key, tenant_id , crt_time ) for values from ('2017-08-01') to ('2017-09-01');

create table t_range_list_0_201709 partition of t_range_list_0 (id  primary key, tenant_id , crt_time ) for values from ('2017-09-01') to ('2017-10-01');

create table t_range_list_0_201710 partition of t_range_list_0 (id  primary key, tenant_id , crt_time ) for values from ('2017-10-01') to ('2017-11-01');

create table t_range_list_0_201711 partition of t_range_list_0 (id  primary key, tenant_id , crt_time ) for values from ('2017-11-01') to ('2017-12-01');

create table t_range_list_0_201712 partition of t_range_list_0 (id  primary key, tenant_id , crt_time ) for values from ('2017-12-01') to ('2018-01-01');


-- t_range_list_1

create table t_range_list_1_201701 partition of t_range_list_1 (id  primary key, tenant_id , crt_time ) for values from ('2017-01-01') to ('2017-02-01');

create table t_range_list_1_201702 partition of t_range_list_1 (id  primary key, tenant_id , crt_time ) for values from ('2017-02-01') to ('2017-03-01');

create table t_range_list_1_201703 partition of t_range_list_1 (id  primary key, tenant_id , crt_time ) for values from ('2017-03-01') to ('2017-04-01');

create table t_range_list_1_201704 partition of t_range_list_1 (id  primary key, tenant_id , crt_time ) for values from ('2017-04-01') to ('2017-05-01');

create table t_range_list_1_201705 partition of t_range_list_1 (id  primary key, tenant_id , crt_time ) for values from ('2017-05-01') to ('2017-06-01');

create table t_range_list_1_201706 partition of t_range_list_1 (id  primary key, tenant_id , crt_time ) for values from ('2017-06-01') to ('2017-07-01');

create table t_range_list_1_201707 partition of t_range_list_1 (id  primary key, tenant_id , crt_time ) for values from ('2017-07-01') to ('2017-08-01');

create table t_range_list_1_201708 partition of t_range_list_1 (id  primary key, tenant_id , crt_time ) for values from ('2017-08-01') to ('2017-09-01');

create table t_range_list_1_201709 partition of t_range_list_1 (id  primary key, tenant_id , crt_time ) for values from ('2017-09-01') to ('2017-10-01');

create table t_range_list_1_201710 partition of t_range_list_1 (id  primary key, tenant_id , crt_time ) for values from ('2017-10-01') to ('2017-11-01');

create table t_range_list_1_201711 partition of t_range_list_1 (id  primary key, tenant_id , crt_time ) for values from ('2017-11-01') to ('2017-12-01');

create table t_range_list_1_201712 partition of t_range_list_1 (id  primary key, tenant_id , crt_time ) for values from ('2017-12-01') to ('2018-01-01');


-- t_range_list_2;

create table t_range_list_2_201701 partition of t_range_list_2 (id  primary key, tenant_id , crt_time ) for values from ('2017-01-01') to ('2017-02-01');

create table t_range_list_2_201702 partition of t_range_list_2 (id  primary key, tenant_id , crt_time ) for values from ('2017-02-01') to ('2017-03-01');

create table t_range_list_2_201703 partition of t_range_list_2 (id  primary key, tenant_id , crt_time ) for values from ('2017-03-01') to ('2017-04-01');

create table t_range_list_2_201704 partition of t_range_list_2 (id  primary key, tenant_id , crt_time ) for values from ('2017-04-01') to ('2017-05-01');

create table t_range_list_2_201705 partition of t_range_list_2 (id  primary key, tenant_id , crt_time ) for values from ('2017-05-01') to ('2017-06-01');

create table t_range_list_2_201706 partition of t_range_list_2 (id  primary key, tenant_id , crt_time ) for values from ('2017-06-01') to ('2017-07-01');

create table t_range_list_2_201707 partition of t_range_list_2 (id  primary key, tenant_id , crt_time ) for values from ('2017-07-01') to ('2017-08-01');

create table t_range_list_2_201708 partition of t_range_list_2 (id  primary key, tenant_id , crt_time ) for values from ('2017-08-01') to ('2017-09-01');

create table t_range_list_2_201709 partition of t_range_list_2 (id  primary key, tenant_id , crt_time ) for values from ('2017-09-01') to ('2017-10-01');

create table t_range_list_2_201710 partition of t_range_list_2 (id  primary key, tenant_id , crt_time ) for values from ('2017-10-01') to ('2017-11-01');

create table t_range_list_2_201711 partition of t_range_list_2 (id  primary key, tenant_id , crt_time ) for values from ('2017-11-01') to ('2017-12-01');

create table t_range_list_2_201712 partition of t_range_list_2 (id  primary key, tenant_id , crt_time ) for values from ('2017-12-01') to ('2018-01-01');





-- t_range_list_3;

create table t_range_list_3_201701 partition of t_range_list_3 (id  primary key, tenant_id , crt_time ) for values from ('2017-01-01') to ('2017-02-01');

create table t_range_list_3_201702 partition of t_range_list_3 (id  primary key, tenant_id , crt_time ) for values from ('2017-02-01') to ('2017-03-01');

create table t_range_list_3_201703 partition of t_range_list_3 (id  primary key, tenant_id , crt_time ) for values from ('2017-03-01') to ('2017-04-01');

create table t_range_list_3_201704 partition of t_range_list_3 (id  primary key, tenant_id , crt_time ) for values from ('2017-04-01') to ('2017-05-01');

create table t_range_list_3_201705 partition of t_range_list_3 (id  primary key, tenant_id , crt_time ) for values from ('2017-05-01') to ('2017-06-01');

create table t_range_list_3_201706 partition of t_range_list_3 (id  primary key, tenant_id , crt_time ) for values from ('2017-06-01') to ('2017-07-01');

create table t_range_list_3_201707 partition of t_range_list_3 (id  primary key, tenant_id , crt_time ) for values from ('2017-07-01') to ('2017-08-01');

create table t_range_list_3_201708 partition of t_range_list_3 (id  primary key, tenant_id , crt_time ) for values from ('2017-08-01') to ('2017-09-01');

create table t_range_list_3_201709 partition of t_range_list_3 (id  primary key, tenant_id , crt_time ) for values from ('2017-09-01') to ('2017-10-01');

create table t_range_list_3_201710 partition of t_range_list_3 (id  primary key, tenant_id , crt_time ) for values from ('2017-10-01') to ('2017-11-01');

create table t_range_list_3_201711 partition of t_range_list_3 (id  primary key, tenant_id , crt_time ) for values from ('2017-11-01') to ('2017-12-01');

create table t_range_list_3_201712 partition of t_range_list_3 (id  primary key, tenant_id , crt_time ) for values from ('2017-12-01') to ('2018-01-01');



-- t_range_list_4;

create table t_range_list_4_201701 partition of t_range_list_4 (id  primary key, tenant_id , crt_time ) for values from ('2017-01-01') to ('2017-02-01');

create table t_range_list_4_201702 partition of t_range_list_4 (id  primary key, tenant_id , crt_time ) for values from ('2017-02-01') to ('2017-03-01');

create table t_range_list_4_201703 partition of t_range_list_4 (id  primary key, tenant_id , crt_time ) for values from ('2017-03-01') to ('2017-04-01');

create table t_range_list_4_201704 partition of t_range_list_4 (id  primary key, tenant_id , crt_time ) for values from ('2017-04-01') to ('2017-05-01');

create table t_range_list_4_201705 partition of t_range_list_4 (id  primary key, tenant_id , crt_time ) for values from ('2017-05-01') to ('2017-06-01');

create table t_range_list_4_201706 partition of t_range_list_4 (id  primary key, tenant_id , crt_time ) for values from ('2017-06-01') to ('2017-07-01');

create table t_range_list_4_201707 partition of t_range_list_4 (id  primary key, tenant_id , crt_time ) for values from ('2017-07-01') to ('2017-08-01');

create table t_range_list_4_201708 partition of t_range_list_4 (id  primary key, tenant_id , crt_time ) for values from ('2017-08-01') to ('2017-09-01');

create table t_range_list_4_201709 partition of t_range_list_4 (id  primary key, tenant_id , crt_time ) for values from ('2017-09-01') to ('2017-10-01');

create table t_range_list_4_201710 partition of t_range_list_4 (id  primary key, tenant_id , crt_time ) for values from ('2017-10-01') to ('2017-11-01');

create table t_range_list_4_201711 partition of t_range_list_4 (id  primary key, tenant_id , crt_time ) for values from ('2017-11-01') to ('2017-12-01');

create table t_range_list_4_201712 partition of t_range_list_4 (id  primary key, tenant_id , crt_time ) for values from ('2017-12-01') to ('2018-01-01');


-- t_range_list_5;

create table t_range_list_5_201701 partition of t_range_list_5 (id  primary key, tenant_id , crt_time ) for values from ('2017-01-01') to ('2017-02-01');

create table t_range_list_5_201702 partition of t_range_list_5 (id  primary key, tenant_id , crt_time ) for values from ('2017-02-01') to ('2017-03-01');

create table t_range_list_5_201703 partition of t_range_list_5 (id  primary key, tenant_id , crt_time ) for values from ('2017-03-01') to ('2017-04-01');

create table t_range_list_5_201704 partition of t_range_list_5 (id  primary key, tenant_id , crt_time ) for values from ('2017-04-01') to ('2017-05-01');

create table t_range_list_5_201705 partition of t_range_list_5 (id  primary key, tenant_id , crt_time ) for values from ('2017-05-01') to ('2017-06-01');

create table t_range_list_5_201706 partition of t_range_list_5 (id  primary key, tenant_id , crt_time ) for values from ('2017-06-01') to ('2017-07-01');

create table t_range_list_5_201707 partition of t_range_list_5 (id  primary key, tenant_id , crt_time ) for values from ('2017-07-01') to ('2017-08-01');

create table t_range_list_5_201708 partition of t_range_list_5 (id  primary key, tenant_id , crt_time ) for values from ('2017-08-01') to ('2017-09-01');

create table t_range_list_5_201709 partition of t_range_list_5 (id  primary key, tenant_id , crt_time ) for values from ('2017-09-01') to ('2017-10-01');

create table t_range_list_5_201710 partition of t_range_list_5 (id  primary key, tenant_id , crt_time ) for values from ('2017-10-01') to ('2017-11-01');

create table t_range_list_5_201711 partition of t_range_list_5 (id  primary key, tenant_id , crt_time ) for values from ('2017-11-01') to ('2017-12-01');

create table t_range_list_5_201712 partition of t_range_list_5 (id  primary key, tenant_id , crt_time ) for values from ('2017-12-01') to ('2018-01-01');


-- t_range_list_6;

create table t_range_list_6_201701 partition of t_range_list_6 (id  primary key, tenant_id , crt_time ) for values from ('2017-01-01') to ('2017-02-01');

create table t_range_list_6_201702 partition of t_range_list_6 (id  primary key, tenant_id , crt_time ) for values from ('2017-02-01') to ('2017-03-01');

create table t_range_list_6_201703 partition of t_range_list_6 (id  primary key, tenant_id , crt_time ) for values from ('2017-03-01') to ('2017-04-01');

create table t_range_list_6_201704 partition of t_range_list_6 (id  primary key, tenant_id , crt_time ) for values from ('2017-04-01') to ('2017-05-01');

create table t_range_list_6_201705 partition of t_range_list_6 (id  primary key, tenant_id , crt_time ) for values from ('2017-05-01') to ('2017-06-01');

create table t_range_list_6_201706 partition of t_range_list_6 (id  primary key, tenant_id , crt_time ) for values from ('2017-06-01') to ('2017-07-01');

create table t_range_list_6_201707 partition of t_range_list_6 (id  primary key, tenant_id , crt_time ) for values from ('2017-07-01') to ('2017-08-01');

create table t_range_list_6_201708 partition of t_range_list_6 (id  primary key, tenant_id , crt_time ) for values from ('2017-08-01') to ('2017-09-01');

create table t_range_list_6_201709 partition of t_range_list_6 (id  primary key, tenant_id , crt_time ) for values from ('2017-09-01') to ('2017-10-01');

create table t_range_list_6_201710 partition of t_range_list_6 (id  primary key, tenant_id , crt_time ) for values from ('2017-10-01') to ('2017-11-01');

create table t_range_list_6_201711 partition of t_range_list_6 (id  primary key, tenant_id , crt_time ) for values from ('2017-11-01') to ('2017-12-01');

create table t_range_list_6_201712 partition of t_range_list_6 (id  primary key, tenant_id , crt_time ) for values from ('2017-12-01') to ('2018-01-01');


-- t_range_list_7;

create table t_range_list_7_201701 partition of t_range_list_7 (id  primary key, tenant_id , crt_time ) for values from ('2017-01-01') to ('2017-02-01');

create table t_range_list_7_201702 partition of t_range_list_7 (id  primary key, tenant_id , crt_time ) for values from ('2017-02-01') to ('2017-03-01');

create table t_range_list_7_201703 partition of t_range_list_7 (id  primary key, tenant_id , crt_time ) for values from ('2017-03-01') to ('2017-04-01');

create table t_range_list_7_201704 partition of t_range_list_7 (id  primary key, tenant_id , crt_time ) for values from ('2017-04-01') to ('2017-05-01');

create table t_range_list_7_201705 partition of t_range_list_7 (id  primary key, tenant_id , crt_time ) for values from ('2017-05-01') to ('2017-06-01');

create table t_range_list_7_201706 partition of t_range_list_7 (id  primary key, tenant_id , crt_time ) for values from ('2017-06-01') to ('2017-07-01');

create table t_range_list_7_201707 partition of t_range_list_7 (id  primary key, tenant_id , crt_time ) for values from ('2017-07-01') to ('2017-08-01');

create table t_range_list_7_201708 partition of t_range_list_7 (id  primary key, tenant_id , crt_time ) for values from ('2017-08-01') to ('2017-09-01');

create table t_range_list_7_201709 partition of t_range_list_7 (id  primary key, tenant_id , crt_time ) for values from ('2017-09-01') to ('2017-10-01');

create table t_range_list_7_201710 partition of t_range_list_7 (id  primary key, tenant_id , crt_time ) for values from ('2017-10-01') to ('2017-11-01');

create table t_range_list_7_201711 partition of t_range_list_7 (id  primary key, tenant_id , crt_time ) for values from ('2017-11-01') to ('2017-12-01');

create table t_range_list_7_201712 partition of t_range_list_7 (id  primary key, tenant_id , crt_time ) for values from ('2017-12-01') to ('2018-01-01');


-- t_range_list_8;

create table t_range_list_8_201701 partition of t_range_list_8 (id  primary key, tenant_id , crt_time ) for values from ('2017-01-01') to ('2017-02-01');

create table t_range_list_8_201702 partition of t_range_list_8 (id  primary key, tenant_id , crt_time ) for values from ('2017-02-01') to ('2017-03-01');

create table t_range_list_8_201703 partition of t_range_list_8 (id  primary key, tenant_id , crt_time ) for values from ('2017-03-01') to ('2017-04-01');

create table t_range_list_8_201704 partition of t_range_list_8 (id  primary key, tenant_id , crt_time ) for values from ('2017-04-01') to ('2017-05-01');

create table t_range_list_8_201705 partition of t_range_list_8 (id  primary key, tenant_id , crt_time ) for values from ('2017-05-01') to ('2017-06-01');

create table t_range_list_8_201706 partition of t_range_list_8 (id  primary key, tenant_id , crt_time ) for values from ('2017-06-01') to ('2017-07-01');

create table t_range_list_8_201707 partition of t_range_list_8 (id  primary key, tenant_id , crt_time ) for values from ('2017-07-01') to ('2017-08-01');

create table t_range_list_8_201708 partition of t_range_list_8 (id  primary key, tenant_id , crt_time ) for values from ('2017-08-01') to ('2017-09-01');

create table t_range_list_8_201709 partition of t_range_list_8 (id  primary key, tenant_id , crt_time ) for values from ('2017-09-01') to ('2017-10-01');

create table t_range_list_8_201710 partition of t_range_list_8 (id  primary key, tenant_id , crt_time ) for values from ('2017-10-01') to ('2017-11-01');

create table t_range_list_8_201711 partition of t_range_list_8 (id  primary key, tenant_id , crt_time ) for values from ('2017-11-01') to ('2017-12-01');

create table t_range_list_8_201712 partition of t_range_list_8 (id  primary key, tenant_id , crt_time ) for values from ('2017-12-01') to ('2018-01-01');


-- t_range_list_9;

create table t_range_list_9_201701 partition of t_range_list_9 (id  primary key, tenant_id , crt_time ) for values from ('2017-01-01') to ('2017-02-01');

create table t_range_list_9_201702 partition of t_range_list_9 (id  primary key, tenant_id , crt_time ) for values from ('2017-02-01') to ('2017-03-01');

create table t_range_list_9_201703 partition of t_range_list_9 (id  primary key, tenant_id , crt_time ) for values from ('2017-03-01') to ('2017-04-01');

create table t_range_list_9_201704 partition of t_range_list_9 (id  primary key, tenant_id , crt_time ) for values from ('2017-04-01') to ('2017-05-01');

create table t_range_list_9_201705 partition of t_range_list_9 (id  primary key, tenant_id , crt_time ) for values from ('2017-05-01') to ('2017-06-01');

create table t_range_list_9_201706 partition of t_range_list_9 (id  primary key, tenant_id , crt_time ) for values from ('2017-06-01') to ('2017-07-01');

create table t_range_list_9_201707 partition of t_range_list_9 (id  primary key, tenant_id , crt_time ) for values from ('2017-07-01') to ('2017-08-01');

create table t_range_list_9_201708 partition of t_range_list_9 (id  primary key, tenant_id , crt_time ) for values from ('2017-08-01') to ('2017-09-01');

create table t_range_list_9_201709 partition of t_range_list_9 (id  primary key, tenant_id , crt_time ) for values from ('2017-09-01') to ('2017-10-01');

create table t_range_list_9_201710 partition of t_range_list_9 (id  primary key, tenant_id , crt_time ) for values from ('2017-10-01') to ('2017-11-01');

create table t_range_list_9_201711 partition of t_range_list_9 (id  primary key, tenant_id , crt_time ) for values from ('2017-11-01') to ('2017-12-01');

create table t_range_list_9_201712 partition of t_range_list_9 (id  primary key, tenant_id , crt_time ) for values from ('2017-12-01') to ('2018-01-01');


-- insert test data:

insert into t_range_list(tenant_id,crt_time) values(10110,'2017-01-01 09:10:30'),(10111,'2017-02-01 09:10:30'),(10112,'2017-03-01 09:10:30'),(10113,'2017-04-01 09:10:30'),

(10114,'2017-05-01 09:10:30'),(10115,'2017-06-01 09:10:30'),(10116,'2017-07-01 09:10:30'),(10118,'2017-08-01 09:10:30'),

(10119,'2017-09-01 09:10:30'),(10120,'2017-10-01 09:10:30'),(10121,'2017-11-01 09:10:30'),(10122,'2017-12-01 09:10:30')


--excute SQL Select

pdb=# explain analyze select * from t_range_list

where mod(tenant_id, 10)=mod(10112, 10)

and crt_time='2017-03-01 09:10:30';

                                                      QUERY PLAN                                                       

-----------------------------------------------------------------------------------------------------------------------

 Append  (cost=0.00..39.75 rows=1 width=20) (actual time=0.015..0.016 rows=1 loops=1)

   ->  Seq Scan on t_range_list_2_201703  (cost=0.00..39.75 rows=1 width=20) (actual time=0.014..0.015 rows=1 loops=1)

         Filter: ((crt_time = '2017-03-01 09:10:30'::timestamp without time zone) AND (mod(tenant_id, 10) = 2))

 Planning time: 5.177 ms

 Execution time: 0.047 ms

(5 rows)


pdb=# 


向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手