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

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了PostgreSQL和PPAS的分区表及多种条件下的性能体现二，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

PPAS中的分区表可以按oracle兼容的语法创建，具体使用请参见《Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide_v91.pdf》。
下面是分区表上操作的相关情况

1
创建表:
create table test (id integer primary key,name varchar(32))
PARTITION BY RANGE (id)
(PARTITION t1_1000 VALUES LESS THAN(1001),
PARTITION t1001_2000 VALUES LESS THAN(2001),
PARTITION t2001_3000 VALUES LESS THAN(3001));

1.1
从数据库取的表定义
-- Table: test
-- DROP TABLE test;
CREATE TABLE test
(
id integer NOT NULL,
name character varying(32),
CONSTRAINT test_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE test
OWNER TO enterprisedb;


-- Table: test_t1_1000
-- DROP TABLE test_t1_1000;
CREATE TABLE test_t1_1000
(
-- Inherited from table test: id integer NOT NULL,
-- Inherited from table test: name character varying(32),
CONSTRAINT test_t1_1000_pkey PRIMARY KEY (id),
CONSTRAINT test_t1_1000_partition CHECK (id < 1001)
)
INHERITS (test)
WITH (
OIDS=FALSE
);
ALTER TABLE test_t1_1000
OWNER TO enterprisedb;

-- Table: test_t1001_2000
-- DROP TABLE test_t1001_2000;
CREATE TABLE test_t1001_2000
(
-- Inherited from table test: id integer NOT NULL,
CONSTRAINT test_t1001_2000_pkey PRIMARY KEY (id),
CONSTRAINT test_t1001_2000_partition CHECK (id >= 1001 AND id < 2001)
)
INHERITS (test)
WITH (
OIDS=FALSE
);
ALTER TABLE test_t1001_2000
OWNER TO enterprisedb;


-- Table: test_t2001_3000
-- DROP TABLE test_t2001_3000;
CREATE TABLE test_t2001_3000
(
-- Inherited from table test: id integer NOT NULL,
CONSTRAINT test_t2001_3000_pkey PRIMARY KEY (id),
CONSTRAINT test_t2001_3000_partition CHECK (id >= 2001 AND id < 3001)
)
INHERITS (test)
WITH (
OIDS=FALSE
);
ALTER TABLE test_t2001_3000
OWNER TO enterprisedb;

2
给表中插入值时自动根据ID值插入到分区表中
edbtest=# INSERT INTO test(id,name)VALUES (6,'ertr');
INSERT 0 0

edbtest=# select * from test;
id | name
----+------
6 | ertr
(1 row)

edbtest=#
edbtest=# select count(*) from only test;
count
-------
0
(1 row)

edbtest=# select count(*) from only test_t1_1000;
count
-------
1
(1 row)
^
edbtest=#
edbtest=# select count(*) from only test_t1001_2000;
count
-------
0
(1 row)

edbtest=#

3
从父表中删除该值
edbtest=# delete from test where id=6;
DELETE 1
edbtest=#
edbtest=# select count(*) from only test_t1_1000;
count
-------
0
(1 row)

4
批量插入值
edbtest=#insert into test select generate_series(1,2600),'abc';
INSERT 0 0
edbtest=#
edbtest=# select count(*) from test;
count
-------
2600
(1 row)

edbtest=# select count(*) from only test;
count
-------
0
(1 row)

edbtest=# select count(*) from only test_t2001_3000;
count
-------
600
(1 row)

edbtest=#

5
查询

5.1
按分区列值查询,只查询对应分区表
edbtest=# explain select * from test where id=200;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Result (cost=0.00..7.27 rows=2 width=47)
-> Append (cost=0.00..7.27 rows=2 width=47)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: (id = 200)
-> Index Scan using test_t1_1000_pkey on test_t1_1000 test (cost=0.00..7.27 rows=1 width=8)
Index Cond: (id = 200)
(6 rows)

5.2
按分区列值做范围查询,只查询对应分区表
edbtest=# explain select * from test where id<200 and id>100;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Result (cost=0.00..9.25 rows=101 width=9)
-> Append (cost=0.00..9.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 test_t1_1000_pkey on test_t1_1000 test (cost=0.00..9.25 rows=100 width=8)
Index Cond: ((id < 200) AND (id > 100))
(6 rows)

edbtest=#

edbtest=# 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 test_t1_1000 test (cost=0.00..20.00 rows=600 width=8)
Filter: ((id < 700) AND (id > 100))
(6 rows)

edbtest=#
edbtest=# explain select * from test where id<1100 and id>900;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Result (cost=0.00..18.50 rows=201 width=8)
-> Append (cost=0.00..18.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 test_t1_1000_pkey on test_t1_1000 test (cost=0.00..9.25 rows=100 width=8)
Index Cond: ((id < 1100) AND (id > 900))
-> Index Scan using test_t1001_2000_pkey on test_t1001_2000 test (cost=0.00..9.25 rows=100 width=8)
Index Cond: ((id < 1100) AND (id > 900))
(8 rows)

5.3
按分区列值和其它列查询,只查询对应分区表
edbtest=# explain select * from test where id=300 and name='ccc';
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Result (cost=0.00..7.27 rows=2 width=47)
-> Append (cost=0.00..7.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 test_t1_1000_pkey on test_t1_1000 test (cost=0.00..7.27 rows=1 width=8)
Index Cond: (id = 300)
Filter: ((name)::text = 'ccc'::text)
(7 rows)

5.4
按分区列值查询,值有显式类型转换,只查询对应分区表
edbtest=# explain select * from test where id='5'::int;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Result (cost=0.00..7.27 rows=2 width=47)
-> Append (cost=0.00..7.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..7.27 rows=1 width=8)
Index Cond: (id = 5)
(6 rows)

5.5
按分区列值查询,值和列类型不同,值有隐式类型转换,只查询对应分区表
edbtest=# explain select * from test where id='5';
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Result (cost=0.00..7.27 rows=2 width=47)
-> Append (cost=0.00..7.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..7.27 rows=1 width=8)
Index Cond: (id = 5)
(6 rows)


5.6
按分区列值查询,列要做隐式类型转换,走全表扫描
edbtest=# explain select * from test where id || name ='5abc';
QUERY PLAN
---------------------------------------------------------------------------------
Result (cost=0.00..65.00 rows=14 width=14)
-> Append (cost=0.00..65.00 rows=14 width=14)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: (((id)::text || (name)::text) = '5abc'::text)
-> Seq Scan on test_t1_1000 test (cost=0.00..25.00 rows=5 width=8)
Filter: (((id)::text || (name)::text) = '5abc'::text)
-> Seq Scan on test_t1001_2000 test (cost=0.00..25.00 rows=5 width=8)
Filter: (((id)::text || (name)::text) = '5abc'::text)
-> Seq Scan on test_t2001_3000 test (cost=0.00..15.00 rows=3 width=8)
Filter: (((id)::text || (name)::text) = '5abc'::text)
(10 rows)

edbtest=#

5.7
按分区列值查询,值使用了函数,走全表扫描
edbtest=# explain select * from test where id=to_number('1');
QUERY PLAN
---------------------------------------------------------------------------------
Result (cost=0.00..52.00 rows=14 width=14)
-> Append (cost=0.00..52.00 rows=14 width=14)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: ((id)::numeric = 1::numeric)
-> Seq Scan on test_t1_1000 test (cost=0.00..20.00 rows=5 width=8)
Filter: ((id)::numeric = 1::numeric)
-> Seq Scan on test_t1001_2000 test (cost=0.00..20.00 rows=5 width=8)
Filter: ((id)::numeric = 1::numeric)
-> Seq Scan on test_t2001_3000 test (cost=0.00..12.00 rows=3 width=8)
Filter: ((id)::numeric = 1::numeric)
(10 rows)

5.8
按分区列值查询,走分区表索引扫描
edbtest=# explain select * from test where id=cast('1' as int);
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Result (cost=0.00..7.27 rows=2 width=47)
-> Append (cost=0.00..7.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..7.27 rows=1 width=8)
Index Cond: (id = 1)
(6 rows)

edbtest=#

5.9
按分区列值查询,值使用了子查询,用等号走全表扫描,用in走分区表索引扫描
edbtest=# explain select * from test where id in(select 1 from dual);
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Nested Loop Semi Join (cost=0.00..8.31 rows=2 width=47)
-> Append (cost=0.00..7.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..7.27 rows=1 width=8)
Index Cond: (id = 1)
-> Materialize (cost=0.00..1.01 rows=1 width=0)
-> Seq Scan on dual (cost=0.00..1.01 rows=1 width=0)
(8 rows)

edbtest=# explain select * from test where id =(select 1 from dual);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Result (cost=1.01..22.82 rows=4 width=28)
InitPlan 1 (returns $0)
-> Seq Scan on dual (cost=0.00..1.01 rows=1 width=0)
-> Append (cost=0.00..21.81 rows=4 width=28)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=86)
Filter: (id = $0)
-> Index Scan using test_t1_1000_pkey on test_t1_1000 test (cost=0.00..7.27 rows=1 width=8)
Index Cond: (id = $0)
-> Index Scan using test_t1001_2000_pkey on test_t1001_2000 test (cost=0.00..7.27 rows=1 width=8)
Index Cond: (id = $0)
-> Index Scan using test_t2001_3000_pkey on test_t2001_3000 test (cost=0.00..7.27 rows=1 width=8)
Index Cond: (id = $0)
(12 rows)

edbtest=#

5.10
按分区列值 更新,走分区表索引扫描
edbtest=# explain update test set name = 'bbb' where id=99;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Update on test (cost=0.00..7.27 rows=2 width=10)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=10)
Filter: (id = 99)
-> Index Scan using test_t1_1000_pkey on test_t1_1000 test (cost=0.00..7.27 rows=1 width=10)
Index Cond: (id = 99)
(5 rows)

5.11
按分区列值 删除,走分区表索引扫描
edbtest=# explain delete from test where id=99;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Delete on test (cost=0.00..7.27 rows=2 width=6)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=6)
Filter: (id = 99)
-> Index Scan using test_t1_1000_pkey on test_t1_1000 test (cost=0.00..7.27 rows=1 width=6)
Index Cond: (id = 99)
(5 rows)

edbtest=#

-----------------

转载请著明出处： blog.csdn.net/beiigang beigang.iteye.com

原文链接：https://www.f2er.com/postgresql/196191.html

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问