java的获取pg分区表的api,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=#

更多功能请参考：

阅读(1603) | 评论(0) | 转发(0) |