备案控制台
探索云世界
开发者社区 数据库 文章 正文

PostgreSQL分区表的执行计划

2017-12-20 1139
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB
推荐场景：
PolarDB for MySQL 多主集群体验
简介：

开始

比如说我已经做好了对分区表的规则：

复制代码 
postgres=# CREATE OR REPLACE FUNCTION ptest_insert_trigger() RETURNS TRIGGER AS $$ 
postgres$# 
postgres$# BEGIN 
postgres$# 
postgres$#    IF ( NEW.id <5000000 ) THEN 
postgres$#        INSERT INTO ctest01 VALUES (NEW.*);
postgres$#    ELSIF ( NEW.id >= 5000000 ) THEN 
postgres$#        INSERT INTO ctest02 VALUES (NEW.*); 
postgres$#    ELSE 
postgres$#        RAISE EXCEPTION 'Error while inserting data';
postgres$#    END IF; 
postgres$#   
postgres$#   RETURN NULL;
postgres$# END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# CREATE TRIGGER insert_ptest_trigger BEFORE INSERT ON ptest FOR EACH ROW 
postgres-#   EXECUTE PROCEDURE ptest_insert_trigger();
CREATE TRIGGER
postgres=#
复制代码

就是说 ctest01 的数据， id<5000000, ctest02的数据， id>=5000000。

此时我的执行计划仍然是这个样子的：它似乎没有意识到我的ptest表的规则：

复制代码 
postgres=# explain select * from ptest where id=5000 or id=6000000;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Result  (cost=0.00..54.93 rows=5 width=20)
   ->  Append  (cost=0.00..54.93 rows=5 width=20)
         ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
               Filter: ((id = 5000) OR (id = 6000000))
         ->  Bitmap Heap Scan on ctest01 ptest  (cost=19.49..27.46 rows=2 width=9)
               Recheck Cond: ((id = 5000) OR (id = 6000000))
               ->  BitmapOr  (cost=19.49..19.49 rows=2 width=0)
                     ->  Bitmap Index Scan on ctest01_id_idx  (cost=0.00..9.74 rows=1 width=0)
                           Index Cond: (id = 5000)
                     ->  Bitmap Index Scan on ctest01_id_idx  (cost=0.00..9.74 rows=1 width=0)
                           Index Cond: (id = 6000000)
         ->  Bitmap Heap Scan on ctest02 ptest  (cost=19.49..27.46 rows=2 width=9)
               Recheck Cond: ((id = 5000) OR (id = 6000000))
               ->  BitmapOr  (cost=19.49..19.49 rows=2 width=0)
                     ->  Bitmap Index Scan on ctest02_id_idx  (cost=0.00..9.74 rows=1 width=0)
                           Index Cond: (id = 5000)
                     ->  Bitmap Index Scan on ctest02_id_idx  (cost=0.00..9.74 rows=1 width=0)
                           Index Cond: (id = 6000000)
(18 rows)

postgres=#
复制代码

对where 条件，它把它应用到每一个分区子表上了！

这里要谈到一个参数：

constraint_exclusion (enum)

Controls the query planner's use of table constraints to optimize queries. The allowed values of constraint_exclusion areon (examine constraints for all tables), off (never examine constraints), and partition (examine constraints only for inheritance child tables and UNION ALL subqueries). partition is the default setting. It is often used with inheritance and partitioned tables to improve performance.

当其为on或者 partition 的时候，在我这个例子里都是一样效果（我的父表没有数据）

复制代码 
postgres=# show constraint_exclusion;
 constraint_exclusion 
----------------------
 partition
(1 row)


postgres=# explain select * from ptest where id=5000;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Result  (cost=0.00..13.75 rows=2 width=36)
   ->  Append  (cost=0.00..13.75 rows=2 width=36)
         ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
               Filter: (id = 5000)
         ->  Index Scan using ctest01_id_idx on ctest01 ptest  (cost=0.00..13.75 rows=1 width=9)
               Index Cond: (id = 5000)
