PolarDB:Create global indexes on multi-level partitioned tables

Prerequisites

The revision version of the PolarDB for PostgreSQL (Compatible with Oracle) cluster is V1.1.35 or later.

Usage notes

• You can only create a global index on the root table of a multi-level partitioned table. You cannot create the global index on subpartitions.

• When created on partition keys, global indexes may not always perform better than local indexes. For example, for range queries on HASH partitioned tables, global indexes perform better than local indexes, but for equivalence queries, local indexes perform better than global indexes. The optimizer automatically selects the more efficient indexes. You can also manually specify the indexes used for queries based on your business scenarios to reduce the workload of the optimizer.

Example

1. Create a multi-level partitioned 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 q1_2012
         VALUES LESS THAN('2012-Apr-01')
         (
           SUBPARTITION q1_europe VALUES ('FRANCE', 'ITALY'),
           SUBPARTITION q1_asia VALUES ('INDIA', 'PAKISTAN'),
           SUBPARTITION q1_americas VALUES ('US', 'CANADA')
         ),
     PARTITION q2_2012
       VALUES LESS THAN('2012-Jul-01')
         (
           SUBPARTITION q2_europe VALUES ('FRANCE', 'ITALY'),
           SUBPARTITION q2_asia VALUES ('INDIA', 'PAKISTAN'),
           SUBPARTITION q2_americas VALUES ('US', 'CANADA')
         ),
     PARTITION q3_2012
       VALUES LESS THAN('2012-Oct-01')
         (
           SUBPARTITION q3_europe VALUES ('FRANCE', 'ITALY'),
           SUBPARTITION q3_asia VALUES ('INDIA', 'PAKISTAN'),
           SUBPARTITION q3_americas VALUES ('US', 'CANADA')
         ),
     PARTITION q4_2012
       VALUES LESS THAN('2013-Jan-01')
         (
           SUBPARTITION q4_europe VALUES ('FRANCE', 'ITALY'),
           SUBPARTITION q4_asia VALUES ('INDIA', 'PAKISTAN'),
           SUBPARTITION q4_americas VALUES ('US', 'CANADA')
         )
   );

2. Create a global index on the multi-level partitioned table.

   CREATE index sales_part_no_idx_global ON sales(part_no) global ;
   
   explain (costs off) SELECT * FROM sales WHERE part_no = '101';
                           QUERY PLAN                         
   -----------------------------------------------------------
    Global Index Scan using sales_part_no_idx_global on sales
      Index Cond: ((part_no)::text = '101'::text)

3. Create a global index on the partition key.

   CREATE index sales_date_idx_global ON sales(date) global ;
   
   explain (costs off) SELECT * FROM sales WHERE date = '2012-08-01';
                          QUERY PLAN                       
   --------------------------------------------------------
    Global Index Scan using sales_date_idx_global on sales
      Index Cond: (date = '01-AUG-12 00:00:00'::date)