Create global indexes on multi-level partitioned tables

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL (Compatible with Oracle) cluster at revision version V1.1.35 or later

Global indexes vs. local indexes

A global index is maintained as a single index structure at the root table level and spans all partitions. A local index is created separately for each partition.

Usage notes

• Create global indexes only on the root table. Global indexes cannot be created on subpartitions.

• On partition key columns, global indexes do not always outperform local indexes:

  • Range queries on HASH-partitioned tables: global indexes perform better (single index probe)

  • Equivalence queries: local indexes perform better

• The optimizer automatically selects the more efficient index. To reduce optimizer overhead, specify the index explicitly in your query based on your workload pattern.

Create global indexes

The following example creates a multi-level partitioned table and then creates global indexes on both a non-key column and a partition key column.

Step 1: Create the 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')
      )
  );

Step 2: Create a global index on a non-key column

CREATE index sales_part_no_idx_global ON sales(part_no) global;

Verify the optimizer uses the global index:

explain (costs off) SELECT * FROM sales WHERE part_no = '101';

Expected output:

                        QUERY PLAN
-----------------------------------------------------------
 Global Index Scan using sales_part_no_idx_global on sales
   Index Cond: ((part_no)::text = '101'::text)

Step 3: Create a global index on the partition key

CREATE index sales_date_idx_global ON sales(date) global;

Verify the optimizer uses the global index:

explain (costs off) SELECT * FROM sales WHERE date = '2012-08-01';

Expected output:

                       QUERY PLAN
--------------------------------------------------------
 Global Index Scan using sales_date_idx_global on sales
   Index Cond: (date = '01-AUG-12 00:00:00'::date)

The global keyword creates the index at the root table level. Global Index Scan in the query plan confirms the optimizer selected the global index.