(6 rows)

postgres=# 

postgres=# explain select * from ptest where id=600000;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Result  (cost=0.00..13.75 rows=2 width=36)
   ->  Append  (cost=0.00..13.75 rows=2 width=36)
         ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
               Filter: (id = 600000)
         ->  Index Scan using ctest01_id_idx on ctest01 ptest  (cost=0.00..13.75 rows=1 width=9)
               Index Cond: (id = 600000)
(6 rows)

postgres=# 


postgres=# explain select * from ptest where id=5000
UNION ALL
select * from ptest where id=6000000;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..27.55 rows=4 width=36)
   ->  Append  (cost=0.00..27.55 rows=4 width=36)
         ->  Result  (cost=0.00..13.75 rows=2 width=36)
               ->  Append  (cost=0.00..13.75 rows=2 width=36)
                     ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
                           Filter: (id = 5000)
                     ->  Index Scan using ctest01_id_idx on ctest01 ptest  (cost=0.00..13.75 rows=1 width=9)
                           Index Cond: (id = 5000)
         ->  Result  (cost=0.00..13.75 rows=2 width=36)
               ->  Append  (cost=0.00..13.75 rows=2 width=36)
                     ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
                           Filter: (id = 6000000)
                     ->  Index Scan using ctest02_id_idx on ctest02 ptest  (cost=0.00..13.75 rows=1 width=9)
                           Index Cond: (id = 6000000)
(14 rows)

postgres=# 

postgres=# explain select * from ptest where id=5000 or id=6000000;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Result  (cost=0.00..54.93 rows=5 width=20)
   ->  Append  (cost=0.00..54.93 rows=5 width=20)
         ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
               Filter: ((id = 5000) OR (id = 6000000))
         ->  Bitmap Heap Scan on ctest01 ptest  (cost=19.49..27.46 rows=2 width=9)
               Recheck Cond: ((id = 5000) OR (id = 6000000))
               ->  BitmapOr  (cost=19.49..19.49 rows=2 width=0)
                     ->  Bitmap Index Scan on ctest01_id_idx  (cost=0.00..9.74 rows=1 width=0)
                           Index Cond: (id = 5000)
                     ->  Bitmap Index Scan on ctest01_id_idx  (cost=0.00..9.74 rows=1 width=0)
                           Index Cond: (id = 6000000)
         ->  Bitmap Heap Scan on ctest02 ptest  (cost=19.49..27.46 rows=2 width=9)
               Recheck Cond: ((id = 5000) OR (id = 6000000))
               ->  BitmapOr  (cost=19.49..19.49 rows=2 width=0)
                     ->  Bitmap Index Scan on ctest02_id_idx  (cost=0.00..9.74 rows=1 width=0)
                           Index Cond: (id = 5000)
                     ->  Bitmap Index Scan on ctest02_id_idx  (cost=0.00..9.74 rows=1 width=0)
                           Index Cond: (id = 6000000)
(18 rows)

postgres=#
复制代码
复制代码 
postgres=# set session constraint_exclusion=on;
SET
postgres=# 

postgres=# explain select * from ptest where id=5000;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Result  (cost=0.00..13.75 rows=2 width=36)
   ->  Append  (cost=0.00..13.75 rows=2 width=36)
         ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
               Filter: (id = 5000)
         ->  Index Scan using ctest01_id_idx on ctest01 ptest  (cost=0.00..13.75 rows=1 width=9)
               Index Cond: (id = 5000)
(6 rows)

postgres=# 

postgres=# 
postgres=# explain select * from ptest where id=6000000;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Result  (cost=0.00..13.75 rows=2 width=36)
   ->  Append  (cost=0.00..13.75 rows=2 width=36)
         ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
               Filter: (id = 6000000)
         ->  Index Scan using ctest02_id_idx on ctest02 ptest  (cost=0.00..13.75 rows=1 width=9)
               Index Cond: (id = 6000000)
