postgresql 10.1 分区表之 list 分区

查看数据库版本

select version();
PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit

list 分区
创建分区主表 drop table tmp_par_list

create table tmp_par_list ( 
  id int8,
  random_char varchar(100),
  day_id varchar(8)
) partition by list(day_id);

创建分区从表

CREATE TABLE tmp_par_list_p20171130   PARTITION OF tmp_par_list FOR VALUES in ('20171130');
CREATE TABLE tmp_par_list_p20171201   PARTITION OF tmp_par_list FOR VALUES in ('20171201');
CREATE TABLE tmp_par_list_p20171202   PARTITION OF tmp_par_list FOR VALUES in ('20171202');
CREATE TABLE tmp_par_list_p20171203   PARTITION OF tmp_par_list FOR VALUES in ('20171203');

创建索引

create index idx_tmp_par_list_p20171130_x1 on tmp_par_list_p20171130(day_id);
create index idx_tmp_par_list_p20171201_x1 on tmp_par_list_p20171201(day_id);
create index idx_tmp_par_list_p20171202_x1 on tmp_par_list_p20171202(day_id);
create index idx_tmp_par_list_p20171203_x1 on tmp_par_list_p20171203(day_id);

插入数据

insert into tmp_par_list 
select *
from (
	select generate_series(1, 5) as id, md5(random()::text) as info , '20171130' as day_id 
	union all
	select generate_series(1, 5) as id, md5(random()::text) as info , '20171201' as day_id 
	union all
	select generate_series(1, 5) as id, md5(random()::text) as info , '20171202' as day_id 
	union all
	select generate_series(1, 5) as id, md5(random()::text) as info , '20171203' as day_id 
    ) t0
;

查看数据

select * from public.tmp_par_list order by day_id,id ;

select * from public.tmp_par_list_p20171130;
select * from public.tmp_par_list_p20171201;
select * from public.tmp_par_list_p20171202;
select * from public.tmp_par_list_p20171203;
 

查看执行计划

explain
select *
  from tmp_par_list tp
 where 1=1
 
Append  (cost=0.00..51.20 rows=1120 width=260)
  ->  Seq Scan on tmp_par_list_p20171130 tp  (cost=0.00..12.80 rows=280 width=260)
  ->  Seq Scan on tmp_par_list_p20171201 tp_1  (cost=0.00..12.80 rows=280 width=260)
  ->  Seq Scan on tmp_par_list_p20171202 tp_2  (cost=0.00..12.80 rows=280 width=260)
  ->  Seq Scan on tmp_par_list_p20171203 tp_3  (cost=0.00..12.80 rows=280 width=260)
  
 
explain
select *
  from tmp_par_list tp
 where 1=1
   and tp.day_id='20171201'

Append  (cost=0.15..8.17 rows=1 width=260)
  ->  Index Scan using idx_tmp_par_list_p20171201_x1 on tmp_par_list_p20171201 tp  (cost=0.15..8.17 rows=1 width=260)
        Index Cond: ((day_id)::text = '20171201'::text)
        
        
explain
select *
  from tmp_par_list tp
 where 1=1
   and tp.day_id in ( '20171201' , '20171202')
   
Append  (cost=0.00..27.00 rows=6 width=260)
  ->  Seq Scan on tmp_par_list_p20171201 tp  (cost=0.00..13.50 rows=3 width=260)
        Filter: ((day_id)::text = ANY ('{20171201,20171202}'::text[]))
  ->  Seq Scan on tmp_par_list_p20171202 tp_1  (cost=0.00..13.50 rows=3 width=260)
        Filter: ((day_id)::text = ANY ('{20171201,20171202}'::text[]))

插入分区外数据

insert into tmp_par_list 
select *
from (
	select generate_series(1, 5) as id, md5(random()::text) as info , '20171129' as day_id 
    ) t0
;

ERROR:  no partition of relation "tmp_par_list" found for row
DETAIL:  Partition key of the failing row contains (day_id) = (20171129).



insert into tmp_par_list 
select *
from (
	select generate_series(1, 5) as id, md5(random()::text) as info , '20171204' as day_id 
    ) t0
;

ERROR:  no partition of relation "tmp_par_list" found for row
DETAIL:  Partition key of the failing row contains (day_id) = (20171204).