PostgreSQL和PPAS的分区表及多种条件下的性能体现一

概述因某项目测了PPAS和PostgreSQL的分区表在where条件里按分区键、函数、子查询等11中不同情况时的性能体现，两者基本一致，只有在in关键字和to_number函数的情况下不同，in关键字在PPAS中只扫描对应的子表，Postgres里做全表扫描；to_number函数在PostgreSQL中是没有的，因此报错，因为PPAS有兼容oracle引擎，所以没问题，走相应子表扫描。 相同的情况

因某项目测了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

总结

以上是内存溢出为你收集整理的PostgreSQL和PPAS的分区表及多种条件下的性能体现一全部内容，希望文章能够帮你解决PostgreSQL和PPAS的分区表及多种条件下的性能体现一所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。