(6 rows)

postgres=# 


postgres=# explain select * from ptest where id=5000
postgres-# UNION ALL
postgres-# select * from ptest where id=6000000;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..27.55 rows=4 width=36)
   ->  Append  (cost=0.00..27.55 rows=4 width=36)
         ->  Result  (cost=0.00..13.75 rows=2 width=36)
               ->  Append  (cost=0.00..13.75 rows=2 width=36)
                     ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
                           Filter: (id = 5000)
                     ->  Index Scan using ctest01_id_idx on ctest01 ptest  (cost=0.00..13.75 rows=1 width=9)
                           Index Cond: (id = 5000)
         ->  Result  (cost=0.00..13.75 rows=2 width=36)
               ->  Append  (cost=0.00..13.75 rows=2 width=36)
                     ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
                           Filter: (id = 6000000)
                     ->  Index Scan using ctest02_id_idx on ctest02 ptest  (cost=0.00..13.75 rows=1 width=9)
                           Index Cond: (id = 6000000)
(14 rows)

postgres=# 

postgres=# explain select * from ptest where id=5000 or id=6000000;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Result  (cost=0.00..54.93 rows=5 width=20)
   ->  Append  (cost=0.00..54.93 rows=5 width=20)
         ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
               Filter: ((id = 5000) OR (id = 6000000))
         ->  Bitmap Heap Scan on ctest01 ptest  (cost=19.49..27.46 rows=2 width=9)
               Recheck Cond: ((id = 5000) OR (id = 6000000))
               ->  BitmapOr  (cost=19.49..19.49 rows=2 width=0)
                     ->  Bitmap Index Scan on ctest01_id_idx  (cost=0.00..9.74 rows=1 width=0)
                           Index Cond: (id = 5000)
                     ->  Bitmap Index Scan on ctest01_id_idx  (cost=0.00..9.74 rows=1 width=0)
                           Index Cond: (id = 6000000)
         ->  Bitmap Heap Scan on ctest02 ptest  (cost=19.49..27.46 rows=2 width=9)
               Recheck Cond: ((id = 5000) OR (id = 6000000))
               ->  BitmapOr  (cost=19.49..19.49 rows=2 width=0)
                     ->  Bitmap Index Scan on ctest02_id_idx  (cost=0.00..9.74 rows=1 width=0)
                           Index Cond: (id = 5000)
                     ->  Bitmap Index Scan on ctest02_id_idx  (cost=0.00..9.74 rows=1 width=0)
                           Index Cond: (id = 6000000)
(18 rows)

postgres=#
复制代码

也就是说， constraint_exclusion 的识别能力也是有限的。对于 where 条件比较复杂的，也是无法处理的。

那么，constraint_exclusion off 时候，又如此？此时连对 id=5000 这样的，都需在所有的分区表里查询：

复制代码 
postgres=# set session constraint_exclusion=off;
SET
postgres=# 
postgres=# 
postgres=# explain select * from ptest where id=5000;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Result  (cost=0.00..27.51 rows=3 width=27)
   ->  Append  (cost=0.00..27.51 rows=3 width=27)
         ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
               Filter: (id = 5000)
         ->  Index Scan using ctest01_id_idx on ctest01 ptest  (cost=0.00..13.75 rows=1 width=9)
               Index Cond: (id = 5000)
         ->  Index Scan using ctest02_id_idx on ctest02 ptest  (cost=0.00..13.75 rows=1 width=9)
               Index Cond: (id = 5000)
(8 rows)

postgres=# 



postgres=# explain select * from ptest where id=6000000;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Result  (cost=0.00..27.51 rows=3 width=27)
   ->  Append  (cost=0.00..27.51 rows=3 width=27)
         ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=62)
               Filter: (id = 6000000)
         ->  Index Scan using ctest01_id_idx on ctest01 ptest  (cost=0.00..13.75 rows=1 width=9)
               Index Cond: (id = 6000000)
         ->  Index Scan using ctest02_id_idx on ctest02 ptest  (cost=0.00..13.75 rows=1 width=9)
               Index Cond: (id = 6000000)
