本文为您介绍分区剪枝示例,以便您掌握使用分区剪枝。

示例:SELECT

EXPLAIN语句用于显示语句的执行计划。您可以使用EXPLAIN语句来确认是否从查询的执行计划中剪去分区。

  1. 首先,创建一张简单表:
    CREATE TABLE sales
    (
      dept_no     number,   
      part_no     varchar2,
      country     varchar2(20),
      date        date,
      amount      number
    )
    PARTITION BY LIST(country)
    (
      PARTITION europe VALUES('FRANCE', 'ITALY'),
      PARTITION asia VALUES('INDIA', 'PAKISTAN'),
      PARTITION americas VALUES('US', 'CANADA')
    );
  2. 然后,执行包含EXPLAIN语句的约束查询:
    EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'INDIA';
    查询计划结果如下,服务器将只扫描表sales_asia,country值为INDIA的记录将会存储到该表中:
    postgres=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'INDIA';
                        QUERY PLAN                     
    ---------------------------------------------------
    
     Append
       ->  Seq Scan on sales
             Filter: ((country)::text = 'INDIA'::text)
       ->  Seq Scan on sales_asia
             Filter: ((country)::text = 'INDIA'::text)
    (5 rows)
  3. 可执行如下查询,搜索与未包括在分区键中的值匹配的记录:
    EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE dept_no = '30';  
    查询计划结果如下,服务器将查询所有的分区,从而定位满足查询的记录:
    postgres=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE dept_no = '30';
                   QUERY PLAN               
    -----------------------------------------
     Append
       ->  Seq Scan on sales
             Filter: (dept_no = 30::numeric)
       ->  Seq Scan on sales_europe
             Filter: (dept_no = 30::numeric)
       ->  Seq Scan on sales_asia
             Filter: (dept_no = 30::numeric)
       ->  Seq Scan on sales_americas
             Filter: (dept_no = 30::numeric)
    (9 rows)
  4. 排除约束在查询子分区表时同样适用:
    CREATE TABLE sales
    (
      dept_no     number,
      part_no     varchar2,
      country     varchar2(20),
      date        date,
      amount      number
    )
    PARTITION BY RANGE(date) SUBPARTITION BY LIST (country)
    (
      PARTITION "2011" VALUES LESS THAN('01-JAN-2012')
      (
        SUBPARTITION europe_2011 VALUES ('ITALY', 'FRANCE'),
        SUBPARTITION asia_2011 VALUES ('PAKISTAN', 'INDIA'),
        SUBPARTITION americas_2011 VALUES ('US', 'CANADA')
      ),
      PARTITION "2012" VALUES LESS THAN('01-JAN-2013')
      (
        SUBPARTITION europe_2012 VALUES ('ITALY', 'FRANCE'),
        SUBPARTITION asia_2012 VALUES ('PAKISTAN', 'INDIA'),
        SUBPARTITION americas_2012 VALUES ('US', 'CANADA')
      ),
      PARTITION "2013" VALUES LESS THAN('01-JAN-2014')
      (
        SUBPARTITION europe_2013 VALUES ('ITALY', 'FRANCE'),
        SUBPARTITION asia_2013 VALUES ('PAKISTAN', 'INDIA'),
        SUBPARTITION americas_2013 VALUES ('US', 'CANADA')
      )
    );
    当您查询这张表时,查询计划器会从搜索路径中剪去任何可能不包含您想要的结果集的分区或子分区。
    postgres=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'US' AND date = 'Dec 12, 2012';
                                     QUERY PLAN                                 
    -----------------------------------------------------------------------------
     Append
       ->  Seq Scan on sales
             Filter: (((country)::text = 'US'::text) AND (date = '12-DEC-12 00:00:00'::timestamp without time zone))
       ->  Seq Scan on sales_2012
             Filter: (((country)::text = 'US'::text) AND (date = '12-DEC-12 00:00:00'::timestamp without time zone))
       ->  Seq Scan on sales_americas_2012
             Filter: (((country)::text = 'US'::text) AND (date = '12-DEC-12 00:00:00'::timestamp without time zone))
    (7 rows)

示例:UPDATE、DELETE

分区表支持update、delete在执行器期间(runtime)执行分区剪枝。
  1. 执行以下命令,创建一张表。
    create table t1_hash (id int , value int) partition by hash(id) partitions 4;
  2. 执行以下命令,进行update操作。
    postgres=# explain update t1_hash set value = value+1 where id = least(1,2);
                                   QUERY PLAN                                
    -------------------------------------------------------------------------
     Update on t1_hash  (cost=0.00..92.18 rows=24 width=14)
       Update on t1_hash_p1
       Update on t1_hash_p2 t1_hash
       ->  Append  (cost=0.00..92.18 rows=24 width=14)
             Subplans Removed: 1
             ->  Seq Scan on t1_hash_p1  (cost=0.00..46.03 rows=12 width=14)
                   Filter: (id = LEAST(1, 2))
    (7 rows)
  3. 执行以下命令,进行delete操作。
    postgres=# explain delete from t1_hash where id = least(1,2);
                                   QUERY PLAN                                
    -------------------------------------------------------------------------
     Delete on t1_hash  (cost=0.00..92.12 rows=24 width=10)
       Delete on t1_hash_p1
       Delete on t1_hash_p2 t1_hash
       ->  Append  (cost=0.00..92.12 rows=24 width=10)
             Subplans Removed: 1
             ->  Seq Scan on t1_hash_p1  (cost=0.00..46.00 rows=12 width=10)
                   Filter: (id = LEAST(1, 2))
    (7 rows)