Postgresql-9.5 官方自带分区表功能测试

0、单表测试

tank=# create table ghan(id int PRIMARY KEY,name char(10) ,password text,cudate date);

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

tank=# explain select count(*) from ghan where cudate < '2017-07-01' and cudate >'2017-04-10';
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Aggregate  (cost=214311.89..214311.90 rows=1 width=0)
   ->  Seq Scan on ghan  (cost=0.00..214219.20 rows=37076 width=0)
         Filter: ((cudate < '2017-07-01'::date) AND (cudate > '2017-04-10'::date))
(3 rows)


tank=# \timing
Timing is on.
tank=# select count(*) from ghan where cudate < '2017-07-01' and cudate >'2017-04-10';
  count  
---------
 2217718
(1 row)


Time: 2752.445 ms

官方自带分区表功能：


一、新建主表

CREATE TABLE ghan_record
(
id int PRIMARY KEY,
name char(10),
password text,
sales_date DATE NOT NULL DEFAULT CURRENT_DATE
);


二、新建范围分区子表


/* Creating a range partition table*/


CREATE TABLE ghan_record_m1_to_m2
(
PRIMARY KEY (id, sales_date),
CHECK (sales_date >= DATE '2016-10-01'
AND sales_date < DATE '2017-03-01')
)
INHERITS (ghan_record);


CREATE TABLE ghan_record_m3_to_m4
(
PRIMARY KEY (id, sales_date),
CHECK (sales_date >= DATE '2017-03-01'
AND sales_date < DATE '2017-05-01')
)
INHERITS (ghan_record);


CREATE TABLE ghan_record_m5_to_m6
(
PRIMARY KEY (id, sales_date),
CHECK (sales_date >= DATE '2017-05-01'
AND sales_date < DATE '2017-07-01')
)
INHERITS (ghan_record);


CREATE TABLE ghan_record_m7_to_m8
(
PRIMARY KEY (id, sales_date),
CHECK (sales_date >= DATE '2017-07-01'
AND sales_date < DATE '2017-09-01')
)
INHERITS (ghan_record);




CREATE TABLE ghan_record_m9_to_m10
(
PRIMARY KEY (id, sales_date),
CHECK (sales_date >= DATE '2017-09-01'
AND sales_date < DATE '2017-11-01')
)
INHERITS (ghan_record);


CREATE TABLE ghan_record_m11_to_m12
(
PRIMARY KEY (id, sales_date),
CHECK (sales_date >= DATE '2017-11-01'
AND sales_date < DATE '2018-01-01')
)
INHERITS (ghan_record);



四、新表子表索引

/* Creating an index on child tables*/


CREATE INDEX m1_to_m2_sales_date ON ghan_record_m1_to_m2 (sales_date);


CREATE INDEX m3_to_m4_sales_date ON ghan_record_m3_to_m4(sales_date);


CREATE INDEX m5_to_m6_sales_date ON ghan_record_m5_to_m6(sales_date);


CREATE INDEX m7_to_m8_sales_date ON ghan_record_m7_to_m8(sales_date);


CREATE INDEX m9_to_m10_sales_date ON ghan_record_m9_to_m10(sales_date);


CREATE INDEX m11_to_m12_sales_date ON ghan_record_m11_to_m12 (sales_date);



五、新建主表分区插入函数


/* Creating a trigger on the master table*/


CREATE OR REPLACE FUNCTION ghan_record_insert()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.sales_date >= DATE '2016-10-01' AND NEW.sales_date < DATE '2017-03-01') THEN
INSERT INTO ghan_record_m1_to_m2 VALUES (NEW.*);
ELSEIF (NEW.sales_date >= DATE '2017-03-01' AND NEW.sales_date < DATE '2017-05-01') THEN
INSERT INTO ghan_record_m3_to_m4 VALUES (NEW.*);
ELSEIF (NEW.sales_date >= DATE '2017-05-01' AND NEW.sales_date < DATE '2017-07-01') THEN
INSERT INTO ghan_record_m5_to_m6 VALUES (NEW.*);
ELSEIF (NEW.sales_date >= DATE '2017-07-01' AND NEW.sales_date < DATE '2017-09-01') THEN
INSERT INTO ghan_record_m7_to_m8 VALUES (NEW.*);
ELSEIF (NEW.sales_date >= DATE '2017-09-01' AND NEW.sales_date < DATE '2017-11-01') THEN
INSERT INTO ghan_record_m9_to_m10 VALUES (NEW.*);
ELSEIF (NEW.sales_date >= DATE '2017-11-01' AND NEW.sales_date < DATE '2018-01-01') THEN
INSERT INTO ghan_record_m11_to_m12 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date is out of range. Something is wrong with
ghan_record_insert_trigger() function';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;


