PostgreSQL分区表的执行计划

最新推荐文章于 2023-08-21 00:53:25 发布
最新推荐文章于 2023-08-21 00:53:25 发布
阅读量118 收藏
点赞数
文章标签： 数据库

开始

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

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=#

 

结束

weixin_33805992
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
pg_party：ActiveRecord PostgreSQL分区
02-01
产品特点分区特定的数据库操作的迁移方法查询分区数据，创建临时分区和获取分区元数据的模型方法局限性分区表在db/schema.rb表示不正确-请使用:sql模式格式未来的工作自动分区创建（通过cron或其他方式）安装将此行...
PostgreSQL执行计划介绍
u011868279的博客
08-31 4867
PostgreSQL执行计划介绍
进阶数据库系列（九）：PostgreSQL 执行计划
pgdba的博客
08-09 493
前面介绍了 PostgreSQL 常用管理命令、访问控制与认证、语法、数据类型、常用函数、锁操作相关的知识点，今天我将详细的为大家介绍 PostgreSQL 执行计划介绍相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！了解 PostgreSQL 执行计划对于程序员来说是一项关键技能，执行计划是我们优化查询，验证我们的优化查询是否确实按照我们期望的方式运行的重要方式。
【数据库】 - postgresql数据库执行计划
wangdong的博客
07-31 2299
数据库 - 执行计划 1. 执行计划命令 explain 2. 命令参数 analyze：选项为TRUE 会实际执行SQL，并获得相应的查询计划，默认为FALSE buffers：buffers必须跟analyze一起使用，只有真实执行SQL才能获取缓冲区信息 costs：表示每一个计划节点的估计启动和总代价，以及估计的行数和每行的宽度 Verbose ：显示关于计划的额外信息 ，例如：节点输出的列（Output） Format（text，xml，json，yaml）（格式化输出）
Postgresql | 执行计划
Albirostris
03-09 2845
postgresql 执行计划
pgcat：增强的PostgreSQL逻辑复制
02-05
内置逻辑复制具有以下不足： 仅支持将基表作为复制目标不过滤任何原点，这将导致双向死循环无法执行表名映射没有冲突解决pgcat进行了以下增强： 支持任何表类型作为复制目标，例如视图，fdw，分区表，citus分布式表...
PostgreSQL数据库中窗口函数的语法与使用
12-15
一个窗口函数在一系列与当前行有某种关联的表行上执行一种计算。这与一个聚集函数所完成的计算有可比之处。但是窗口函数并不会使多行被聚集成一个单独的输出行，这与通常的非窗口聚集函数不同。取而代之，行保留它们...
PostegreSQL-partitioning
04-25
PostgreSQL内置了对以下类型分区的支持： 按间隔分区。 该表分为一个键列或多个列指定的间隔。 每个分区的间隔不重叠。 按列表分区。 该表使用每个键的显式枚举进行拆分，该分区包含该值。 执行 让我们创建一个表...
wombat:基于pyarrow的数据分析工具包
03-21
为了方便起见，函数命名和行为尝试复制Pandas API / Postgresql语言的命名和行为。 当前功能： 引擎API（延迟执行）： 直接在Pyarrow表和数据集上进行操作 过滤下拉菜单以优化速度（仅读取分区的子集） 列跟踪：仅...
PG数据库执行计划-Explain详解
linyuchen820的博客
08-21 375
​ 执行计划是由规划器对一条SQL语句规划出的执行步骤，通俗的说：是规划器列举出”一条SQL，通过某种扫描方式，加载某些表或块的数据，再根据过滤条件，最终拿到符合该SQL的所有数据“的过程。​ 扫描方式有：顺序扫描（全表扫描）、索引扫描、hash索引扫描。
【转载】PostgreSQL执行计划
荷塘月色
05-22 5722
简介 PostgreSQL是“世界上最先进的开源关系型数据库”。因为出现较晚，所以客户人群基数较MySQL少，但是发展势头很猛，最大优势是完全开源。 MySQL是“世界上最流行的开源关系型数据库”。当前客户基数大，随着被Oracle收购，开源程度减小，尤其是近期单独拉了免费的MariaDB分支，更表明MySQL有闭源的倾向； 至于两者孰优孰劣，不是本文要讨论的重点，在一般的使用中，没什么大的差别，下面我们只讨论PG中执行计划。 执行计划 pg在查询规划路径过程中，查询请求的不同执行方案是通过建立不同的路径来
二、postgre数据库SQL优化：查看执行计划
积跬步，慕至千里的博客
11-03 5771
sql优化是一项必备技能，为了优化sql任务，则需要明确需要优化的内容。除了经验之外，可以借助工具，查看sql执行过程中的耗时环节，从而针对性的进行优化，本文即为对执行计划的一些基本信息进行了总结学习。
PG PostgreSQL 执行计划
秋̶᭄ꦿ攻城狮࿆ྂ
08-11 834
PG PostgreSQL 执行计划执行计划解释代价参数GEQO(遗传查询优化)其它执行计划配置项执行计划配置执行计划节点类型运算类型 执行计划解释 EXPLAIN [ ANALYZE ] [ VERBOSE ] statement where option can be one of: ANALYZE [ boolean ] VERBOSE [ boolean ] COSTS [ boolean ] SETTINGS [ boolean ]
PG数据库内核源码分析——执行计划EXPLAIN
让我思考一下
06-25 1211
在Postgres中，显示执行计划可用explain命令，示例如下： 我们分析一下源码，看一下 是怎么显示执行计划的，选取了一条简单的查询语句。如果是数据库内核开发者，在新实现某个算子或者新增了某个Plan，这时还需要改一下执行计划显示相关代码，主要在explain.c中。我们分析一下其大概的过程。输入这条语句 进行分析。在进行源码跟踪前想一下，如果自己实现，应该怎么做呢？大概步骤如下：后面我们跟踪一下源码，看源码是怎么实现的。主要代码在 中。我们看一下其执行流程 EXPLAIN语法定义 在gram.y中定
PostgreSQL中的执行计划
12-24 2113
PostgreSQL中的执行计划 EXPLAN 预生成执行计划 EXPLAN sql 真实执行计划 explan analyze sql 输出详细内容 explan(analyze on, timing on,verbose on,buffers on,costs on) sql 语法 EXPLAIN [ ( option [, ...] ) ] statement EXPLAIN [ ANALYZE ] [ VERBOSE ] statemen 选项： analyze：执行语句并显示真正的运行时
PostgreSQL执行计划的解释
热门推荐
秋韩
05-25 2万+
一个顺序磁盘页面操作的cost值由系统参数seq_page_cost (floating point)参数指定的，由于这个参数默认为1.0，所以我们可以认为一次顺序磁盘页面操作的cost值为1。 下面 osdba=# explain select * from t; QUERY PLAN ———————————————————- Seq Scan on t  (cost=0.00 ..4
postgresql 数据库执行计划 Hash Join
一名数据库爱好者的专栏
05-28 3527
os: centos 7.4 db: postgresql 10.11 Hash Join (散列连接) 实现可以理解为使用驱动表(小表)用来建立 hash map ，依次读取驱动表的数据，对于每一行数据根据连接条件生成一个 hash map 中的一个元組(生成 hashkey)， 再和被驱动表比较 hashkey，如果不相同则直接丢弃，如果相同再次过一遍join condition和filter，满足条件的数据集返回。 Hash Join (散列连接) 不依赖索引，一般会选择个小表当驱动表。 Hash J
postgresql 分区表
最新发布
08-23
PostgreSQL 支持分区表，它允许将表数据根据特定的条件拆分成多个子表，从而提高查询性能和管理数据。下面是一些关于 PostgreSQL 分区表的基本信息： 1. 分区表定义：在创建表时，可以使用 PARTITION BY 子句指定...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值