PostgreSQL的执行计划分析
-------------
新增文件夹...
新增文件夹
(多个标签用逗号分隔)
想查看Postgresql的执行计划，下面分析下PG执行计划中的cost等相关值是怎么计算出来的：
PG的版本是9.1.2
1.终端工具PGADMIN,对执行的语句按F7即可，然后看数据输出和解释
2.命令行分析：explain select * from table_
一般我们会比较关注消耗值cost和扫描的方式，如走索引或者full scan全表扫描.当COST值消耗比较大时需要注意是否有优化的可能。
与执行计划相关的几个参数，参看下面的示例： kenyon=# select count(1) from dba.&&&&&&&&&&&&&&&&&&&&&&&&&&&&
--普通堆栈表，无任何索引约束
kenyon=# explain select * from dba.
QUERY PLAN
--------------------------------------------------------
Seq Scan on website
(cost=0.00..1.20 rows=20 width=4)
--relpages磁盘页，reltuples是行数(与实际不一定相符，一般略小)
kenyon=# select relpages,reltuples from pg_class where relname = 'website';
relpages | reltuples
----------+-----------
kenyon=# select 1*1+20*0.01;
--cost = relpages * seq_page_cost + reltuples * cpu_tuple_cost
----------
kenyon=# show cpu_tuple_
cpu_tuple_cost
----------------
kenyon=# show seq_page_
seq_page_cost
---------------
--加限制条件的执行计划
kenyon=# select count(1) from dba.website where hits &15;
kenyon=# explain select * from dba.website where hits &15;
QUERY PLAN
-------------------------------------------------------
Seq Scan on website
(cost=0.00..1.25 rows=5 width=4)
Filter: (hits & 15)
kenyon=# show cpu_operator_
cpu_operator_cost
-------------------
因为扫描的总数是20行，不变的，所以COST不会下降，相反反而增加了0.05，这是因为额外消耗了CPU的时间去检查符合约束条件数据，即cost 在原来的基础上再增加 20 * 0.0025 = 0.05
（reltuples *&&&&cpu_operator_cost）
--加索引的执行计划
kenyon=# select count(1) from dba.website_2 ;
kenyon=# explain select * from dba.website_2 ;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on website_2
(cost=0.00..112.00 rows=8000 width=4)
kenyon=# select relpages,reltuples from pg_class where relname = 'website_2';
relpages | reltuples
----------+-----------
kenyon=# explain select * from dba.website_2 where hits &7900;
--走的索引
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using ind_website_2 on website_2
(cost=0.00..10.00 rows=100 width=4)
Index Cond: (hits & 7900)
kenyon=# explain select * from dba.website_2 where hits &10;
--未走索引（不满足索引条件，full scan）
QUERY PLAN
--------------------------------------------------------------
Seq Scan on website_2
(cost=0.00..132.00 rows=7991 width=4)
-- 132 = 112+5
Filter: (hits & 10)
虽然读取的COST更大，但是因为索引的缘故，访问的数据量变小了，所以总体COST是下降的。--多表JOIN的执行计划 示例： 若想看实际的一个执行时间，可以加上 analyze 参数 kenyon=# explain analyze select * from dba.website a ,dba.website_2 b where a.hits = b.hits and a.hits &18;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=1.26..1.90 rows=2 width=8) (actual time=0.070..0.075 rows=2 loops=1)
Merge Cond: (b.hits = a.hits)
-& Index Scan using ind_website_2 on website_2 b (cost=0.00..235.25 rows=8000 width=4) (actual time=0.013..0.020 rows=21 loops=1)
-& Sort (cost=1.26..1.26 rows=2 width=4) (actual time=0.035..0.037 rows=2 loops=1)
Sort Key: a.hits
Sort Method: quicksort Memory: 17kB
-& Seq Scan on website a (cost=0.00..1.25 rows=2 width=4) (actual time=0.009..0.011 rows=2 loops=1)
Filter: (hits & 18)
Total runtime : 0.120 ms
(9 rows) total runtime 是执行器启动和关闭的时间，但不包括解析，重写和规划的时间
注意： pg_class中的relpages,reltuples数据不是实时更新的，一般在vacuum analyze和少部分DDL（如建立索引）后更新。
示例1： kenyon=# insert into dba.website select generate_series();
INSERT 0 1001
kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like '%website%';
relpages | reltuples |
----------+-----------+---------------+---------
20 | website
8000 | website_2
8000 | ind_website_2 | i
kenyon=# vacuum analyze dba.
kenyon=# vacuum analyze dba.
kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like '%website%';
relpages | reltuples |
----------+-----------+---------------+---------
1021 | website
8999 | website_2
8999 | ind_website_2 | i
(3 rows)示例2： kenyon=# insert into dba.website select generate_series();
INSERT 0 1001
kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like '%website%';
relpages | reltuples |
----------+-----------+---------------+---------
21 | website
8999 | website_2
8999 | ind_website_2 | i
kenyon=# create index ind_website on dba.website(hits);
CREATE INDEX
kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like '%website%';
relpages | reltuples |
----------+-----------+---------------+---------
1022 | website
8999 | website_2
8999 | ind_website_2 | i
1022 | ind_website
(4 rows)所涉及的系统表：
pg_statistic
pg_stat是任何人都可以看的，而且可读性高，比较直观，pg_statistic只有superuser才能读，并且可读性差，普通人员建议看 pg_stats，pg_stats是pg_statistic的视图。 这两个表也不是实时更新的，需要vacuum analyze时会更新
所涉及的系统变量：
default_statistics_target
geqo_threshold
join_collapse_limit
from_collapse_limit kenyon=# show default_statistics_
default_statistics_target
---------------------------
kenyon=# show geqo_
--这个参数的大小会设置执行计划从穷举搜索到概率选择性搜索的临界值
geqo_threshold
----------------
kenyon=# show join_collapse_
--join连接走执行计划上限
join_collapse_limit
---------------------
kenyon=# show from_collapse_
from_collapse_limit
---------------------
(1 row)EXPLAIN
EXPLAIN— show the execution plan of a statement
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
&where option can be one of:
& &ANALYZE [ boolean ]
& &VERBOSE [ boolean ]
& &COSTS [ boolean ]
& &BUFFERS [ boolean ]
& &FORMAT { TEXT | XML | JSON | YAML }
例子： kenyon=# explain (analyze,verbose,costs,buffers) select id from dba.test222 order by id desc limit 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
(cost=07.80 rows=1 width=4) (actual time=87.167..87.168 rows=1 loops=1)
Output: id
Buffers: shared hit=393
(cost=43.60 rows=94320 width=4) (actual time=87.165..87.165 rows=1 loops=1)
Output: id
Sort Key: test222.id
Sort Method: top-N heapsort
Memory: 17kB
Buffers: shared hit=393
Seq Scan on dba.test222
(cost=0.00..1336.20 rows=94320 width=4) (actual time=0.036..42.847 rows=100000 loops=1)
Output: id
Buffers: shared hit=393
Total runtime: 87.183 ms
kenyon=# explain (analyze,verbose,costs,buffers) select max(id) from dba.test222;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
(cost=72.01 rows=1 width=4) (actual time=77.679..77.680 rows=1 loops=1)
Output: max(id)
Buffers: shared hit=393
Seq Scan on dba.test222
(cost=0.00..1336.20 rows=94320 width=4) (actual time=0.012..36.908 rows=100000 loops=1)
Output: id
Buffers: shared hit=393
Total runtime: 77.701 ms
(7 rows)explain参数解释：
ANALYZE ：执行命令并显示执行事件，默认false
VERBOSE ：对执行计划提供额外的信息，如查询字段信息等，默认false
COSTS ：显示执行计划的，默认true
BUFFERS ：默认false，前置条件是analyze
FORMAT ：默认格式是text
相关资讯 　—　
相关文档 　—　
发布时间： 16:43:11
同类热门经验
10671次浏览
18967次浏览
14460次浏览
8862次浏览
8646次浏览
6636次浏览
OPEN-OPEN, all rights reserved.优化Oracle with全表扫描的问题_数据库技术_Linux公社-Linux系统门户网站
你好，游客
优化Oracle with全表扫描的问题
来源：Linux社区&
作者：壹頁書
今天开发接了一个很BT的需求。&找一个人的所有好友，查询所有好友的所有作品，然后按照时间倒序排列,取若干记录，&然后关联作品评论表。&作品包括原唱表，翻唱表，伴奏表，视频表，博客表和照片表,&不同的作品类型还要关联不同的专辑表，最后还要关联用户表..
--------------------------------------------------------------------------------
Linux-6-64下安装 12C笔记
在 6.4下安装Oracle 11gR2(x64)
Oracle 11gR2 在VMWare虚拟机中安装步骤
Debian 下 安装 Oracle 11g XE R2
--------------------------------------------------------------------------------结果就是这个SQL很长...&with&t1 as (select to_userid from friend_list f where f.userid=),&t2 as (& select 'mc' as t,rid,createtime & from& (& & select mc.rowid rid,mc.createtime from music_cover mc,t1 where mc.userid=t1.to_userid and mc.opus_stat &0 order by mc.createtime desc& ) where rownum& 100& union all& select 'mo',rid,createtime & from& (& & select mo.rowid rid,mo.createtime& from music_original mo,t1 where mo.userid=t1.to_userid and mo.opus_stat &0 order by mo.createtime desc& ) where rownum& 100& & union all& select 'mv',rid,createtime & from& (& & select mv.rowid rid,mv.createtime& from music_video mv,t1 where mv.userid=t1.to_userid and mv.opus_stat &0 order by mv.createtime desc& ) where rownum& 100& & union all& select 'ma',rid,createtime & from& (& & select ma.rowid rid,ma.createtime from music_accompany ma,t1 where ma.userid=t1.to_userid and ma.opus_stat &0 order by ma.createtime desc& ) where rownum& 100& & union all& select 'bl',rid,createtime& from& (& & select bl.rowid rid,bl.createtime& from blog_list bl,t1 where bl.userid=t1.to_userid and bl.opus_stat &0 order by bl.createtime desc& ) where rownum& 100& & union all& select 'pl',rid,createtime& from& (& & select pl.rowid rid,pl.createtime& from photo_list pl,t1 where pl.userid=t1.to_userid and pl.opus_stat &0 order by pl.createtime desc& ) where rownum& 100&),&t3 as &(& select * from & (& & select * from t2 order by createtime desc& )& where rownum&100&),&t4 as&(&select & t3.t,& decode(t3.t,&'mc',2,&'mo',2,&'mv',2,&'ma',2,&'pl',4,&'bl',5&) type_code,& mc.userid||mo.userid||mv.userid||ma.userid||bl.userid||pl.userid userid,& mc.file_url||mo.file_url||mv.file_url||ma.file_url||bl.file_url||pl.file_url file_url,& mc.opus_Name||mo.opus_Name||mv.opus_name||ma.opus_name||bl.opus_name||pl.opus_name opus_name,& mc.opus_id||mo.opus_id||mv.opus_id||ma.opus_id||bl.opus_id||pl.opus_id opus_id,& mc.createtime||mo.createtime||mv.createtime||ma.createtime||bl.createtime||pl.createtime createtime,& mv.opus_desc||mo.opus_desc||mc.opus_desc||ma.opus_desc||bl.opus_desc||pl.opus_desc opus_desc,& mv.album_id||mo.album_id||mc.album_id||ma.album_id||bl.album_id||pl.album_id album_id,& mv.visit_num||mo.visit_num||mc.visit_num||ma.visit_num||bl.visit_num||pl.visit_num visit_num&from t3&left join music_cover mc on(t3.rid=mc.rowid)&left join music_original mo on(t3.rid=mo.rowid)&left join music_video mv on(t3.rid=mv.rowid)&left join music_accompany ma on(t3.rid=ma.rowid)&left join blog_list bl on(t3.rid=bl.rowid)&left join photo_list pl on(t3.rid=pl.rowid)&)&select /*+ ordered use_nl(t4,base) */&base.nickname,&decode(t4.type_code,&2,(select al.album_name from music_album al where al.album_id=t4.album_id),&4,(select al.album_name from photo_album al where al.album_id=t4.album_id),&5,(select al.album_name from blog_album al where al.album_id=t4.album_id)&) album_name,&(select count(*) from user_comment com where com.typeid=t4.type_code and t4.opus_id=com.to_id and status=1) commentTotal,&t4.*&from t4,mvbox_user.user_baseinfo base where base.userid=t4.&
创建索引消除排序&create index inx_music_cover on music_cover(userid,opus_stat,operTime);&create index inx_music_original on music_original(userid,opus_stat,operTime);&create index inx_music_video on music_video(userid,opus_stat,operTime);&create index inx_music_accompany on music_accompany(userid,opus_stat,operTime);&create index inx_blog_list on blog_list(userid,opus_stat,operTime);&create index inx_photo_list on photo_list(userid,opus_stat,operTime);&create index inx_user_comment on user_comment(to_id,typeid,status);&用户表比较大，是一个有900w记录的分区表。查看执行计划，都符合预期，只是最后关联用户表的时候，使用了全表扫描，直接导致这个SQL执行了20s左右。但是用户表的关联字段明明已经创建了索引。。
更多详情见请继续阅读下一页的精彩内容：
相关资讯 & & &
& (02月25日)
& (01月13日)
& (06月16日)
& (02月24日)
& (11/21/:10)
图片资讯 & & &
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款用心创造滤镜
扫码下载App
汇聚2000万达人的兴趣社区下载即送20张免费照片冲印
扫码下载App
温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
阅读(29468)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
在LOFTER的更多文章
loftPermalink:'',
id:'fks_',
blogTitle:'PostgreSQL 优化一例:
使用函数索引和联合索引',
blogAbstract:'
&&&&&&&&& &今天有个生产库上的语句比较慢，花费近 500 毫秒左右，为了便于测试，后来将这几个表导到测试环境下测试，并且优化后，时间下降到仅需要 0.104 ms， 下面是优化过程。
--1 表结构
&francs=& \\d test_count&&&&&&& Table \"francs.test_count\"&& Column&&& |&&&&&& Type&&&&&&& |&& Modifiers&& ',
blogTag:'',
blogUrl:'blog/static/',
isPublished:1,
istop:false,
modifyTime:1,
publishTime:7,
permalink:'blog/static/',
commentCount:0,
mainCommentCount:0,
recommendCount:0,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:false,
hostIntro:'',
hmcon:'1',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}1628人阅读
From PostgreSQL 中文维基, PostgreSQL 中文站, PostgreSQL 中国社区, PostgreSQL Chinese community
Jump to: navigation, search
[编辑] 分区
目录 [隐藏]
1.2 实现分区
1.3 分区和约束排除
PostgreSQL 支持基本的表分区功能。 本节描述为何需要表分区以及你如何在你的数据库设计里面实现表分区。
[编辑] 概述
分区的意思是把逻辑上的一个大表分割成物理上的几块儿。 分区可以提供若干好处：
某些类型的查询性能可以得到极大提升。
更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。 如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。
批量删除可以用简单地删除某个分区来实现 － 只要需求已经在分区设计是进行了规划。 DROP TABLE 比批量 DELETE 要快很多， 因为不需要有 VACUUM 的开销。
很少用的数据可以移动到便宜的、慢一些地存储介质上。
这种好处通常只有在表可能会变得非常大的情况下才有价值。 表在多大的情况下会从分区中收益取决于应用，不过有个基本的拇指规则就是表的大小超过了数据库服务器的物理内存大小。
目前，PostgreSQL 支持通过表继承进行分区。 每个分区必须做为单独一个父表的子表进行创建。父表自身通常是空的； 它的存在只是为了代表整个数据集。你在试图实现分区之前，应该先熟悉继承（参阅5.8节）。
PostgreSQL 里面可以实现下面形式的分区：
表被一个或者多个键字字段分区成&范围&， 在这些范围之间没有重叠的数值分布到不同的分区里。 比如，我们可以为特定的商业对象根据数据范围分区，或者根据标识符范围分区。
表是通过明确地列出每个分区里应该出现那些键字值实现的。
目前还不支持散列分区。
[编辑] 实现分区
要设置一个分区的表，做下面的步骤：
创建&主表&，所有分区都从它上面继承下去。
这个表将没有什么数据，不要在这个表上定义任何检查约束， 除非你希望约束同样也适用于所有分区。同时在其上定义任何索引或者唯一约束也没有意义。
创建几个&子&表，每个都从主表上继承。 通常，这些表将不会对从主表继承过来集合增加任何字段。
我们将把子表称作分区，尽管它们就是普通的 PostgreSQL 表。
给分区表增加约束，定义每个分区允许的健值。
典型的例子是：
&&&& CHECK ( x = 1 )
&&&& CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
&&&& CHECK ( outletID &= 100 AND outletID & 200 )
确信这些约束保证在不同的分区里不会有重叠的键字。一个常见的错误是设置下面这样的范围：
&&&& CHECK ( outletID BETWEEN 100 AND 200 )
&&&& CHECK ( outletID BETWEEN 200 AND 300 )
这样做是错误的，因为它没说清楚健&#2 属于那个范围。
请注意在范围和列表分区的语法方面没有什么区别；这些术语只是用于描述的。
对于每个分区，在键字字段上创建一个索引，以及其它你想创建的索引。 （键字索引并非严格要求的，但是在大多数情况下它是很有帮助的。 如果你希望键字值是唯一的，那么你应该总是给每个分区创建一个唯一或者主键约束。
另外，定义一个规则或者触发器，把对主表的修改重定向到合适的分区表。
确保 postgresql.conf 里的配置参数 constraint_exclusion 是打开的。 没有这个参数，查询不会按照需要进行优化。
比如，假设我们为一个巨大的冰激凌公司构造数据库。 该公司每天都测量最高温度，以及每个地区的冰激凌销售。 概念上，我们需要一个这样的表：
&CREATE TABLE measurement (
&&&& city_id&&&&&&&& int not null,
&&&& logdate&&&&&&&& date not null,
&&&& peaktemp&&&&&&& int,
&&&& unitsales&&&&&& int
我们知道大多数查询都只会访问最后一周，最后一个月或者最后一个季度的数据， 因为这个表的主要用途是为管理准备在线报告。 为了减少需要存储的旧数据，我们决定值保留最近三年的有用数据。 在每个月的开头，我们都会删除最旧的一个月的数据。
在这种情况下，我们可以使用分区来帮助我们实现所有我们对表的不同需求。 下面的步骤描述了上面的需求，分区可以这样设置：
主表是 measurement 表，就像上面那样声明。
然后我们为每个月创建一个分区：
&&&& CREATE TABLE measurement_yy04mm02 ( ) INHERITS (measurement);
&&&& CREATE TABLE measurement_yy04mm03 ( ) INHERITS (measurement);
&&&& CREATE TABLE measurement_yy05mm11 ( ) INHERITS (measurement);
&&&& CREATE TABLE measurement_yy05mm12 ( ) INHERITS (measurement);
&&&& CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement);
每个分区都是拥有自己内容的完整的表，只是它们从 measurement 表继承定义。
这样就解决了我们的一个问题：删除旧数据。 每个月，我们需要做的只是在最旧的子表上执行一个 DROP TABLE， 然后为新月份创建一个新的子表。
我们必须增加非重叠的表约束，所以我们的建表脚本就变成：
&&&& CREATE TABLE measurement_yy04mm02 (
&&&&&&&& CHECK ( logdate &= DATE '' AND logdate & DATE '' )
&&&& ) INHERITS (measurement);
&&&& CREATE TABLE measurement_yy04mm03 (
&&&&&&&& CHECK ( logdate &= DATE '' AND logdate & DATE '' )
&&&& ) INHERITS (measurement);
&&&& CREATE TABLE measurement_yy05mm11 (
&&&&&&&& CHECK ( logdate &= DATE '' AND logdate & DATE '' )
&&&& ) INHERITS (measurement);
&&&& CREATE TABLE measurement_yy05mm12 (
&&&&&&&& CHECK ( logdate &= DATE '' AND logdate & DATE '' )
&&&& ) INHERITS (measurement);
&&&& CREATE TABLE measurement_yy06mm01 (
&&&&&&&& CHECK ( logdate &= DATE '' AND logdate & DATE '' )
&&&& ) INHERITS (measurement);
我们可能还需要在键字字段上有索引：
&&&& CREATE INDEX measurement_yy04mm02_logdate ON measurement_yy04mm02 (logdate);
&&&& CREATE INDEX measurement_yy04mm03_logdate ON measurement_yy04mm03 (logdate);
&&&& CREATE INDEX measurement_yy05mm11_logdate ON measurement_yy05mm11 (logdate);
&&&& CREATE INDEX measurement_yy05mm12_logdate ON measurement_yy05mm12 (logdate);
&&&& CREATE INDEX measurement_yy06mm01_logdate ON measurement_yy06mm01 (logdate);
我们选择先不建立更多的索引。
如果数据只进入最新的分区，我们可以设置一个非常简单的规则来插入数据。 我们必须每个月都重新定义这个规则，这样它总是指向当前分区。
&&&& CREATE OR REPLACE RULE measurement_current_partition AS
&&&& ON INSERT TO measurement
&&&& DO INSTEAD
&&&&&&&& INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NEW.logdate,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NEW.peaktemp,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NEW.unitsales );
我们可能想插入数据并且想让服务器自动定位应该向哪个分区插入数据。 我们可以用像下面这样的更复杂的规则集来实现这个目标。
&&&& CREATE RULE measurement_insert_yy04mm02 AS
&&&& ON INSERT TO measurement WHERE
&&&&&&&& ( logdate &= DATE '' AND logdate & DATE '' )
&&&& DO INSTEAD
&&&&&&&& INSERT INTO measurement_yy04mm02 VALUES ( NEW.city_id,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NEW.logdate,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NEW.peaktemp,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NEW.unitsales );
&&&& CREATE RULE measurement_insert_yy05mm12 AS
&&&& ON INSERT TO measurement WHERE
&&&&&&&& ( logdate &= DATE '' AND logdate & DATE '' )
&&&& DO INSTEAD
&&&&&&&& INSERT INTO measurement_yy05mm12 VALUES ( NEW.city_id,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NEW.logdate,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NEW.peaktemp,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NEW.unitsales );
&&&& CREATE RULE measurement_insert_yy06mm01 AS
&&&& ON INSERT TO measurement WHERE
&&&&&&&& ( logdate &= DATE '' AND logdate & DATE '' )
&&&& DO INSTEAD
&&&&&&&& INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NEW.logdate,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NEW.peaktemp,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& NEW.unitsales );
请注意每个规则里面的 WHERE 子句正好匹配其分区的 CHECK 约束。
我们可以看出来，一个复杂的分区方案可能要求相当不少的 DDL。 在上面的例子里我们需要每个月创建一次新分区，因此写一个脚本自动生成需要的 DDL 是明智的。
还要注意下面的事项：
目前还没有什么办法校验所有 CHECK 是相互排他的。 数据库设计者必须注意这一点。
目前还没有简单的办法声明数据行绝对不能插入主表。 主表上的一个 CHECK (false) 约束将被所有子表继承，因此不能这么用。一个可行的办法是在主表上设置一个 ON INSERT 触发器，总是抛出错误。（另外，这样的触发器也可以用于重定向数据到合适的子表， 而不是用上面建议的那样一套规则。）
分区也可以使用一个 UNION ALL 视图来安排：
&CREATE VIEW measurement AS
&&&&&&&&&& SELECT * FROM measurement_yy04mm02
&UNION ALL SELECT * FROM measurement_yy04mm03
&UNION ALL SELECT * FROM measurement_yy05mm11
&UNION ALL SELECT * FROM measurement_yy05mm12
&UNION ALL SELECT * FROM measurement_yy06mm01;
不过，约束排除目前还不支持用这种方式定义的分区。 还有，重建试图也给增加和删除数据集里面的独立分区增加了额外的步骤。
[编辑] 分区和约束排除
约束排除（Constraint exclusion）是一种查询优化技巧， 它改进了用上面方法定义的表分区的性能。比如：
&SET constraint_exclusion =
&SELECT count(*) FROM measurement WHERE logdate &= DATE '';
如果没有约束排除，上面的查询会扫描 measurement 表中的每一个分区。 打开了约束排除之后，规划器将检查每个分区的约束然后试图证明该分区不需要被扫描，因为它不能包含任何符合 WHERE 子句条件的数据行。 如果规划器可以证明这个，它就把该分区从查询规划里排除出去。
你可以使用 EXPLAIN 命令显示一个规划在 constraint_exclusion 打开和关闭情况下的不同。用上面方法设置的表的典型的缺省规划是：
&SET constraint_exclusion =
&EXPLAIN SELECT count(*) FROM measurement WHERE logdate &= DATE '';
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& QUERY PLAN
&-----------------------------------------------------------------------------------------------
& Aggregate& (cost=158.66..158.68 rows=1 width=0)
&&& -&& Append& (cost=0.00..151.88 rows=2715 width=0)
&&&&&&&&& -&& Seq Scan on measurement& (cost=0.00..30.38 rows=543 width=0)
&&&&&&&&&&&&&&& Filter: (logdate &= ''::date)
&&&&&&&&& -&& Seq Scan on measurement_yy04mm02 measurement& (cost=0.00..30.38 rows=543 width=0)
&&&&&&&&&&&&&&& Filter: (logdate &= ''::date)
&&&&&&&&& -&& Seq Scan on measurement_yy04mm03 measurement& (cost=0.00..30.38 rows=543 width=0)
&&&&&&&&&&&&&&& Filter: (logdate &= ''::date)
&&&&&&&&& -&& Seq Scan on measurement_yy05mm12 measurement& (cost=0.00..30.38 rows=543 width=0)
&&&&&&&&&&&&&&& Filter: (logdate &= ''::date)
&&&&&&&&& -&& Seq Scan on measurement_yy06mm01 measurement& (cost=0.00..30.38 rows=543 width=0)
&&&&&&&&&&&&&&& Filter: (logdate &= ''::date)
部分或者全部分区可能会使用索引扫描而不是全表扫描， 不过这里要表达的意思是我们没有必要扫描旧的分区旧可以回答这个查询。 在我们打开约束排除之后，我们可以得到生成同样回答的明显节省的规划：
&SET constraint_exclusion =
&EXPLAIN SELECT count(*) FROM measurement WHERE logdate &= DATE '';
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& QUERY PLAN
&-----------------------------------------------------------------------------------------------
& Aggregate& (cost=63.47..63.48 rows=1 width=0)
&&& -&& Append& (cost=0.00..60.75 rows=1086 width=0)
&&&&&&&&& -&& Seq Scan on measurement& (cost=0.00..30.38 rows=543 width=0)
&&&&&&&&&&&&&&& Filter: (logdate &= ''::date)
&&&&&&&&& -&& Seq Scan on measurement_yy06mm01 measurement& (cost=0.00..30.38 rows=543 width=0)
&&&&&&&&&&&&&&& Filter: (logdate &= ''::date)
请注意，约束排除只由 CHECK 约束驱动，而不会由索引驱动。 因此，在键字字段上定义索引是没有必要的。 在给出的分区上是否需要建立索引取决于那些扫描该分区的查询通常是扫描该分区的一大部分还是只是一小部分。 对于后者，索引通常都有帮助，对于前者则没有什么好处。
还有下面的注意：
约束排除只是在查询的 WHERE 子句包含约束的时候才生效。 一个参数化的查询不会被优化，因为在运行时规划器不知道改参数会选择哪个分区。 由于某些原因，像 CURRENT_DATE 这样的&稳定的（stable）&函数必须避免。 把分区键字和另外一个表的字段连接起来也不会得到优化。
在 CHECK 约束里面避免跨数据类型的比较， 因为目前规划器会无法证明这样的条件为假。比如，下面的约束会在 x 是整数字段的时候可用，但是在 x 是一个 bigint 的时候不能用：
CHECK ( x = 1 )
对于 bigint 字段，我们必须使用类似下面这样的约束：
CHECK ( x = 1::bigint )
这个问题并不仅仅局限于 bigint 数据类型 — 它可能会发生在任何约束的缺省数据类型与其比较的字段的数据类型不匹配的场合。 在提交的查询里的跨数据类型的比较通常是 OK 的，只是不能在 CHECK 条件里。
目前，在主表上的 UPDATE 和 DELETE 命令并不执行约束排除。
主表的所有分区上面的所有约束都认为是约束排除了的，因此，大量的分区会显著增加查询规划的时间。
别忘记你仍然需要为每个分区独立运行 ANALYZE。 类似下面的命令
&&&& ANALYZE
是只会处理主表的。
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：13043次
排名：千里之外
原创：17篇
(1)(2)(14)(6)