PostgreSQL?10分區(qū)表及性能測試報告小結(jié)
更新時間：2022年01月10日 11:22:06 作者：古道輕風(fēng)
PostgreSQL的分區(qū)表跟先前版本一樣，也要先建立主表，然后再建立子表，使用繼承的特性，但不需要手工寫規(guī)則了，目前支持range、list分區(qū)，10正式版本發(fā)布時不知會不會支持其它方法，感興趣的朋友跟隨小編一起看看吧
作者簡介：
中國比較早的postgresql使用者,2001年就開始使用postgresql,自2003年底至2014年一直擔(dān)任PGSQL中國社區(qū)論壇PostgreSQL的論壇板塊版主、管理員，參與Postgresql討論和發(fā)表專題文章7000多貼.擁有15年的erp設(shè)計,開發(fā)和實施經(jīng)驗,開源mrp系統(tǒng)PostMRP就是我的作品,該應(yīng)用軟件是一套基于Postgresql專業(yè)的制造業(yè)管理軟件系統(tǒng).目前任職于--中國第一物流控股有限公司/運力寶（北京）科技有限公司,為公司的研發(fā)部經(jīng)理
一、 測試環(huán)境
操作系統(tǒng)：CentOS 6.4
Postgresql版本號：10.0
CPU：Intel(R) Xeon(R) CPU E5-2407 v2 @ 2.40GHz 4核心 4線程
內(nèi)存：32G
硬盤：2T SAS 7200
二、 編譯安裝PostgreSQL 10
－－編譯安裝及初始化
[root@ad source]# git clone git://git.postgresql.org/git/postgresql.git [root@ad source]# cd postgresql [root@ad source]# ./configure --prefix=/usr/local/pgsql10 [root@ad postgresql]# gmake -j 4 [root@ad postgresql]# gmake install [root@ad postgresql]# su postgres [postgres@ad postgresql]# /usr/local/pgsql10/bin/initdb --no-locale -E utf8 -D /home/postgres/data10/ -U postgres
－－修改一些參數(shù)
postgresql.conf listen_addresses = '*' port = 10000 shared_buffers = 8096MB maintenance_work_mem = 512MB effective_cache_size = 30GB log_destination = 'csvlog' logging_collector = on log_directory = 'log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_file_mode = 0600 log_checkpoints = off log_connections = off log_disconnections = off log_duration = off log_line_prefix = '%m %h %a %u %d %x [%p] ' log_statement = 'none' log_timezone = 'PRC' track_activity_query_size = 4096 max_wal_size = 32GB min_wal_size = 2GB checkpoint_completion_target = 0.5
pg_hba.conf增加許可條目
host all all 192.168.1.0/24 trust
－－啟動服務(wù)
[postgres@ad data10]$ /usr/local/pgsql10/bin/pg_ctl start -D /home/postgres/data10/ －－連接數(shù)據(jù)庫 [postgres@ad data10]$ /usr/local/pgsql10/bin/psql -p 10000 -U postgres -h 127.0.0.1 -d postgres psql (10devel) Type "help" for help. postgres=#
PostgreSQL的分區(qū)表跟先前版本一樣，也要先建立主表，然后再建立子表，使用繼承的特性，但不需要手工寫規(guī)則了，這個比較贊阿。目前支持range、list分區(qū)，10正式版本發(fā)布時不知會不會支持其它方法。
range分區(qū)表
1、分區(qū)主表
create table order_range(id bigserial not null,userid integer,product text, createdate date) partition by range ( createdate );
分區(qū)主表不能建立全局約束，使用partition by range(xxx)說明分區(qū)的方式，xxx可以是多個字段，表達(dá)式……，具體見https://www.postgresql.org/docs/devel/static/sql-createtable.html
2、分區(qū)子表
create table order_range(id bigserial not null,userid integer,product text,
createdate date not null) partition by range ( createdate );
create table order_range_201701 partition of order_range(id primary key,userid,product,
createdate) for values from ('2017-01-01') to ('2017-02-01');
create table order_range_201702 partition of order_range(id primary key,userid,product,
createdate) for values from ('2017-02-01') to ('2017-03-01');- 說明：
- 建立分區(qū)表時必需指定主表。
- 分區(qū)表和主表的 列數(shù)量，定義 必須完全一致。
- 分區(qū)表的列可以單獨增加Default值，或約束。
- 當(dāng)用戶向主表插入數(shù)據(jù)庫時，系統(tǒng)自動路由到對應(yīng)的分區(qū)，如果沒有找到對應(yīng)分區(qū)，則拋出錯誤。
- 指定分區(qū)約束的值（范圍，LIST值），范圍，LIST不能重疊，重疊的路由會卡殼。
- 指定分區(qū)的列必需設(shè)置成not null,如建立主表時沒設(shè)置系統(tǒng)會自動加上。
- Range分區(qū)范圍為 >=最小值 and <最大值……
- 不支持通過更新的方法把數(shù)據(jù)從一個區(qū)移動到另外一個區(qū)，這樣做會報錯。如果要這樣做的話需要刪除原來的記錄，再INSERT一條新的記錄。
- 修改主表的字段名，字段類型時，會自動同時修改所有的分區(qū)。
- TRUNCATE 主表時，會清除所有繼承表分區(qū)的記錄，如果要清除單個分區(qū)，請對分區(qū)進(jìn)行操作。
- DROP主表時會把所有子表一起給DROP掉，如果drop單個分區(qū)，請對分區(qū)進(jìn)行操作。
- 使用psql能查看分區(qū)表的詳細(xì)定義。
postgres=# \d+ order_range
Table "public.order_range"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+---------+-----------+----------+-----------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('order_range_id_seq'::regclass) | plain | |
userid | integer | | | | plain | |
product | text | | | | extended | |
createdate | date | | not null | | plain | |
Partition key: RANGE (createdate)
Partitions: order_range_201701 FOR VALUES FROM ('2017-01-01') TO ('2017-02-01'),
order_range_201702 FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
postgres=# list分區(qū)表
1、分區(qū)主表
create table order_list(id bigserial not null,userid integer,product text,area text, createdate date) partition by list( area );
2、分區(qū)子表
create table order_list_gd partition of order_list(id primary key,userid,product,area,createdate) for values in ('廣東');
create table order_list_bj partition of order_list(id primary key,userid,product,area,createdate) for values in ('北京'); 多級分區(qū)表
先按地區(qū)分區(qū)，再按日期分區(qū)
1、主表
create table order_range_list(id bigserial not null,userid integer,product text,area text, createdate date) partition by list ( area );
2、一級分區(qū)表
create table order_range_list_gd partition of order_range_list for values in ('廣東') partition by range(createdate);
create table order_range_list_bj partition of order_range_list for values in ('北京') partition by range(createdate); 3、二級分區(qū)表
create table order_range_list_gd_201701 partition of order_range_list_gd(id primary
key,userid,product,area,createdate) for values from ('2017-01-01') to ('2017-02-01');
create table order_range_list_gd_201702 partition of order_range_list_gd(id primary
key,userid,product,area,createdate) for values from ('2017-02-01') to ('2017-03-01');
create table order_range_list_bj_201701 partition of order_range_list_bj(id primary
key,userid,product,area,createdate) for values from ('2017-01-01') to ('2017-02-01');
create table order_range_list_bj_201702 partition of order_range_list_bj(id primary
key,userid,product,area,createdate) for values from ('2017-02-01') to ('2017-03-01'); 直接操作分區(qū)也要受分區(qū)規(guī)則的約束
postgres=# insert into order_range_201702 (id,userid,product,createdate) values(1,
(random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'));
ERROR: new row for relation "order_range_201702" violates partition constraint
DETAIL: Failing row contains (1, 322345, 51a9357a78416d11a018949a42dd2f8d, 2017-01-01).
INSERT提示違反了分區(qū)約束
postgres=# update order_range_201701 set createdate='2017-02-01' where createdate='2017-01-17';
ERROR: new row for relation "order_range_201701" violates partition constraint
DETAIL: Failing row contains (1, 163357, 7e8fbe7b632a54ba1ec401d969f3259a, 2017-02-01).
UPDATE提示違反了分區(qū)約束
如果分區(qū)表是外部表，則約束失效，后面有介紹
使用ALTER TABLE xxx ATTACH[DETACH] PARTITION 增加或刪除分區(qū)
1、移除分區(qū)
錄入2條測試數(shù)據(jù)
postgres=# insert into order_range (userid,product,createdate)
values((random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'::date+
(random()*31)::integer));
INSERT 0 1
Time: 25.006 ms
postgres=# insert into order_range (userid,product,createdate)
values((random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'::date+
(random()*31)::integer));
INSERT 0 1
Time: 7.601 ms
postgres=# select * from order_range;
id | userid | product | createdate
----+--------+----------------------------------+------------
1 | 163357 | 7e8fbe7b632a54ba1ec401d969f3259a | 2017-01-17
2 | 349759 | 8095c9036295d3c800dace9069f9c102 | 2017-01-27
(2 rows)刪除分區(qū)
postgres=# alter table order_range detach partition order_range_201701; ALTER TABLE Time: 14.129 ms
查看確認(rèn)分區(qū)沒了
postgres=# \d+ order_range;
Table "public.order_range"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+---------+-----------+----------+-----------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('order_range_id_seq'::regclass) | plain | |
userid | integer | | | | plain | |
product | text | | | | extended | |
createdate | date | | not null | | plain | |
Partition key: RANGE (createdate)
Partitions: order_range_201702 FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
postgres=# 數(shù)據(jù)也查不出來了
postgres=# select * from order_range; id | userid | product | createdate ----+--------+---------+------------ (0 rows) Time: 0.505 ms
但分區(qū)表還在
postgres=# select * from order_range_201701; id | userid | product | createdate ----+--------+----------------------------------+------------ 1 | 163357 | 7e8fbe7b632a54ba1ec401d969f3259a | 2017-01-17 2 | 349759 | 8095c9036295d3c800dace9069f9c102 | 2017-01-27 (2 rows) Time: 0.727 ms
2、添加分區(qū)
postgres=# alter table order_range attach partition order_range_201701 FOR VALUES FROM ('2017-01-01') TO ('2017-02-01');
ERROR: column "createdate" in child table must be marked NOT NULL
Time: 0.564 ms增加子表里，約束需要與主表一致
postgres=# alter table order_range_201701 alter column createdate set not null;
ALTER TABLE
Time: 17.345 ms
postgres=# alter table order_range attach partition order_range_201701 FOR VALUES FROM ('2017-01-01') TO ('2017-01-15');
ERROR: partition constraint is violated by some row
Time: 1.276 ms加回來時可以修改其約束范圍，但數(shù)據(jù)必需在約束的規(guī)則范圍內(nèi)
postgres=# alter table order_range attach partition order_range_201701 FOR VALUES FROM
('2017-01-01') TO ('2017-02-01');
ALTER TABLE
Time: 18.407 ms分區(qū)表又加回來了
postgres=# \d+ order_range
Table "public.order_range"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+---------+-----------+----------+-----------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('order_range_id_seq'::regclass) | plain | |
userid | integer | | | | plain | |
product | text | | | | extended | |
createdate | date | | not null | | plain | |
Partition key: RANGE (createdate)
Partitions: order_range_201701 FOR VALUES FROM ('2017-01-01') TO ('2017-02-01'),
order_range_201702 FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
postgres=# select * from order_range;
id | userid | product | createdate
----+--------+----------------------------------+------------
1 | 163357 | 7e8fbe7b632a54ba1ec401d969f3259a | 2017-01-17
2 | 349759 | 8095c9036295d3c800dace9069f9c102 | 2017-01-27
(2 rows)
Time: 0.627 ms添加外部表作為分區(qū)表
－－增加一個新庫，建立需要的外部表
[postgres@ad root]$ /usr/local/pgsql10/bin/psql -p 10000 -U postgres -h 127.0.0.1 -d postgres
psql (10devel)
Type "help" for help.
#建立數(shù)據(jù)庫
postgres=# create database postgres_fdw;
CREATE DATABASE
postgres_fdw=# create table order_range_fdw(id bigserial not null,userid integer,product text, createdate date not null);
CREATE TABLE
postgres_fdw=#
#錄入一條測試數(shù)據(jù)
postgres_fdw=# insert into order_range_fdw (userid,product,createdate)
values((random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'::date-
(random()*31)::integer));
INSERT 0 1
postgres_fdw=# select * from order_range_fdw;
id | userid | product | createdate
----+--------+----------------------------------+------------
2 | 300686 | 55956a07742d6aebdef7ebb78c2400d7 | 2016-12-22
(1 row)－－在postgres庫中增加外部表order_range_fdw
[postgres@ad root]$ /usr/local/pgsql10/bin/psql -p 10000 -U postgres -h 127.0.0.1 -d postgres
psql (10devel)
Type "help" for help.
#增加postgres_fdw模塊
postgres=# create extension postgres_fdw;
CREATE EXTENSION
#建立外部服務(wù)器
postgres=# CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.1.10', port '10000', dbname 'postgres_fdw');
CREATE SERVER
#建立外部服務(wù)器用戶標(biāo)識
postgres=# CREATE USER MAPPING FOR postgres
postgres-# SERVER foreign_server
postgres-# OPTIONS (user 'postgres', password '');
CREATE USER MAPPING
#建立外部表
postgres=# CREATE FOREIGN TABLE order_range_fdw (
postgres(# id bigint not null,
postgres(# userid integer,
postgres(# product text,
postgres(# createdate date not null
postgres(# )
postgres-# SERVER foreign_server
postgres-# OPTIONS (schema_name 'public', table_name 'order_range_fdw');
CREATE FOREIGN TABLE
#查詢數(shù)據(jù)
postgres=# select * from order_range_fdw;
id | userid | product | createdate
----+--------+----------------------------------+------------
2 | 300686 | 55956a07742d6aebdef7ebb78c2400d7 | 2016-12-22
(1 row)
－－將外部表作為分區(qū)表添加到order_range下
#添加分區(qū)表
postgres=# alter table order_range attach partition order_range_fdw FOR VALUES FROM ('1900-01-01') TO ('2017-01-01');
ALTER TABLE
#查看order_range下的所有分區(qū)表
postgres=# \d+ order_range
Table "public.order_range"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+---------+-----------+----------+-----------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('order_range_id_seq'::regclass) | plain | |
userid | integer | | | | plain | |
product | text | | | | extended | |
createdate | date | | not null | | plain | |
Partition key: RANGE (createdate)
Partitions: order_range_201701 FOR VALUES FROM ('2017-01-01') TO ('2017-02-01'),
order_range_201702 FOR VALUES FROM ('2017-02-01') TO ('2017-03-01'),
order_range_fdw FOR VALUES FROM ('1900-01-01') TO ('2017-01-01')
#查詢數(shù)據(jù)
postgres=# select * from order_range where createdate<'2017-01-01';
id | userid | product | createdate
----+--------+----------------------------------+------------
2 | 300686 | 55956a07742d6aebdef7ebb78c2400d7 | 2016-12-22
(1 row)
#查看執(zhí)行計劃
postgres=# explain select * from order_range where createdate<'2017-01-01';
QUERY PLAN
--------------------------------------------------------------------------------
Append (cost=100.00..131.79 rows=379 width=48)
-> Foreign Scan on order_range_fdw (cost=100.00..131.79 rows=379 width=48)
(2 rows)
#測試看看能不能更新數(shù)據(jù)
postgres=# insert into order_range (userid,product,createdate)
values((random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'::date-
(random()*31)::integer));
ERROR: cannot route inserted tuples to a foreign table
postgres=# update order_range set createdate='2016-12-01' where createdate='2016-12-22';
UPDATE 1
postgres=# select * from order_range where createdate<'2017-01-01';
id | userid | product | createdate
----+--------+----------------------------------+------------
2 | 300686 | 55956a07742d6aebdef7ebb78c2400d7 | 2016-12-01
(1 row)
postgres=# delete from order_range where createdate='2016-12-01';
DELETE 1
postgres=# select * from order_range where createdate<'2017-01-01';
id | userid | product | createdate
----+--------+---------+------------
(0 rows)
postgres=# 插入數(shù)據(jù)時竟然不能路由到外部表，這個是處于什么考慮呢？？？，源碼中只是提示 /* We do not yet have a way to insert into a foreign partition */
還沒有辦法這樣做，猜猜后面的版本應(yīng)該能實現(xiàn)
下面再說說使用外部表作為分區(qū)表還有一些問題
1、無法約束向分區(qū)表插入約束外的數(shù)據(jù)，如下所示
postgres=# \d+ order_range
Table "public.order_range"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+---------+-----------+----------+-----------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('order_range_id_seq'::regclass) | plain | |
userid | integer | | | | plain | |
product | text | | | | extended | |
createdate | date | | not null | | plain | |
Partition key: RANGE (createdate)
Partitions: order_range_201701 FOR VALUES FROM ('2017-01-01') TO ('2017-02-01'),
order_range_201702 FOR VALUES FROM ('2017-02-01') TO ('2017-03-01'),
order_range_fdw FOR VALUES FROM ('1900-01-01') TO ('2017-01-01')
postgres=#
postgres=# insert into order_range_fdw (id,userid,product,createdate) values(1,
(random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'));
INSERT 0 1
postgres=# select * from order_range;
id | userid | product | createdate
----+--------+----------------------------------+------------
1 | 163357 | 7e8fbe7b632a54ba1ec401d969f3259a | 2017-01-17
2 | 349759 | 8095c9036295d3c800dace9069f9c102 | 2017-01-27
1 | 621895 | 5546c6e2a7006b52b5c2df55e19b3759 | 2017-02-01
4 | 313019 | 445316004208e09fb4e7eda2bf5b0865 | 2017-01-01
1 | 505836 | 6e9232c4863c82a2e97b9157996572ea | 2017-01-01
(5 rows)
postgres=# select * from order_range where createdate ='2017-01-01';
id | userid | product | createdate
----+--------+---------+------------
(0 rows)如果這樣操作會導(dǎo)致數(shù)據(jù)查詢出現(xiàn)不匹配。
2、sql執(zhí)行時無法下推
Sql執(zhí)行無法下推的話對于聚集函數(shù)的執(zhí)行存在很大的性能問題，使用時一定要特別的注意，如下所示
postgres=# delete from order_range_fdw;
DELETE 1
postgres=# insert into order_range_fdw (id,userid,product,createdate) values(1,
(random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2016-01-01'));
INSERT 0 1
postgres=# insert into order_range_fdw (id,userid,product,createdate) values(1,
(random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2016-02-01'));
INSERT 0 1
#訪問order_range，基執(zhí)行是
postgres=# explain analyze select count(1) from order_range where createdate<'2017-01-01';
QUERY PLAN
------------------------------------------------------------------------------------------
Aggregate (cost=178.27..178.28 rows=1 width=8) (actual time=0.656..0.656 rows=1 loops=1)
-> Append (cost=100.00..175.42 rows=1138 width=0) (actual time=0.647..0.649 rows=2 loops=1)
-> Foreign Scan on order_range_fdw (cost=100.00..175.42 rows=1138 width=0) (actual
time=0.647..0.648 rows=2 loops=1)
Planning time: 0.267 ms
Execution time: 1.122 ms
(5 rows)
#直接訪問外部表
postgres=# explain analyze select count(1) from order_range_fdw where createdate<'2017-01-01';
QUERY PLAN
-------------------------------------------------------------------------------------------
Foreign Scan (cost=102.84..155.54 rows=1 width=8) (actual time=0.661..0.662 rows=1 loops=1)
Relations: Aggregate on (public.order_range_fdw)
Planning time: 0.154 ms
Execution time: 1.051 ms
(4 rows)3、sql查詢需要訪問的分區(qū)表中包含了“外部分區(qū)表”和“非外部分區(qū)表”時， 無法使用Parallel Seq Scan，如下所示
#插入100W數(shù)據(jù)到分區(qū)表中
postgres=# insert into order_range (userid,product,createdate) SELECT
(random()::numeric(7,6)*1000000)::integer,md5(random()::text),('2017-01-01'::date+
(random()*58)::integer) from generate_series(1,1000000);
INSERT 0 1000000
#訪問所有的分區(qū)表
postgres=# explain select count(1) from order_range;
QUERY PLAN
---------------------------------------------------------------------------------------
Aggregate (cost=24325.22..24325.23 rows=1 width=8)
-> Append (cost=0.00..21558.23 rows=1106797 width=0)
-> Seq Scan on order_range_201701 (cost=0.00..11231.82 rows=580582 width=0)
-> Seq Scan on order_range_201702 (cost=0.00..10114.02 rows=522802 width=0)
-> Foreign Scan on order_range_fdw (cost=100.00..212.39 rows=3413 width=0)
(5 rows)
#只訪問“非外部分區(qū)表”
postgres=# explain select count(1) from order_range where createdate>='2017-01-01';
QUERY PLAN
-------------------------------------------------------------------------------------
Finalize Aggregate (cost=17169.84..17169.85 rows=1 width=8)
-> Gather (cost=17169.62..17169.83 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=16169.62..16169.63 rows=1 width=8)
-> Append (cost=0.00..15803.52 rows=146440 width=0)
-> Parallel Seq Scan on order_range_201701 (cost=0.00..8449.86
rows=80636 width=0)
Filter: (createdate >= '2017-01-01'::date)
-> Parallel Seq Scan on order_range_201702 (cost=0.00..7353.66
rows=65804 width=0)
Filter: (createdate >= '2017-01-01'::date)
(9 rows)
postgres=# 外部分區(qū)表的應(yīng)用場景
將業(yè)務(wù)庫上的不再修改的冷數(shù)全部分離到另一個節(jié)點上面，然后做為外部分區(qū)表掛上來。這樣可以保持業(yè)務(wù)庫的容量盡可以的輕，同時也不會對業(yè)務(wù)有侵入，這一點是非常的友好。但要注意Sql執(zhí)行無法下推的問題，無法使用Parallel Seq Scan問題。
如果在后面版本中能解決fdw partition insert路由問題和sql語句執(zhí)行下推問題那么就可以拿來做olap應(yīng)用了。
四、建立測試業(yè)務(wù)表
下面模似一個用戶收支流水表
－－非分區(qū)表
create table t_pay_all (id serial not null primary key,userid integer not null,pay_money float8 not null,createdate date not null); create index t_pay_all_userid_idx on t_pay_all using btree(userid); create index t_pay_all_createdate_idx on t_pay_all using btree(createdate);
－－分區(qū)表
生成12個分區(qū)，一個月份一個表
create table t_pay (id serial not null,userid integer not null,pay_money float8 not null,createdate
date not null) partition by range (createdate);
create table t_pay_201701 partition of t_pay(id primary key,userid,pay_money,createdate) for values
from ('2017-01-01') to ('2017-02-01');
create index t_pay_201701_createdate_idx on t_pay_201701 using btree(createdate);
create index t_pay_201701_userid_idx on t_pay_201701 using btree(userid);
create table t_pay_201702 partition of t_pay(id primary key,userid,pay_money,createdate) for values
from ('2017-02-01') to ('2017-03-01');
create index t_pay_201702_createdate_idx on t_pay_201702 using btree(createdate);
create index t_pay_201702_userid_idx on t_pay_201702 using btree(userid);
create table t_pay_201703 partition of t_pay(id primary key,userid,pay_money,createdate) for values
from ('2017-03-01') to ('2017-04-01');
create index t_pay_201703_createdate_idx on t_pay_201703 using btree(createdate);
create index t_pay_201703_userid_idx on t_pay_201703 using btree(userid);
create table t_pay_201704 partition of t_pay(id primary key,userid,pay_money,createdate) for values
from ('2017-04-01') to ('2017-05-01');
create index t_pay_201704_createdate_idx on t_pay_201704 using btree(createdate);
create index t_pay_201704_userid_idx on t_pay_201704 using btree(userid);
create table t_pay_201705 partition of t_pay(id primary key,userid,pay_money,createdate) for values
from ('2017-05-01') to ('2017-06-01');
create index t_pay_201705_createdate_idx on t_pay_201705 using btree(createdate);
create index t_pay_201705_userid_idx on t_pay_201705 using btree(userid);
create table t_pay_201706 partition of t_pay(id primary key,userid,pay_money,createdate) for values
from ('2017-06-01') to ('2017-07-01');
create index t_pay_201706_createdate_idx on t_pay_201706 using btree(createdate);
create index t_pay_201706_userid_idx on t_pay_201706 using btree(userid);
create table t_pay_201707 partition of t_pay(id primary key,userid,pay_money,createdate) for values
from ('2017-07-01') to ('2017-08-01');
create index t_pay_201707_createdate_idx on t_pay_201707 using btree(createdate);
create index t_pay_201707_userid_idx on t_pay_201707 using btree(userid);
create table t_pay_201708 partition of t_pay(id primary key,userid,pay_money,createdate) for values
from ('2017-08-01') to ('2017-09-01');
create index t_pay_201708_createdate_idx on t_pay_201708 using btree(createdate);
create index t_pay_201708_userid_idx on t_pay_201708 using btree(userid);
create table t_pay_201709 partition of t_pay(id primary key,userid,pay_money,createdate) for values
from ('2017-09-01') to ('2017-10-01');
create index t_pay_201709_createdate_idx on t_pay_201709 using btree(createdate);
create index t_pay_201709_userid_idx on t_pay_201709 using btree(userid);
create table t_pay_201710 partition of t_pay(id primary key,userid,pay_money,createdate) for values
from ('2017-10-01') to ('2017-11-01');
create index t_pay_201710_createdate_idx on t_pay_201710 using btree(createdate);
create index t_pay_201710_userid_idx on t_pay_201710 using btree(userid);
create table t_pay_201711 partition of t_pay(id primary key,userid,pay_money,createdate) for values
from ('2017-11-01') to ('2017-12-01');
create index t_pay_201711_createdate_idx on t_pay_201711 using btree(createdate);
create index t_pay_201711_userid_idx on t_pay_201711 using btree(userid);
create table t_pay_201712 partition of t_pay(id primary key,userid,pay_money,createdate) for values
from ('2017-12-01') to ('2018-01-01');
create index t_pay_201712_createdate_idx on t_pay_201712 using btree(createdate);
create index t_pay_201712_userid_idx on t_pay_201712 using btree(userid); 五、性能測試
數(shù)據(jù)導(dǎo)入
－－生成測試數(shù)據(jù)1000W條記錄（盡可能平均分布）
postgres=# copy (select (random()::numeric(7,6)*1000000)::integer as
userid,round((random()*100)::numeric,2) as pay_money,('2017-01-01'::date+ (random()*364)::integer)
as createtime from generate_series(1,10000000)) to '/home/pg/data.txt';
COPY 10000000
Time: 42674.548 ms (00:42.675)－－非分區(qū)表數(shù)據(jù)導(dǎo)入測試
postgres=# copy t_pay_all(userid,pay_money,createdate) from '/home/pg/data.txt'; COPY 10000000 Time: 114258.743 ms (01:54.259)
－－分區(qū)表數(shù)據(jù)導(dǎo)入測試
postgres=# copy t_pay(userid,pay_money,createdate) from '/home/pg/data.txt'; COPY 10000000 Time: 186358.447 ms (03:06.358) postgres=#
結(jié)論：數(shù)據(jù)導(dǎo)入時性能相差大約是一半，所以大數(shù)據(jù)量導(dǎo)入時最好直接導(dǎo)成分區(qū)表數(shù)據(jù)，然后直接對分區(qū)表進(jìn)行操作
查詢某一天的數(shù)據(jù)－－直接從cache里取數(shù)據(jù)
－－非分區(qū)表
postgres=# explain (analyze,buffers) select * from t_pay_all where createdate ='2017-06-01';
QUERY PLAN
-------------------------------------------------------------------------------------------
Bitmap Heap Scan on t_pay_all (cost=592.06..50797.88 rows=27307 width=20) (actual
time=14.544..49.039 rows=27384 loops=1)
Recheck Cond: (createdate = '2017-06-01'::date)
Heap Blocks: exact=22197
Buffers: shared hit=22289
-> Bitmap Index Scan on t_pay_all_createdate_idx (cost=0.00..585.24 rows=27307 width=0)
(actual time=7.121..7.121 rows=27384 loops=1)
Index Cond: (createdate = '2017-06-01'::date)
Buffers: shared hit=92
Planning time: 0.153 ms
Execution time: 51.583 ms
(9 rows)
Time: 52.272 ms－－分區(qū)表
postgres=# explain (analyze,buffers) select * from t_pay where createdate ='2017-06-01';
QUERY PLAN
----------------------------------------------------------------------------------------------
Append (cost=608.92..6212.11 rows=27935 width=20) (actual time=4.880..27.032 rows=27384 loops=1)
Buffers: shared hit=5323
-> Bitmap Heap Scan on t_pay_201706 (cost=608.92..6212.11 rows=27935 width=20) (actual
time=4.879..21.990 rows=27384 loops=1)
Recheck Cond: (createdate = '2017-06-01'::date)
Heap Blocks: exact=5226
Buffers: shared hit=5323
-> Bitmap Index Scan on t_pay_201706_createdate_idx (cost=0.00..601.94 rows=27935
width=0) (actual time=3.399..3.399 rows=27384 loops=1)
Index Cond: (createdate = '2017-06-01'::date)
Buffers: shared hit=97
Planning time: 0.521 ms
Execution time: 30.061 ms
(11 rows)結(jié)論：分區(qū)表的Planning time時間明顯比非分區(qū)表要高，但比起Execution time基本可以忽略。
查詢某個時間范圍的數(shù)據(jù)
1、時間范圍落在同一個分區(qū)內(nèi)
－－非分區(qū)表
postgres=# explain (analyze,buffers)select * from t_pay_all where createdate >='2017-06-01'
AND createdate<'2017-07-01';
QUERY PLAN
------------------------------------------------------------------------------------------
Bitmap Heap Scan on t_pay_all (cost=19802.01..95862.00 rows=819666 width=20) (actual
time=115.210..459.547 rows=824865 loops=1)
Recheck Cond: ((createdate >= '2017-06-01'::date) AND (createdate < '2017-07-01'::date))
Heap Blocks: exact=63701
Buffers: shared read=66578
-> Bitmap Index Scan on t_pay_all_createdate_idx (cost=0.00..19597.10 rows=819666 width=0)
(actual time=101.453..101.453 rows=825865 loops=1)
Index Cond: ((createdate >= '2017-06-01'::date) AND (createdate < '2017-07-01'::date))
Buffers: shared read=2877
Planning time: 0.166 ms
Execution time: 504.297 ms
(9 rows)
Time: 505.021 ms
postgres=# explain (analyze,buffers)select count(1) from t_pay_all where createdate >='2017-06-01'
AND createdate<'2017-07-01';
QUERY PLAN
----------------------------------------------------------------------------------------------
Finalize Aggregate (cost=90543.96..90543.97 rows=1 width=8) (actual time=335.334..335.335
rows=1 loops=1)
Buffers: shared hit=351 read=66593
-> Gather (cost=90543.74..90543.95 rows=2 width=8) (actual time=334.988..335.327 rows=3
loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=351 read=66593
-> Partial Aggregate (cost=89543.74..89543.75 rows=1 width=8) (actual
time=330.796..330.797 rows=1 loops=3)
Buffers: shared read=66578
-> Parallel Bitmap Heap Scan on t_pay_all (cost=19802.01..88689.92 rows=341528
width=0) (actual time=124.126..303.125 rows=274955 loops=3)
Recheck Cond: ((createdate >= '2017-06-01'::date) AND (createdate <
'2017-07-01'::date))
Heap Blocks: exact=25882
Buffers: shared read=66578
-> Bitmap Index Scan on t_pay_all_createdate_idx (cost=0.00..19597.10
rows=819666 width=0) (actual time=111.233..111.233 rows=825865 loops=1)
Index Cond: ((createdate >= '2017-06-01'::date) AND (createdate <
'2017-07-01'::date))
Buffers: shared read=2877
Planning time: 0.213 ms
Execution time: 344.013 ms
(17 rows)
Time: 344.759 ms
postgres=# －－分區(qū)表
postgres=# explain (analyze,buffers)select * from t_pay where createdate >='2017-06-01' AND
createdate<'2017-07-01';
QUERY PLAN
-------------------------------------------------------------------------------------------
Append (cost=0.00..17633.97 rows=824865 width=20) (actual time=0.020..272.926 rows=824865
loops=1)
Buffers: shared hit=5261
-> Seq Scan on t_pay_201706 (cost=0.00..17633.97 rows=824865 width=20) (actual
time=0.019..170.128 rows=824865 loops=1)
Filter: ((createdate >= '2017-06-01'::date) AND (createdate < '2017-07-01'::date))
Buffers: shared hit=5261
Planning time: 0.779 ms
Execution time: 335.351 ms
(7 rows)
Time: 336.676 ms
postgres=# explain (analyze,buffers)select count(1) from t_pay where createdate >='2017-06-01'
AND createdate<'2017-07-01';
QUERY PLAN
--------------------------------------------------------------------------------------------
Finalize Aggregate (cost=12275.86..12275.87 rows=1 width=8) (actual time=144.023..144.023
rows=1 loops=1)
Buffers: shared hit=5429
-> Gather (cost=12275.64..12275.85 rows=2 width=8) (actual time=143.966..144.016 rows=3
loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=5429
-> Partial Aggregate (cost=11275.64..11275.65 rows=1 width=8) (actual
time=140.230..140.230 rows=1 loops=3)
Buffers: shared hit=5261
-> Append (cost=0.00..10416.41 rows=343694 width=0) (actual time=0.022..106.973
rows=274955 loops=3)
Buffers: shared hit=5261
-> Parallel Seq Scan on t_pay_201706 (cost=0.00..10416.41 rows=343694
width=0) (actual time=0.020..68.952 rows=274955 loops=3)
Filter: ((createdate >= '2017-06-01'::date) AND (createdate <
'2017-07-01'::date))
Buffers: shared hit=5261
Planning time: 0.760 ms
Execution time: 145.289 ms
(15 rows)
Time: 146.610 ms在同一個分區(qū)內(nèi)查詢優(yōu)勢明顯
2、不在同一個分區(qū)內(nèi)
－－非分區(qū)表
postgres=# explain (analyze,buffers)select count(1) from t_pay_all where createdate >='2017-06-01'
AND createdate<'2017-12-01';
QUERY PLAN
-------------------------------------------------------------------------------------------
Finalize Aggregate (cost=132593.42..132593.43 rows=1 width=8) (actual time=717.848..717.848
rows=1 loops=1)
Buffers: shared hit=33571 read=30446 dirtied=9508 written=4485
-> Gather (cost=132593.20..132593.41 rows=2 width=8) (actual time=717.782..717.841 rows=3
loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=33571 read=30446 dirtied=9508 written=4485
-> Partial Aggregate (cost=131593.20..131593.21 rows=1 width=8) (actual
time=714.096..714.097 rows=1 loops=3)
Buffers: shared hit=33319 read=30446 dirtied=9508 written=4485
-> Parallel Seq Scan on t_pay_all (cost=0.00..126330.64 rows=2105024 width=0)
(actual time=0.059..545.016 rows=1675464 loops=3)
Filter: ((createdate >= '2017-06-01'::date) AND (createdate <
'2017-12-01'::date))
Rows Removed by Filter: 1661203
Buffers: shared hit=33319 read=30446 dirtied=9508 written=4485
Planning time: 0.178 ms
Execution time: 721.822 ms
(14 rows)
Time: 722.521 ms－－分區(qū)表
postgres=# explain (analyze,buffers)select count(1) from t_pay where createdate >='2017-06-01'
AND createdate<'2017-12-01';
QUERY PLAN
------------------------------------------------------------------------------------------
Finalize Aggregate (cost=69675.98..69675.99 rows=1 width=8) (actual time=714.560..714.560 rows=1
loops=1)
Buffers: shared hit=27002 read=5251
-> Gather (cost=69675.77..69675.98 rows=2 width=8) (actual time=714.426..714.551 rows=3
loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=27002 read=5251
-> Partial Aggregate (cost=68675.77..68675.78 rows=1 width=8) (actual
time=710.416..710.416 rows=1 loops=3)
Buffers: shared hit=26774 read=5251
-> Append (cost=0.00..63439.94 rows=2094330 width=0) (actual time=0.023..536.033
rows=1675464 loops=3)
Buffers: shared hit=26774 read=5251
-> Parallel Seq Scan on t_pay_201706 (cost=0.00..10416.41 rows=343694
width=0) (actual time=0.021..67.935 rows=274955 loops=3)
Filter: ((createdate >= '2017-06-01'::date) AND (createdate <
'2017-12-01'::date))
Buffers: shared hit=5261
-> Parallel Seq Scan on t_pay_201707 (cost=0.00..10728.06 rows=354204
width=0) (actual time=0.007..54.999 rows=283363 loops=3)
Filter: ((createdate >= '2017-06-01'::date) AND (createdate <
'2017-12-01'::date))
Buffers: shared hit=5415
-> Parallel Seq Scan on t_pay_201708 (cost=0.00..10744.08 rows=354738
width=0) (actual time=0.007..55.117 rows=283791 loops=3)
Filter: ((createdate >= '2017-06-01'::date) AND (createdate <
'2017-12-01'::date))
Buffers: shared hit=5423
-> Parallel Seq Scan on t_pay_201709 (cost=0.00..10410.71 rows=343714
width=0) (actual time=0.007..53.402 rows=274971 loops=3)
Filter: ((createdate >= '2017-06-01'::date) AND (createdate <
'2017-12-01'::date))
Buffers: shared hit=5255
-> Parallel Seq Scan on t_pay_201710 (cost=0.00..10737.41 rows=354494
width=0) (actual time=0.007..55.475 rows=283595 loops=3)
Filter: ((createdate >= '2017-06-01'::date) AND (createdate <
'2017-12-01'::date))
Buffers: shared hit=5420
-> Parallel Seq Scan on t_pay_201711 (cost=0.00..10403.29 rows=343486
width=0) (actual time=0.036..57.635 rows=274789 loops=3)
Filter: ((createdate >= '2017-06-01'::date) AND (createdate <
'2017-12-01'::date))
Buffers: shared read=5251
Planning time: 1.217 ms
Execution time: 718.372 ms
(30 rows)跨分區(qū)查詢，大約在跨一半分區(qū)時性能相當(dāng)。
查詢某個月里某個用戶數(shù)據(jù)－－直接從cache里取數(shù)據(jù)
1、數(shù)據(jù)都落在所在分區(qū)，并且數(shù)據(jù)量極少
－－非分區(qū)表
postgres=# explain (analyze,buffers) select * from t_pay_all where createdate>='2017-06-01'
AND createdate<'2017-07-01' and userid=268460;
QUERY PLAN
--------------------------------------------------------------------------------------------
Index Scan using t_pay_all_userid_idx on t_pay_all (cost=0.43..48.68 rows=1 width=20)
(actual time=0.053..0.071 rows=7 loops=1)
Index Cond: (userid = 268460)
Filter: ((createdate >= '2017-06-01'::date) AND (createdate < '2017-07-01'::date))
Rows Removed by Filter: 10
Buffers: shared hit=20
Planning time: 0.149 ms
Execution time: 0.101 ms
(7 rows)
Time: 0.676 ms－－分區(qū)表
postgres=# explain (analyze,buffers) select * from t_pay where createdate >='2017-06-01'
AND createdate<'2017-07-01' and userid=268460;
QUERY PLAN
------------------------------------------------------------------------------------------
Append (cost=0.42..12.47 rows=2 width=20) (actual time=0.019..0.032 rows=7 loops=1)
Buffers: shared hit=10
-> Index Scan using t_pay_201706_userid_idx on t_pay_201706 (cost=0.42..12.47 rows=2 width=20)
(actual time=0.018..0.029 rows=7 loops=1)
Index Cond: (userid = 268460)
Filter: ((createdate >= '2017-06-01'::date) AND (createdate < '2017-07-01'::date))
Buffers: shared hit=10
Planning time: 0.728 ms
Execution time: 0.064 ms
(8 rows)
Time: 1.279 ms在返回記錄極少的情況下由于分布表的Planning time開銷較大，所以非分區(qū)表有優(yōu)勢
2、數(shù)據(jù)落在其它分區(qū)，并且數(shù)據(jù)量比較大
－－非分區(qū)表
postgres=# explain (analyze,buffers) select * from t_pay_all where createdate >='2017-06-01'
AND createdate<'2017-07-01' and userid=302283 ;
QUERY PLAN
---------------------------------------------------------------------------------------------
Bitmap Heap Scan on t_pay_all (cost=19780.69..22301.97 rows=683 width=20) (actual
time=91.778..91.803 rows=2 loops=1)
Recheck Cond: ((userid = 302283) AND (createdate >= '2017-06-01'::date) AND (createdate < '2017-07-01'::date))
Heap Blocks: exact=9
Buffers: shared hit=2927
-> BitmapAnd (cost=19780.69..19780.69 rows=683 width=0) (actual time=91.767..91.767 rows=0 loops=1)
Buffers: shared hit=2918
-> Bitmap Index Scan on t_pay_all_userid_idx (cost=0.00..183.00 rows=8342 width=0)
(actual time=0.916..0.916 rows=11013 loops=1)
Index Cond: (userid = 302283)
Buffers: shared hit=41
-> Bitmap Index Scan on t_pay_all_createdate_idx (cost=0.00..19597.10 rows=819666
width=0) (actual time=90.837..90.837 rows=825865 loops=1)
Index Cond: ((createdate >= '2017-06-01'::date) AND (createdate < '2017-07-01'::date))
Buffers: shared hit=2877
Planning time: 0.172 ms
Execution time: 91.851 ms
(14 rows)
Time: 92.534 ms－－分區(qū)表
postgres=# explain (analyze,buffers) select * from t_pay where createdate >='2017-06-01'
AND createdate<'2017-07-01' and userid=302283 ;
QUERY PLAN
-------------------------------------------------------------------------------------------
Append (cost=0.42..12.47 rows=2 width=20) (actual time=0.042..0.046 rows=2 loops=1)
Buffers: shared hit=7
-> Index Scan using t_pay_201706_userid_idx on t_pay_201706 (cost=0.42..12.47 rows=2 width=20)
(actual time=0.041..0.045 rows=2 loops=1)
Index Cond: (userid = 302283)
Filter: ((createdate >= '2017-06-01'::date) AND (createdate < '2017-07-01'::date))
Buffers: shared hit=7
Planning time: 0.818 ms
Execution time: 0.096 ms
(8 rows)
Time: 1.499 ms這是分區(qū)表最大的優(yōu)勢體現(xiàn)了，性能提升不是一般的大
索引維護(hù)
－－非分區(qū)表
postgres=# REINDEX INDEX t_pay_all_createdate_idx; REINDEX Time: 11827.344 ms (00:11.827)
－－分區(qū)表
postgres=# REINDEX INDEX t_pay_201706_createdate_idx; REINDEX Time: 930.439 ms postgres=#
這個也是分區(qū)表的優(yōu)勢，可以針對某個分區(qū)的索引進(jìn)行重建。
刪除整個分區(qū)數(shù)據(jù)
－－非分區(qū)表
postgres=# delete from t_pay_all where createdate >='2017-06-01' and createdate<'2017-07-01'; DELETE 824865 Time: 5775.545 ms (00:05.776)
－－分區(qū)表
postgres=# truncate table t_pay_201706; TRUNCATE TABLE Time: 177.809 ms
個也是分區(qū)表的優(yōu)勢，可以對某個分區(qū)直接truncate
全表掃描
－－非分區(qū)表
postgres=# explain analyze select count(1) from t_pay;
QUERY PLAN
---------------------------------------------------------------------------------------------
Finalize Aggregate (cost=107370.96..107370.97 rows=1 width=8) (actual time=971.561..971.561 rows=1 loops=1)
-> Gather (cost=107370.75..107370.96 rows=2 width=8) (actual time=971.469..971.555 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=106370.75..106370.76 rows=1 width=8) (actual
time=967.378..967.378 rows=1 loops=3)
-> Append (cost=0.00..96800.40 rows=3828141 width=0) (actual time=0.019..698.882 rows=3061712 loops=3)
-> Parallel Seq Scan on t_pay_201701 (cost=0.00..8836.14 rows=349414
width=0) (actual time=0.017..48.716 rows=279531 loops=3)
-> Parallel Seq Scan on t_pay_201702 (cost=0.00..8119.94 rows=321094
width=0) (actual time=0.007..33.072 rows=256875 loops=3)
-> Parallel Seq Scan on t_pay_201703 (cost=0.00..9079.47 rows=359047
width=0) (actual time=0.006..37.153 rows=287238 loops=3)
-> Parallel Seq Scan on t_pay_201704 (cost=0.00..8672.67 rows=342968
width=0) (actual time=0.006..35.317 rows=274374 loops=3)
-> Parallel Seq Scan on t_pay_201705 (cost=0.00..8975.23 rows=354923
width=0) (actual time=0.006..36.571 rows=283938 loops=3)
-> Parallel Seq Scan on t_pay_201706 (cost=0.00..20.00 rows=1000 width=0)
(actual time=0.000..0.000 rows=0 loops=3)
-> Parallel Seq Scan on t_pay_201707 (cost=0.00..8957.04 rows=354204
width=0) (actual time=0.006..36.393 rows=283363 loops=3)
-> Parallel Seq Scan on t_pay_201708 (cost=0.00..8970.38 rows=354738
width=0) (actual time=0.006..37.015 rows=283791 loops=3)
-> Parallel Seq Scan on t_pay_201709 (cost=0.00..8692.14 rows=343714
width=0) (actual time=0.006..35.187 rows=274971 loops=3)
-> Parallel Seq Scan on t_pay_201710 (cost=0.00..8964.94 rows=354494
width=0) (actual time=0.006..36.566 rows=283595 loops=3)
-> Parallel Seq Scan on t_pay_201711 (cost=0.00..8685.86 rows=343486
width=0) (actual time=0.006..35.198 rows=274789 loops=3)
-> Parallel Seq Scan on t_pay_201712 (cost=0.00..8826.59 rows=349059
width=0) (actual time=0.006..36.523 rows=279247 loops=3)
Planning time: 0.706 ms
Execution time: 977.364 ms
(20 rows)
Time: 978.705 ms
postgres=# －－分區(qū)表
postgres=# explain analyze select count(1) from t_pay_all;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=116900.63..116900.64 rows=1 width=8) (actual time=644.093..644.093
rows=1 loops=1)
-> Gather (cost=116900.42..116900.63 rows=2 width=8) (actual time=644.035..644.087 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=115900.42..115900.43 rows=1 width=8) (actual
time=640.587..640.587 rows=1 loops=3)
-> Parallel Seq Scan on t_pay_all (cost=0.00..105473.33 rows=4170833 width=0)
(actual time=0.344..371.965 rows=3061712 loops=3)
Planning time: 0.164 ms
Execution time: 645.438 ms
(8 rows)
Time: 646.027 ms全掃描時分區(qū)表落后，但還基本上能接收。
增加新的分區(qū)并導(dǎo)入數(shù)據(jù)
－－生成新的分區(qū)數(shù)據(jù)
copy (select userid,pay_money,createdate+31 as createdate from t_pay_201712) to '/home/pg/201801.txt';
－－建立新的分區(qū)
create table t_pay_201801 partition of t_pay(id primary key,userid,pay_money,createdate) for
values from ('2018-01-01') to ('2018-02-01');
create index t_pay_201801_createdate_idx on t_pay_201801 using btree(createdate);
create index t_pay_201801_userid_idx on t_pay_201801 using btree(userid); －－非分區(qū)表
postgres=# copy t_pay_all(userid,pay_money,createdate) from '/home/pg/201801.txt'; COPY 837741 Time: 18105.024 ms (00:18.105)
－－分區(qū)表
postgres=# copy t_pay(userid,pay_money,createdate) from '/home/pg/201801.txt'; COPY 837741 Time: 13864.950 ms (00:13.865) postgres=#
新的分區(qū)數(shù)據(jù)導(dǎo)入保持優(yōu)勢
到此這篇關(guān)于PostgreSQL 10分區(qū)表詳解及性能測試報告的文章就介紹到這了,更多相關(guān)PostgreSQL 10分區(qū)表性能測試內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Postgresql源碼分析returns?setof函數(shù)oracle管道pipelined
這篇文章主要為大家介紹了Postgresql源碼分析returns?setof函數(shù)oracle管道pipelined，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-01-01
如何修改Postgresql默認(rèn)賬號postgres的密碼
PostgreSQL數(shù)據(jù)庫創(chuàng)建一個postgres用戶作為數(shù)據(jù)庫的管理員,密碼隨機,所以需要修改密碼,這篇文章主要給大家介紹了關(guān)于如何修改Postgresql默認(rèn)賬號postgres的密碼,需要的朋友可以參考下2023-10-10
PostgreSQL數(shù)據(jù)庫備份與恢復(fù)的四種辦法
在數(shù)據(jù)為王的時代,數(shù)據(jù)庫中存儲的信息堪稱企業(yè)的生命線,而PostgreSQL作為一款廣泛應(yīng)用的開源數(shù)據(jù)庫,學(xué)會如何妥善進(jìn)行備份與恢復(fù)操作,是每個開發(fā)者與運維人員必備的技能,今天,咱們就深入探究一下PostgreSQL相關(guān)的備份恢復(fù)策略,并附上豐富的代碼示例2025-01-01
PostgreSQL數(shù)據(jù)庫時間類型相加減操作
PostgreSQL提供了許多函數(shù),這些函數(shù)返回與當(dāng)前日期和時間相關(guān)的值,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL數(shù)據(jù)庫時間類型相加減操作的相關(guān)資料,需要的朋友可以參考下2023-10-10
PostgreSQL實現(xiàn)交叉表（行列轉(zhuǎn)換）的5種方法示例
這篇文章主要給大家介紹了關(guān)于PostgreSQL實現(xiàn)交叉表（行列轉(zhuǎn)換）的5種方法，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-08-08
盡管PostgreSQL中支持大量的數(shù)據(jù)類型，但是對于URL似乎并沒有一個相應(yīng)的類型能夠去存儲。那么對于URL的數(shù)據(jù)我們在數(shù)據(jù)庫中要怎么去處理呢？今天通過本文給大家介紹下，需要的朋友參考下吧2021-07-07
這篇文章主要介紹了基于postgresql行級鎖for update測試，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了PostgreSQL去掉表中所有不可見字符的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
PostgreSQL TRUNCATE TABLE命令的使用
PostgreSQL的TRUNCATE TABLE命令是一種高效刪除表中所有數(shù)據(jù)的方法,相比DELETE快數(shù)十到數(shù)百倍,本文就來詳細(xì)的介紹一下該命令的使用,感興趣的可以了解一下2025-11-11
PostgreSQL?pg_filenode.map文件介紹
這篇文章主要介紹了PostgreSQL誤刪pg_filenode.map怎么辦，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧2022-09-09