五、新建主表分区触发器


CREATE TRIGGER sales_day_trigger
BEFORE INSERT ON ghan_record
FOR EACH ROW
EXECUTE PROCEDURE ghan_record_insert();



六、随机插入数据测试


tank=#                  
 with dd as ( select generate_series(1,9990000) as key,'ghan' ,md5(random()::text), date (NOW() + (random() * (NOW()+'365 days' - NOW())) + '1 days'
)) insert into ghan_record select * from dd;
INSERT 0 0
Time: 1311078.560 ms
tank=# 



七、查看分区表结构及子表数据。


tank=# \d+ ghan_record
                                        Table "public.ghan_record"
   Column   |     Type      |              Modifiers               | Storage  | Stats target | Description 
------------+---------------+--------------------------------------+----------+--------------+-------------
 id         | integer       | not null                             | plain    |              | 
 name       | character(10) |                                      | extended |              | 
 password   | text          |                                      | extended |              | 
 sales_date | date          | not null default ('now'::text)::date | plain    |              | 
Indexes:
    "ghan_record_pkey" PRIMARY KEY, btree (id)
Triggers:
    sales_day_trigger BEFORE INSERT ON ghan_record FOR EACH ROW EXECUTE PROCEDURE ghan_record_insert()
Child tables: ghan_record_m11_to_m12,
              ghan_record_m1_to_m2,
              ghan_record_m3_to_m4,
              ghan_record_m5_to_m6,
              ghan_record_m7_to_m8,
              ghan_record_m9_to_m10



tank=# \d+ ghan_record_m5_to_m6;
                                    Table "public.ghan_record_m5_to_m6"
   Column   |     Type      |              Modifiers               | Storage  | Stats target | Description 
------------+---------------+--------------------------------------+----------+--------------+-------------
 id         | integer       | not null                             | plain    |              | 
 name       | character(10) |                                      | extended |              | 
 password   | text          |                                      | extended |              | 
 sales_date | date          | not null default ('now'::text)::date | plain    |              | 
Indexes:
    "ghan_record_m5_to_m6_pkey" PRIMARY KEY, btree (id, sales_date)
    "m5_to_m6_sales_date" btree (sales_date)
Check constraints:
    "ghan_record_m5_to_m6_sales_date_check" CHECK (sales_date >= '2017-05-01'::date AND sales_date < '2017-07-01'::date)
Inherits: ghan_record


tank=# 




tank=# select count(*) from ghan_record_m1_to_m2;
  count  
---------
 1973121
(1 row)


Time: 421.154 ms
tank=# select count(*) from ghan_record_m3_to_m4;
  count  
---------
 1669806
(1 row)


Time: 289.057 ms
tank=# select count(*) from ghan_record_m5_to_m6;
  count  
---------
 1668793
(1 row)

Time: 463.913 ms
tank=# 

八、约束排除


