`

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

postgresqlppaspartitionedb 
阅读更多

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

0
顶
2
踩
分享到：
| PostgreSQL中copy命令以二进制形式传输数据 ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    PostgreSQL分区表（partitioning）应用实例详解

    项目中有需求要垂直分表，即按照时间区间将数据拆分到n个表中，PostgreSQL提供了分区表的功能。分区表实际上是把逻辑上的一个大表分割成物理上的几小块，提供了很多好处，比如： 1、查询性能大幅提升 2、删除历史...

    PostgreSQL表分区和子表及删除所有的数据库表.zip

    PostgreSQL表分区和子表及删除所有的数据库表 最近需求要求统计DNS近7天每天的解析情况。数据量相对大，所以我这边对表进行分区。 对每天的数据进行分区存储。主表只存储近7天的数据，7天之前的数据删掉。所以我...

    PostgreSQL分区表实践与思考.pptx

    总之，PostgreSQL分区表是一种有效的技术手段，能够解决大型表在数据管理和查询性能上所面临的挑战。它不仅提高了数据库的性能和存储效率，而且通过分而治之的策略，降低了数据维护的复杂性。数据库管理员应该根据...

    Python-实现轻松创建PostgreSQL的分区表DDL

    在数据库管理中，分区表是一种优化查询性能的技术，它将一个大表分成多个较小、更易管理的部分，每个部分称为一个分区。PostgreSQL作为一个强大的开源关系型数据库管理系统，支持多种分区策略，如范围、列表、哈希等...

    postgresql表分区

    PostgreSQL 作为一款功能强大的开源关系型数据库系统，支持多种类型的表分区机制。本文将详细介绍 PostgreSQL 中的表分区技术，包括其工作原理、实现方法以及示例演示。 #### 二、表分区原理与实现 ##### 2.1 表...

    许中清-PostgreSQL表分区实践

    许中清在2014年PostgreSQL中国大会上分享了关于PostgreSQL的原生表分区方案，这是数据库管理领域中一个非常重要的知识点，尤其适用于大型数据库系统的维护和优化。表分区是一种数据库架构优化技术，它允许将一个大表...

    PostgreSQL 创建表分区

    在PostgreSQL中，表分区是一种优化数据库性能的技术，它允许将大表的数据分割成更小、更易管理的部分，每个部分称为一个分区。这有助于提高查询速度，减少维护成本，并优化存储空间。以下是对创建表分区的详细说明：...

    PostgreSQL表分区的所有表结构和表分区及触发器.zip

    从这里可以看出PostgreSQL表...通过作用于主表的触发器，把主表的修改重定向到适当的分区表等工作，分区的创建和管理都需要我们用语言来控制，增加了开发人员的工作量，PostgreSQL表分区的所有表结构和表分区及触发器

    -赵飞祥（最新版）--斗鱼 PostgreSQL分区表实践与思考_zhaofx_v1.5_20191122.pdf

    1. 分区表的场景与意义：分区表在数据库管理中是一个重要的技术，它的存在可以帮助提升大型数据库的性能，并简化数据的维护和存储。分区表特别适用于数据量庞大的业务场景，比如配置表、状态表、流水表和统计表。...

    PostgreSQL表分区功能演进 (1)_8.pptx

    表分区（Table Partitioning）是指在特定场景下，把逻辑上的一个大表分裂成多个更小的物理分片，以获得性能的提升。通过分区，可以减少查询的数据量，提高查询速度，减少索引的大小和维护成本。 什么是分区表？ ...

    PostgreSql数据库分区表.html

    PostgreSql数据库分区表.html

    Postgresql内置分区表的使用总结

    在postgresql10之后，引入了内置分区表，用户不需要先在父表上定义insert,update,delete触发器，对父表的DML操作会自动路由到相应分区，相比传统分区表大幅度降低了维护成本，目前内置分区表仅支持范围分区和列表...

    PostgreSQL表的继承和分区

    在PostgreSQL中，表的继承是一个非常有用且强大的特性，它可以使得一个表（子表）继承另一个表（父表）的所有列和约束。这为数据库设计提供了一种灵活的方式来组织相似类型的数据，特别是当需要对一组共享相同属性的...

    Python-一个支持PostgreSQL11原生表分区的Django扩展

    **Python-一个支持PostgreSQL11原生表分区的Django扩展** 在现代数据库系统中，数据量的增长是常态，为了提高查询效率和管理大量数据，表分区是一种常见的优化策略。PostgreSQL 11 引入了原生的表分区功能，允许...

    MySQL和PostgreSQL的比较

    PostgreSQL则更为灵活，支持B-树、哈希、R-树和Gist等多种索引类型，以及临时表、常规表和复杂分区表。 #### 十、约束与扩展功能 MySQL支持主键、外键、唯一和非空约束，对检查约束仅做解析但不强制执行。...

    cpp-pgpathman是一个PostgreSQL高性能表分区插件

    **cpp-pgpathman：PostgreSQL的高性能表分区插件** `cpp-pgpathman` 是针对 PostgreSQL 数据库系统设计的一个高效表分区插件。它提供了对数据表进行分区的能力，以优化大型数据库的性能，尤其适用于处理大量数据的...

    postgresql 中文学习手册

    PostgreSQL学习手册(表的继承和分区) PostgreSQL学习手册(常用数据类型) PostgreSQL学习手册(函数和操作符&lt;一&gt;) PostgreSQL学习手册(函数和操作符&lt;二&gt;) PostgreSQL学习手册(函数和操作符&lt;三&gt;) PostgreSQL学习手册...

    PostgreSQL 性能优化宝典

    优化SQL语句是提升数据库性能的直接方式，例如通过减少不必要的数据表连接、合理使用索引、优化查询条件等方式来提高查询效率。 文档还提到了如何让数据库输出好的执行计划。这需要通过执行计划分析工具，比如...

Magicbox
Global site tag (gtag.js) - Google Analytics