Example: Partition pruning v18

Suggest edits

The EXPLAIN statement displays the execution plan of a statement. You can use the EXPLAIN statement to confirm that EDB Postgres Advanced Server is pruning partitions from the execution plan of a query.

This example shows the efficiency of partition pruning. Create a simple 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')
);

Perform a constrained query that includes the EXPLAIN statement:

EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'INDIA';

The resulting query plan shows that the server scans only the sales_asia table. That's the table in which a row with a country value of INDIA is stored.

edb=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'INDIA';
Output
                    QUERY PLAN
---------------------------------------------------
 Append
   ->  Seq Scan on sales_asia
         Filter: ((country)::text = 'INDIA'::text)
(3 rows)

Suppose you perform a query that searches for a row that matches a value not included in the partitioning key:

EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE dept_no = '30';

The resulting query plan shows that the server must look in all of the partitions to locate the rows that satisfy the query:

edb=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE dept_no = '30';
Output
                QUERY PLAN
-------------------------------------------
 Append
   ->  Seq Scan on sales_americas
         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)
(7 rows)

Constraint exclusion also applies when querying subpartitioned tables:

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-2015')
  (
    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 any partitions or subpartitions from the search path that can't contain the desired result set:

edb=# EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'US' AND date = 'Dec 12, 2012';
Output
                                                   QUERY PLAN
-----------------------------------------------------------------------------
------------------------------------
 Append
   ->  Seq Scan on sales_americas_2012
         Filter: (((country)::text = 'US'::text) AND (date = '12-DEC-12
         00:00:00'::timestamp without time zone))
(3 rows)
← Prev

Using partition pruning

↑ Up

Using partition pruning

Next →

Handling stray values in a LIST or RANGE partitioned table