(8 rows)

postgres=#
复制代码

 

结束





本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/archive/2012/11/12/2765818.html，如需转载请自行联系原作者

文章标签：
云原生数据库 PolarDB
关系型数据库
PostgreSQL
Linux
关键词：
云原生数据库 PolarDB分区表
云数据库 RDS PostgreSQL 版执行计划
云数据库 RDS PostgreSQL 版分区表
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
嗯哼9925
目录
相关文章
牧龙鼠
|
7月前
|
SQL 监控 关系型数据库
PostgreSQL普通表转换成分区表
如何使用pg_rewrite扩展将普遍表转换成分区表
牧龙鼠
283 0
yzshover
|
10月前
|
关系型数据库 PostgreSQL
PostgreSQL执行计划explain
PostgreSQL执行计划explain
yzshover
83 0
yzshover
|
10月前
|
关系型数据库 PostgreSQL 索引
PostgreSQL执行计划数据结构
PostgreSQL执行计划数据结构
yzshover
55 0
悟道之客
|
10月前
|
存储 SQL 运维
PolarDB MySQL大表实践-分区表篇
背景：分区表到底是什么？分区作为传统企业级数据库的特性，早已经在很多大数据和数仓场景中得到广泛应用。基于维基百科的解释，分区是将逻辑数据库或其组成元素如表、表空间等划分为不同的独立部分。数据库分区通常是出于可管理性、性能或可用性的原因，或者是为了负载平衡。它在分布式数据库管理系统中很流行，其中每个分区可能分布在多个节点上，节点上的用户在分区上执行本地事务。这提高了具有涉及某些数据视图的常规事务的站
悟道之客
340 0
PolarDB MySQL大表实践-分区表篇
开发者小达人
|
12月前
|
SQL Cloud Native 关系型数据库
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版功能演示（上）——六、查看分析执行计划
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版功能演示（上）——六、查看分析执行计划
开发者小达人
156 0
pgdba
|
关系型数据库 PostgreSQL 索引
PostgreSQL 性能优化: 执行计划
PostgreSQL为每个收到的查询产生一个查询计划。查询计划的结构是一个计划结点的树。最底层的结点是扫描结点：它们从表中返回未经处理的行。不同的表访问模式有不同的扫描结点类型：顺序扫描、索引扫描、位图索引扫描。也还有不是表的行来源，例如VALUES子句和FROM中返回集合的函数，它们有自己的结点类型。如果查询需要连接、聚集、排序、或者在未经处理的行上的其它操作，那么就会在扫描结点之上有其它额外的结点来执行这些操作。并且，做这些操作通常都有多种方法，因此在这些位置也有可能出现不同的结点类型。
pgdba
85 0
ybbf7fwncy2w2
|
存储 SQL 监控
16PostgreSQL 本地分区表的用法和优化|学习笔记
快速学习16PostgreSQL 本地分区表的用法和优化
ybbf7fwncy2w2
394 0
16PostgreSQL 本地分区表的用法和优化|学习笔记
bqospzg5rfs7g
|
SQL 移动开发 关系型数据库
PostgreSQL 执行计划，成本公式解说，代价因子校准，自动跟踪SQL执行计划（三）|学习笔记
快速学习PostgreSQL 执行计划，成本公式解说，代价因子校准，自动跟踪SQL执行计划（三）
bqospzg5rfs7g
552 0
PostgreSQL 执行计划，成本公式解说，代价因子校准，自动跟踪SQL执行计划（三）|学习笔记
PolarDB-Evangelist
|
存储 SQL Cloud Native
直播预告 | PolarDB-X 动手实践系列—— PolarDB-X 的 TTL分区表功能介绍及原理解析
在某些业务场景下，数据库的数据会增量很快，并且数据热度随着时间推移会有明显的降低。如果数据一直存储在 PolarDB-X 中，既会占用存储空间，也会降低正常业务查询的效率。所以，需要有一个机制，能让冷数据定期地转移到其他成本更低的存储，以及让冷数据在 PolarDB-X 中自动删除，这就是TTL分区表。
PolarDB-Evangelist
309 0
直播预告 | PolarDB-X 动手实践系列—— PolarDB-X 的 TTL分区表功能介绍及原理解析
skin778
|
存储 SQL JSON
大分区表高并发性能提升100倍？阿里云 RDS PostgreSQL 12 特性解读
世界上几乎最强大的开源数据库系统 PostgreSQL，于 2019 年 10 月 3 日发布了 12 版本，该版本已经在阿里云正式发布。PostgreSQL 12 在功能和性能上都有很大提升，如大分区表高并发性能提升百倍，B-tree 索引空间和性能优化，实现 SQL 2016 标准的 JSON 特性，支持多列 MCV（Most-Common-Value）统计，内联 CTE（Common table expressions）以及可插拔的表存储访问接口等。本文对部分特性进行解读。
skin778
2005 0
大分区表高并发性能提升100倍？阿里云 RDS PostgreSQL 12 特性解读

