Example: Partition pruning v16
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';
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';
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';
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)