Pg 10 Multi-level Partition Table (range_list) configuration query

-- 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 precepts 2017-01-01 09), (10111 precepts 2017-02-01 09), (10112 precepts 2017-03-01 09 10), (10112)

(10114grammes 2017-05-01 09), (10115-06-01 09), (10116-07-01 09), (10118-08-01 09))

(10119)), (10120) (2017-10-01 09)), (10120) (10-10-01 09)), (10121)-- (10-11-01-09)), (10122)-- 2017-12-01-09 (10).

-- 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 09For 10 purl 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=#