tank=# set constraint_exclusion = off; 
SET
Time: 22.423 ms
tank=# explain select count(*) from ghan_record where sales_date < '2017-05-01' and sales_date >'2017-04-10';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=75364.61..75364.62 rows=1 width=0)
   ->  Append  (cost=0.00..75271.91 rows=37079 width=0)
         ->  Seq Scan on ghan_record  (cost=0.00..0.00 rows=1 width=0)
               Filter: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
         ->  Bitmap Heap Scan on ghan_record_m3_to_m4  (cost=183.95..12582.19 rows=6197 width=0)
               Recheck Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
               ->  Bitmap Index Scan on m3_to_m4_sales_date  (cost=0.00..182.40 rows=6197 width=0)
                     Index Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
         ->  Bitmap Heap Scan on ghan_record_m5_to_m6  (cost=183.92..12576.55 rows=6194 width=0)
               Recheck Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
               ->  Bitmap Index Scan on m5_to_m6_sales_date  (cost=0.00..182.37 rows=6194 width=0)
                     Index Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
         ->  Bitmap Heap Scan on ghan_record_m7_to_m8  (cost=184.95..12779.11 rows=6295 width=0)
               Recheck Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
               ->  Bitmap Index Scan on m7_to_m8_sales_date  (cost=0.00..183.38 rows=6295 width=0)
                     Index Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
         ->  Bitmap Heap Scan on ghan_record_m9_to_m10  (cost=179.98..12584.32 rows=6200 width=0)
               Recheck Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
               ->  Bitmap Index Scan on m9_to_m10_sales_date  (cost=0.00..178.43 rows=6200 width=0)
                     Index Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
         ->  Bitmap Heap Scan on ghan_record_m11_to_m12  (cost=142.33..9883.93 rows=4869 width=0)
               Recheck Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
               ->  Bitmap Index Scan on m11_to_m12_sales_date  (cost=0.00..141.12 rows=4869 width=0)
                     Index Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
         ->  Bitmap Heap Scan on ghan_record_m1_to_m2  (cost=215.49..14865.81 rows=7323 width=0)
               Recheck Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
               ->  Bitmap Index Scan on m1_to_m2_sales_date  (cost=0.00..213.66 rows=7323 width=0)
                     Index Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
(28 rows)


Time: 2.303 ms
tank=# 

tank=# set constraint_exclusion = on; 
SET
Time: 0.343 ms
tank=# explain select count(*) from ghan_record where sales_date < '2017-05-01' and sales_date >'2017-04-10';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=12597.68..12597.69 rows=1 width=0)
   ->  Append  (cost=0.00..12582.19 rows=6198 width=0)
         ->  Seq Scan on ghan_record  (cost=0.00..0.00 rows=1 width=0)
               Filter: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
         ->  Bitmap Heap Scan on ghan_record_m3_to_m4  (cost=183.95..12582.19 rows=6197 width=0)
               Recheck Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
               ->  Bitmap Index Scan on m3_to_m4_sales_date  (cost=0.00..182.40 rows=6197 width=0)
                     Index Cond: ((sales_date < '2017-05-01'::date) AND (sales_date > '2017-04-10'::date))
(8 rows)


Time: 2.088 ms
tank=# 


配置postgresql.conf 约束排除

[postgres@db2 data]$ cat postgresql.conf |grep constraint_exclusion
constraint_exclusion = partition # on, off, or partition
[postgres@db2 data]$ 



十、相同相数据量测试：

tank=# select count(*) from ghan_record where sales_date < '2017-07-01' and sales_date >'2017-04-10';
  count  
---------
 2216775
(1 row)


Time: 1325.496 ms
tank=# explain select count(*) from ghan_record where sales_date < '2017-07-01' and sales_date >'2017-04-10';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=25189.72..25189.73 rows=1 width=0)
   ->  Append  (cost=0.00..25158.74 rows=12392 width=0)
         ->  Seq Scan on ghan_record  (cost=0.00..0.00 rows=1 width=0)
               Filter: ((sales_date < '2017-07-01'::date) AND (sales_date > '2017-04-10'::date))
         ->  Bitmap Heap Scan on ghan_record_m3_to_m4  (cost=183.95..12582.19 rows=6197 width=0)
               Recheck Cond: ((sales_date < '2017-07-01'::date) AND (sales_date > '2017-04-10'::date))
               ->  Bitmap Index Scan on m3_to_m4_sales_date  (cost=0.00..182.40 rows=6197 width=0)
                     Index Cond: ((sales_date < '2017-07-01'::date) AND (sales_date > '2017-04-10'::date))
         ->  Bitmap Heap Scan on ghan_record_m5_to_m6  (cost=183.92..12576.55 rows=6194 width=0)
               Recheck Cond: ((sales_date < '2017-07-01'::date) AND (sales_date > '2017-04-10'::date))
               ->  Bitmap Index Scan on m5_to_m6_sales_date  (cost=0.00..182.37 rows=6194 width=0)
                     Index Cond: ((sales_date < '2017-07-01'::date) AND (sales_date > '2017-04-10'::date))
(12 rows)


Time: 2.058 ms
tank=#