This topic provides an example about how to use partition pruning.
Examples
The EXPLAIN statement displays the execution plan of a statement. You can use the
EXPLAIN statement to confirm that PolarDB-O is pruning partitions from the execution
plan of a query. To demonstrate the efficiency of partition pruning, first 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')
);
Then, 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 will only scan the sales_asia partition,
in which a row with a country value of INDIA can be stored:
polardb=# 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)
If you perform a query that searches for a row that matches a value not included in
the partition key:
EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE dept_no = '30';
The resulting query plan shows that the server must search through all of the partitions
to locate the rows that satisfy the query:
polardb=# 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)
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-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 any partitions or subpartitions
from the search path that cannot contain the result set:
polardb=# 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)