This topic provides examples on how to use partition pruning.

SELECT

The EXPLAIN statement is used to display the execution plan of a statement. You can execute the EXPLAIN statement to confirm whether partitions are pruned from the execution plan of a query.

  1. Create a list-partitioned table.
    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. Run a constrained query that contains the EXPLAIN statement.
    EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'INDIA';
    The following returned execution plan shows that the server plans to scan only the sales_asia partition that stores the rows whose values for the country column is 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. You can execute the following statement to query the records that match a specific query condition based on the values of a non-partition key column:
    EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE dept_no = '30';  
    The following returned execution plan shows that the server plans to query all partitions and locate the rows that satisfy the query:
    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. Constraint exclusion can also be used for queries on subpartitioned tables. The following statement provides an example on how to create a subpartitioned table:
    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')
      )
    );
    When you query the table, the query planner prunes partitions or subpartitions that do not contain the expected result set from the search path.
    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 and DELETE

You can execute the EXPLAIN statement when you update or delete a partitioned table to prune partitions at runtime.
  1. Run the following command to create a hash-partitioned table:
    create table t1_hash (id int , value int) partition by hash(id) partitions 4;
  2. Run the following command to update the table:
    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. Run the following command to delete the table:
    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)