第三方分区表管理工具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=#
更多功能请参考:
阅读(1603) | 评论(0) | 转发(0) |