Postgresql-9.5第三方分区表管理工具pg_partman测试

第三方分区表管理工具pg_partman。

[postgres@db2 ~] unzip pg_partman-2.6.2.zip 
[postgres@db2 ~]cd pg_partman-2.6.2
[postgres@db2 ~] make install
[postgres@db2 ~]$ psql -d tank
psql (9.5.5)
Type "help" for help.

tank=# CREATE EXTENSION pg_partman;
CREATE EXTENSION
tank=# 
tank=# CREATE TABLE ghan_x3
(
id int,
name char(10),
password text,
sales_date DATE NOT NULL DEFAULT CURRENT_DATE
);


tank=# create index ghan_x3_index on ghan_x3 (sales_date);

tank=# SELECT create_parent('public.ghan_x3', 'sales_date', 'time', 'monthly', p_premake := 20);

tank=# with dd as ( select generate_series(1,99900) as key,'ghan' ,md5(random()::text), date (NOW() + (random() * (NOW()+'365 days' - NOW())) + '1 days'
)) insert into ghan_x3 select * from dd;

tank=# select count(*) from ghan_x3 where sales_date < '2017-07-01' and sales_date >'2017-05-10';
 count 
-------
 13737
(1 row)

tank=# explain select count(*) from ghan_x3 where sales_date < '2017-07-01' and sales_date >'2017-05-10';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=135.25..135.26 rows=1 width=0)
   ->  Append  (cost=0.00..135.09 rows=63 width=0)
         ->  Seq Scan on ghan_x3  (cost=0.00..0.00 rows=1 width=0)
               Filter: ((sales_date < '2017-07-01'::date) AND (sales_date > '2017-05-10'::date))
         ->  Bitmap Heap Scan on ghan_x3_p2017_05  (cost=4.61..69.71 rows=32 width=0)
               Recheck Cond: ((sales_date < '2017-07-01'::date) AND (sales_date > '2017-05-10'::date))
               ->  Bitmap Index Scan on ghan_x3_p2017_05_sales_date_idx  (cost=0.00..4.60 rows=32 width=0)
                     Index Cond: ((sales_date < '2017-07-01'::date) AND (sales_date > '2017-05-10'::date))
         ->  Bitmap Heap Scan on ghan_x3_p2017_06  (cost=4.59..65.38 rows=30 width=0)
               Recheck Cond: ((sales_date < '2017-07-01'::date) AND (sales_date > '2017-05-10'::date))
               ->  Bitmap Index Scan on ghan_x3_p2017_06_sales_date_idx  (cost=0.00..4.58 rows=30 width=0)
                     Index Cond: ((sales_date < '2017-07-01'::date) AND (sales_date > '2017-05-10'::date))
(12 rows)


tank=# 


更多功能请参考：