Use partition pruning on partitioned tables - PolarDB

Use EXPLAIN to verify that partition pruning is working as expected. When pruning is active, the execution plan shows only the partitions that contain matching rows — skipping irrelevant ones entirely.

SELECT

The following examples use a list-partitioned sales table to demonstrate how the query planner behaves when querying on a partition key column versus a non-partition key column.

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')
);

Query on a partition key column

Run a constrained query on the country column, which is the partition key:

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

Expected output:

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)

The planner scans only the sales_asia partition, which stores rows where country is INDIA. The europe and americas partitions are pruned from the search path.

Query on a non-partition key column

Run a query on dept_no, which is not the partition key:

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

Expected output:

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)

Because dept_no is not the partition key, the planner cannot determine which partitions might contain matching rows. It scans all partitions.

Query on a subpartitioned table

Constraint exclusion also applies to subpartitioned tables. Create a table partitioned by date (range) with subpartitions by country (list):

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')
  )
);

Query the table with conditions on both the partition key and the subpartition key:

EXPLAIN (COSTS OFF) SELECT * FROM sales WHERE country = 'US' AND date = 'Dec 12, 2012';

Expected output:

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)

The query planner prunes both partitions and subpartitions that cannot contain matching rows. Of the nine subpartitions across three years, only sales_2012 and sales_americas_2012 remain in the search path.

UPDATE and DELETE

Partition pruning also applies at runtime when updating or deleting rows in a partitioned table.

Create a hash-partitioned table

create table t1_hash (id int , value int) partition by hash(id) partitions 4;

Verify pruning for UPDATE

postgres=# explain update t1_hash set value = value+1 where id = least(1,2);

Expected output:

                                       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)

Verify pruning for DELETE

postgres=# explain delete from t1_hash where id = least(1,2);

Expected output:

                                       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)

In both plans, Subplans Removed: 1 indicates that one partition was pruned at execution time. Because LEAST(1, 2) is a function call whose result is only known at runtime, the planner cannot eliminate the partition at the planning stage — it does so during execution instead. The UPDATE and DELETE operations target only t1_hash_p1 and t1_hash_p2, leaving the remaining partitions untouched.