热门文章

最新文章

  • 1
    电子好书发您分享《PolarDB分布式版架构介绍》
  • 2
    数据之势丨AI时代，云原生数据库的最新发展趋势与进展
  • 3
    【PolarDB-X从入门到精通】 第四讲：PolarDB分布式版安装部署（源码编译部署）
  • 4
    【公测】PolarDB PostgreSQL版Serverless功能免费使用​！
  • 5
    postgresql安装
  • 6
    「合肥 * 讯飞」4 月 19 日 PolarDB 开源数据库沙龙，报名中！
  • 7
    电子好书发您分享《使用云起实验室体验PolarDB分布式版》
  • 8
    PostgreSQL Json应用场景介绍和Shared Detoast优化
  • 9
    实战秘籍 | 《学姐和你一起学PolarDB-X》课程拓展资料（第二讲）
  • 10
    把PostgreSQL的表导入SQLite
  • 1
    PostgreSQL从小白到高手教程 - 第47讲：JMETER工具使用
    109
  • 2
    一篇文章带你搞懂非关系型数据库MongoDB
    58
  • 3
    PolarDB中的pdb冷归档存储和X-Engine的价格对比
    16
  • 4
    DataWorks常见问题之将预警信息发送至邮箱
    41
  • 5
    PolarDB for MySQL数据库外网连接解析失败的原因可能有以下几点
    27
  • 6
    PolarDB架构优势
    478
  • 7
    在PolarDB中，行数评估是通过对表的统计数据、基数估计以及算子代价模型来进行估算的。
    87
  • 8
    PolarDB PostgreSQL版：Oracle兼容的高性能数据库
    101
  • 9
    数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性！
    378
  • 10
    postgresql日程排程函数的编写实例
    35

    • 相关课程

    更多
  • 7天突破PolarDB for PostgreSQL 2022版
  • 关系型数据库ACP认证课程
  • 关系型数据库线上预习视频
  • PolarDB for PostgreSQL 入门
  • PostgreSQL云上开发实践
  • PostgreSQL实战进阶

    • 相关电子书

    更多
  • 云栖大会：开源 PolarDB 架构演进、关键技术与社区建设
  • 2023云栖大会：和客户一起玩转PolarDB新特性
  • 2023云栖大会：PolarDB for AI

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • 阿里云百炼xAnalyticDB PostgreSQL构建AIGC应用
  • 云原生数据库PolarDB快速入门
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 使用CloudLens采集PolarDB日志并进行审计分析
  • PolarDB for PostgreSQL 闪回特性体验

    • 推荐镜像

    更多
  • postgresql
  • clickhouse
  • mongodb
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考