因某项目测了PPAS和PostgreSQL的分区表在where条件里按分区键、函数、子查询等11中不同情况时的性能体现，两者基本一致，只有在in关键字和to_number函数的情况下不同，in关键字在PPAS中只扫描对应的子表，Postgres里做全表扫描；to_number函数在PostgreSQL中是没有的，因此报错，因为PPAS有兼容oracle引擎，所以没问题，走相应子表扫描。
相同的情况有：
按分区列值查询,只查询对应分区表
按分区列值做范围查询,只查询对应分区表
按分区列值和其它列查询,只查询对应分区表
按分区列值查询,值有显式类型转换,只查询对应分区表
按分区列值查询,值和列类型不同,值有隐式类型转换,只查询对应分区表
按分区列值查询,列要做隐式类型转换,走全表扫描
按分区列值查询,值使用了函数,走分区表索引扫描
按分区列值查询,值使用了子查询,用等号走全表扫描
按分区列值 更新,走分区表索引扫描
按分区列值 删除,走分区表索引扫描
具体情况见下面：
下面是pg中的过程
1 创建分区表
1.1 主表/子表继承
create table test (id integer primary key, name varchar(32));
CREATE TABLE t1_1000(LIKE test INCLUDING all) INHERITS(test);
CREATE TABLE t1001_2000(LIKE test INCLUDING all) INHERITS(test);
CREATE TABLE t2001_3000(LIKE test INCLUDING all) INHERITS(test);
CREATE TABLE t_other(LIKE test INCLUDING all) INHERITS(test);
ALTER TABLE t1_1000 ADD CONSTRAINT t1_1000_check CHECK (id >= 1 and id <1001);
ALTER TABLE t1001_2000 ADD CONSTRAINT t1_1000_check CHECK (id >= 1001 and id <2001);
ALTER TABLE t2001_3000 ADD CONSTRAINT t1_1000_check CHECK (id >= 2001 and id <3001);
ALTER TABLE t_other ADD CONSTRAINT t_other CHECK (id <= 0 or id >=3001);
1.2 定义触发器函数
CREATE OR REPLACE FUNCTION test_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.id >= 1 and NEW.id<1001) THEN
INSERT INTO t1_1000 VALUES (NEW.*);
ELSeIF ( NEW.id >= 1001 and NEW.id<2001) THEN
INSERT INTO t1001_2000 VALUES (NEW.*);
ELSeIF ( NEW.id >= 2001 and NEW.id<3001) THEN
INSERT INTO t2001_3000 VALUES (NEW.*);
ELSE
INSERT INTO t_other VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION test_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( old.id >= 1 and old.id<1001 ) THEN
DELETE FROM t1_1000 WHERE id=old.id;
ELSIF ( old.id >= 1001 and old.id<2001) THEN
DELETE FROM t1001_2000 WHERE id=old.id;
ELSIF ( old.id >= 2001 and old.id<3001 ) THEN
DELETE FROM t2001_3000 WHERE id=old.id;
ELSE
DELETE FROM t_other WHERE id=old.id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
1.3 加触发器
CREATE TRIGGER insert_test_trigger
BEFORE INSERT ON test
FOR EACH ROW EXECUTE PROCEDURE test_insert_trigger();
CREATE TRIGGER delete_test_trigger
BEFORE DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE test_delete_trigger();
2
给表中插入值时自动根据ID值插入到分区表中
beigang=# INSERT INTO test(id, name)VALUES (6, 'ertr');
INSERT 0 0
beigang=#
beigang=# select * from test;
id | name
----+------
6 | ertr
(1 row)
beigang=#
beigang=# select count(*) from only test;
count
-------
0
(1 row)
beigang=# select count(*) from only t1_1000;
count
-------
1
(1 row)
^
beigang=#
beigang=# select count(*) from only t1001_2000;
count
-------
0
(1 row)
beigang=#
3
从父表中删除该值
beigang=# delete from test where id=6;
DELETE 1
beigang=#
beigang=# select count(*) from only t1_1000;
count
-------
0
(1 row)
4
批量插入值
beigang=# insert into test select generate_series(1,2600),'abc';
INSERT 0 0
beigang=#
beigang=# select count(*) from test;
count
-------
2600
(1 row)
beigang=# select count(*) from only test;
count
-------
0
(1 row)
beigang=# select count(*) from only t2001_3000;
count
-------
600
(1 row)
5
查询
5.1
按分区列值查询,只查询对应分区表
beigang=# explain select * from test where id=200;
QUERY PLAN
---------------------------------------------------------------------------------------------
Result (cost=0.00..8.27 rows=2 width=47)
-> Append (cost=0.00..8.27 rows=2 width=47)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: (id = 200)
-> Index Scan using t1_1000_pkey on t1_1000 test (cost=0.00..8.27 rows=1 width=8)
Index Cond: (id = 200)
(6 rows)
5.2
按分区列值做范围查询,只查询对应分区表
beigang=# explain select * from test where id<200 and id>100;
QUERY PLAN
------------------------------------------------------------------------------------------------
Result (cost=0.00..10.25 rows=101 width=9)
-> Append (cost=0.00..10.25 rows=101 width=9)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: ((id < 200) AND (id > 100))
-> Index Scan using t1_1000_pkey on t1_1000 test (cost=0.00..10.25 rows=100 width=8)
Index Cond: ((id < 200) AND (id > 100))
(6 rows)
beigang=# explain select * from test where id<700 and id>100;
QUERY PLAN
---------------------------------------------------------------------------
Result (cost=0.00..20.00 rows=601 width=8)
-> Append (cost=0.00..20.00 rows=601 width=8)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: ((id < 700) AND (id > 100))
-> Seq Scan on t1_1000 test (cost=0.00..20.00 rows=600 width=8)
Filter: ((id < 700) AND (id > 100))
(6 rows)
beigang=#
beigang=# explain select * from test where id<1100 and id>900;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Result (cost=0.00..20.50 rows=201 width=8)
-> Append (cost=0.00..20.50 rows=201 width=8)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: ((id < 1100) AND (id > 900))
-> Index Scan using t1_1000_pkey on t1_1000 test (cost=0.00..10.25 rows=100 width=8)
Index Cond: ((id < 1100) AND (id > 900))
-> Index Scan using t1001_2000_pkey on t1001_2000 test (cost=0.00..10.25 rows=100 width=8)
Index Cond: ((id < 1100) AND (id > 900))
(8 rows)
5.3
按分区列值和其它列查询,只查询对应分区表
beigang=# explain select * from test where id=300 and name='ccc';
QUERY PLAN
---------------------------------------------------------------------------------------------
Result (cost=0.00..8.27 rows=2 width=47)
-> Append (cost=0.00..8.27 rows=2 width=47)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: ((id = 300) AND ((name)::text = 'ccc'::text))
-> Index Scan using t1_1000_pkey on t1_1000 test (cost=0.00..8.27 rows=1 width=8)
Index Cond: (id = 300)
Filter: ((name)::text = 'ccc'::text)
(7 rows)
5.4
按分区列值查询,值有显式类型转换,只查询对应分区表
beigang=# explain select * from test where id='5'::int;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Result (cost=0.00..5.27 rows=2 width=47)
-> Append (cost=0.00..5.27 rows=2 width=47)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: (id = 5)
-> Index Scan using test_t1_1000_pkey on test_t1_1000 test (cost=0.00..5.27 rows=1 width=8)
Index Cond: (id = 5)
(6 rows)
5.5
按分区列值查询,值和列类型不同,值有隐式类型转换,只查询对应分区表
beigang=# explain select * from test where id='5';
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Result (cost=0.00..5.27 rows=2 width=47)
-> Append (cost=0.00..5.27 rows=2 width=47)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: (id = 5)
-> Index Scan using test_t1_1000_pkey on test_t1_1000 test (cost=0.00..5.27 rows=1 width=8)
Index Cond: (id = 5)
(6 rows)
5.6
按分区列值查询,列要做隐式类型转换,走全表扫描
beigang=# explain select * from test where id || name ='5abc';
QUERY PLAN
----------------------------------------------------------------------------
Result (cost=0.00..89.00 rows=18 width=30)
-> Append (cost=0.00..89.00 rows=18 width=30)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: (((id)::text || (name)::text) = '5abc'::text)
-> Seq Scan on t1_1000 test (cost=0.00..25.00 rows=5 width=8)
Filter: (((id)::text || (name)::text) = '5abc'::text)
-> Seq Scan on t1001_2000 test (cost=0.00..25.00 rows=5 width=8)
Filter: (((id)::text || (name)::text) = '5abc'::text)
-> Seq Scan on t2001_3000 test (cost=0.00..15.00 rows=3 width=8)
Filter: (((id)::text || (name)::text) = '5abc'::text)
-> Seq Scan on t_other test (cost=0.00..24.00 rows=4 width=86)
Filter: (((id)::text || (name)::text) = '5abc'::text)
(12 rows)
5.7
按分区列值查询,值使用了函数,pg没有to_number函数，报错
beigang=# explain select * from test where id=to_number('1');
ERROR: function to_number(unknown) does not exist
LINE 1: explain select * from test where id=to_number('1');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
5.8
按分区列值查询,值使用了函数,走分区表索引扫描
beigang=# explain select * from test where id=cast('1' as int);
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Result (cost=0.00..5.27 rows=2 width=47)
-> Append (cost=0.00..5.27 rows=2 width=47)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: (id = 1)
-> Index Scan using test_t1_1000_pkey on test_t1_1000 test (cost=0.00..5.27 rows=1 width=8)
Index Cond: (id = 1)
(6 rows)
beigang=#
5.9
按分区列值查询,值使用了子查询,用等号走全表扫描,用in走分区表索引扫描
beigang=# explain select * from test where id in(select 1 );
QUERY PLAN
---------------------------------------------------------------------------------------------------
Nested Loop (cost=0.02..41.43 rows=1650 width=25)
Join Filter: (public.test.id = (1))
-> HashAggregate (cost=0.02..0.03 rows=1 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Append (cost=0.00..41.34 rows=5 width=39)
-> Index Scan using test_pkey on test (cost=0.00..8.27 rows=1 width=86)
Index Cond: (id = (1))
-> Index Scan using t1_1000_pkey on t1_1000 test (cost=0.00..8.27 rows=1 width=8)
Index Cond: (id = (1))
-> Index Scan using t1001_2000_pkey on t1001_2000 test (cost=0.00..8.27 rows=1 width=8)
Index Cond: (id = (1))
-> Index Scan using t2001_3000_pkey on t2001_3000 test (cost=0.00..8.27 rows=1 width=8)
Index Cond: (id = (1))
-> Index Scan using t_other_pkey on t_other test (cost=0.00..8.27 rows=1 width=86)
Index Cond: (id = (1))
(15 rows)
beigang=# explain select * from test where id =(select 1 );
QUERY PLAN
---------------------------------------------------------------------------------------------------
Result (cost=0.01..33.08 rows=5 width=39)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Append (cost=0.00..33.07 rows=5 width=39)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: (id = $0)
-> Index Scan using t1_1000_pkey on t1_1000 test (cost=0.00..8.27 rows=1 width=8)
Index Cond: (id = $0)
-> Index Scan using t1001_2000_pkey on t1001_2000 test (cost=0.00..8.27 rows=1 width=8)
Index Cond: (id = $0)
-> Index Scan using t2001_3000_pkey on t2001_3000 test (cost=0.00..8.27 rows=1 width=8)
Index Cond: (id = $0)
-> Index Scan using t_other_pkey on t_other test (cost=0.00..8.27 rows=1 width=86)
Index Cond: (id = $0)
(14 rows)
5.10
按分区列值 更新,走分区表索引扫描
beigang=# explain update test set name = 'bbb' where id=99;
QUERY PLAN
----------------------------------------------------------------------------------------
Update on test (cost=0.00..8.27 rows=2 width=10)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=10)
Filter: (id = 99)
-> Index Scan using t1_1000_pkey on t1_1000 test (cost=0.00..8.27 rows=1 width=10)
Index Cond: (id = 99)
(5 rows)
5.11
按分区列值 删除,走分区表索引扫描
beigang=# explain delete from test where id=99;
QUERY PLAN
---------------------------------------------------------------------------------------
Delete on test (cost=0.00..8.27 rows=2 width=6)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=6)
Filter: (id = 99)
-> Index Scan using t1_1000_pkey on t1_1000 test (cost=0.00..8.27 rows=1 width=6)
Index Cond: (id = 99)
(5 rows)
-----------------
转载请著明出处：
blog.csdn.net/beiigang
beigang.iteye.com
相关推荐
项目中有需求要垂直分表，即按照时间区间将数据拆分到n个表中，PostgreSQL提供了分区表的功能。分区表实际上是把逻辑上的一个大表分割成物理上的几小块，提供了很多好处，比如： 1、查询性能大幅提升 2、删除历史...
PostgreSQL表分区和子表及删除所有的数据库表 最近需求要求统计DNS近7天每天的解析情况。数据量相对大，所以我这边对表进行分区。 对每天的数据进行分区存储。主表只存储近7天的数据，7天之前的数据删掉。所以我...
实现轻松创建PostgreSQL的分区表DDL
PostgreSQL分区表实践与思考.pptx
从这里可以看出PostgreSQL表...通过作用于主表的触发器，把主表的修改重定向到适当的分区表等工作，分区的创建和管理都需要我们用语言来控制，增加了开发人员的工作量，PostgreSQL表分区的所有表结构和表分区及触发器
PostgreSQL表分区功能演进 PostgreSQL表分区功能演进 PostgreSQL表分区功能演进 PostgreSQL表分区功能演进
PostgreSql数据库分区表.html
在postgresql10之后，引入了内置分区表，用户不需要先在父表上定义insert,update,delete触发器，对父表的DML操作会自动路由到相应分区，相比传统分区表大幅度降低了维护成本，目前内置分区表仅支持范围分区和列表...
创建表分区步骤如下： 1. 创建主表 CREATE TABLE users ( uid int not null primary key, name varchar(20)); 2. 创建分区表(必须继承上面的主表) CREATE TABLE users_0 ( check (uid >= 0 and uid< 100>= 100)) ...
PostgreSQL表的继承和分区
2014年PostgreSQL中国大会华为许中清分享的原生表分区方案
PostgreSQL表分区功能演进_8
PostgreSQL Partitioning 表分区
斗鱼 PostgreSQL分区表实践与思考 斗鱼 PostgreSQL分区表实践与思考 斗鱼 PostgreSQL分区表实践与思考 斗鱼 PostgreSQL分区表实践与思考
pg_pathman 是一个 PostgreSQL 高性能表分区插件。支持 HASH 分区、RANGE 分区以及自动扩容分区。可通过内建函数挂载、摘除和分区。
MySQL和PostgreSQL的各种性能比较
PostgreSQL PostgreSQL PostgreSQL学习手册 学习手册 学习手册 (表的继承和分区 表的继承和分区 表的继承和分区 表的继承和分区 ) 10 一、表的继承： 一、表的继承： 一、表的继承： .10 PostgreSQL PostgreSQL ...
如果在按时间分区的表中，大多数查询发生在时间最近的一个分区或几个分区中，而较早时间分区比较少查询，那么，在建分区表后各个分区表会有各自的索引，使用率较高的分区表