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