PolarDB:Examples

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