注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

设置 constraint_exclusion 避免扫描 PostgreSQL 分区表所有分区  

2011-03-04 14:26:34|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

       
              熟悉Postgresql的朋友应该知道,PostgreSQL 没有像 Oracle 一样智能的分区表,在PG里
分区表是通过创建子表继承父表和设置插入,删除触发器实现的。 那么PG的分区表性能如何呢?
举个简单的例子,如果查询表里的数据,PG会扫描所有分区。 尽管只查询某个分区的数据,PG
仍然会扫描所有分区,显然,这大大降低了查询性能,如何避免这种情况呢?还好PG里提供了参数
constraint_exclusion , 从而避免扫描所有分区,下面看个例子。


1--创建父表
skytf=> create table test (id integer, name varchar(32));
CREATE TABLE

skytf=> create index idx_test_id on test using btree(id);
CREATE INDEX

2--创建三个子表
skytf=> create table test_a (like test including constraints including defaults including indexes ) inherits (test );
NOTICE:  merging column "id" with inherited definition
NOTICE:  merging column "name" with inherited definition
CREATE TABLE

skytf=> create table test_b (like test including constraints including defaults including indexes ) inherits (test );
NOTICE:  merging column "id" with inherited definition
NOTICE:  merging column "name" with inherited definition
CREATE TABLE

skytf=> create table test_c (like test including constraints including defaults including indexes ) inherits (test );
NOTICE:  merging column "id" with inherited definition
NOTICE:  merging column "name" with inherited definition
CREATE TABLE

3--设置约束
skytf=> alter table test_a add constraint con_test_a check (id >=1 and id <=1000);
ALTER TABLE

skytf=> alter table test_b add constraint con_test_b check (id >=1001 and id <=2000);
ALTER TABLE

skytf=> alter table test_c add constraint con_test_c check (id >=2001 and id <=3000);
ALTER TABLE

4--插入测试数据
skytf=> insert into test_a select generate_series(1,1000),'aaa';
INSERT 0 1000

skytf=> insert into test_b select generate_series(1001,2000),'bbb';
INSERT 0 1000

skytf=> insert into test_c select generate_series(2001,3000),'ccc';
INSERT 0 1000

5--查询数据
skytf=> select * From test_a limit 10;
 id | name
----+------
  1 | aaa
  2 | aaa
  3 | aaa
  4 | aaa
  5 | aaa
  6 | aaa
  7 | aaa
  8 | aaa
  9 | aaa
 10 | aaa
(10 rows)

skytf=> select * From test_b limit 10;
  id  | name
------+------
 1001 | bbb
 1002 | bbb
 1003 | bbb
 1004 | bbb
 1005 | bbb
 1006 | bbb
 1007 | bbb
 1008 | bbb
 1009 | bbb
 1010 | bbb
(10 rows)

skytf=> select * From test_c limit 10;
  id  | name
------+------
 2001 | ccc
 2002 | ccc
 2003 | ccc
 2004 | ccc
 2005 | ccc
 2006 | ccc
 2007 | ccc
 2008 | ccc
 2009 | ccc
 2010 | ccc
(10 rows)


6--查看子表表结构
skytf=> \d test
             Table "skytf.test"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(32) |
Indexes:
    "idx_test_id" btree (id)
Number of child tables: 3 (Use \d+ to list them.)

skytf=> \d test_a
            Table "skytf.test_a"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(32) |
Indexes:
    "test_a_id_idx" btree (id)
Check constraints:
    "con_test_a" CHECK (id >= 1 AND id <= 1000)
Inherits: test

skytf=> \d test_b;
            Table "skytf.test_b"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(32) |
Indexes:
    "test_b_id_idx" btree (id)
Check constraints:
    "con_test_b" CHECK (id >= 1001 AND id <= 2000)
Inherits: test

skytf=> \d test_c;
            Table "skytf.test_c"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(32) |
Indexes:
    "test_c_id_idx" btree (id)
Check constraints:
    "con_test_c" CHECK (id >= 2001 AND id <= 3000)
Inherits: test

7--表分析
skytf=> analyze test;
ANALYZE
skytf=> analyze test_a;
ANALYZE
skytf=> analyze test_b;
ANALYZE
skytf=> analyze test_c;
ANALYZE


8--当constraint_exclusion 设置为 off 时
skytf=> show constraint_exclusion;
 constraint_exclusion
----------------------
 off
(1 row)

skytf=> explain select * From test where id=1;
                                         QUERY PLAN                                         
---------------------------------------------------------------------------------------------
 Result  (cost=2.28..20.60 rows=7 width=53)
   ->  Append  (cost=2.28..20.60 rows=7 width=53)
         ->  Bitmap Heap Scan on test  (cost=2.28..7.80 rows=4 width=86)
               Recheck Cond: (id = 1)
               ->  Bitmap Index Scan on idx_test_id  (cost=0.00..2.28 rows=4 width=0)
                     Index Cond: (id = 1)
         ->  Index Scan using test_a_id_idx on test_a test  (cost=0.00..4.27 rows=1 width=8)
               Index Cond: (id = 1)
         ->  Index Scan using test_b_id_idx on test_b test  (cost=0.00..4.27 rows=1 width=8)
               Index Cond: (id = 1)
         ->  Index Scan using test_c_id_idx on test_c test  (cost=0.00..4.27 rows=1 width=8)
               Index Cond: (id = 1)
(12 rows)

     备注: 从上面看到,PLAN里扫描了 test_a,test_b,test_c 三张表,而 id=1 的记录落在 test_a
 表,理论上只要扫描 test_a 分区就行了,接下来看 constraint_exclusion=partition的情况。


9--设置 constraint_exclusion=partition
skytf=> set constraint_exclusion=partition;
SET

skytf=> show constraint_exclusion;
 constraint_exclusion
----------------------
 partition
(1 row)

skytf=> explain select * From test where id=1;
                                         QUERY PLAN                                         
---------------------------------------------------------------------------------------------
 Result  (cost=2.28..12.07 rows=5 width=70)
   ->  Append  (cost=2.28..12.07 rows=5 width=70)
         ->  Bitmap Heap Scan on test  (cost=2.28..7.80 rows=4 width=86)
               Recheck Cond: (id = 1)
               ->  Bitmap Index Scan on idx_test_id  (cost=0.00..2.28 rows=4 width=0)
                     Index Cond: (id = 1)
         ->  Index Scan using test_a_id_idx on test_a test  (cost=0.00..4.27 rows=1 width=8)
               Index Cond: (id = 1)
(8 rows)

     备注,将 constraint_exclusion 参数设置为 "partition" 后,PLAN只扫描指定分区,
    
10--官网解释
constraint_exclusion (enum)
        Controls the query planner iss use of table constraints to optimize queries. The allowed
values of constraint_exclusion are on (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.

       When this parameter allows it for a particular table, the planner compares query conditions
with the table is CHECK constraints, and omits scanning tables for which the conditions contradict the constraints.     
    
总结: 1 constraint_exclusion 的含义是:当PG生产执行计划时是否考虑表上的约束,这个参数有三个选项
              "off,on ,partition" ,默认参数为 off, 意思不使用表上的 constraint 来生成计划,如果设置成
               on ,则对所有表生效,生成 PLAN 时会考虑表上的 constraint, 建议设置成 partition,只对分区表
               生效,从而避免扫描分区表所有分区。

  评论这张
 
阅读(27522)| 评论(2)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016