PolarDB for PostgreSQL (Compatible with Oracle) allows you to create global indexes on multi-level partitioned tables and on partition keys. Global indexes maintain a single index across the entire table hierarchy, regardless of how the data is partitioned, enabling efficient queries that filter on non-partition-key columns.
Prerequisites
Before you begin, ensure that you have:
A PolarDB for PostgreSQL (Compatible with Oracle) cluster at revision version V1.1.35 or later
Limits and index selection
Scope: Global indexes can only be created on the root table of a multi-level partitioned table. Creating a global index on a subpartition is not supported.
When to use a global index: Global indexes on partition keys do not always outperform local indexes. Use the following table to choose the right index type:
| Query pattern | Recommended index type | Reason |
|---|---|---|
| Range queries on HASH-partitioned tables | Global index | Avoids scanning all hash partitions |
| Equality queries | Local index | Local index probes fewer partitions |
The optimizer automatically selects the more efficient index. You can also manually specify the index used for queries based on your business scenarios to reduce the workload of the optimizer.
Create global indexes
The following examples use a two-level partitioned table named sales. The table is partitioned by RANGE on date, and each partition is subpartitioned by LIST on country.
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-partition-key column
Create a global index on part_no, which is not a partition key:
CREATE index sales_part_no_idx_global ON sales(part_no) global;Verify the query plan to confirm the global index is used:
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 a partition-key column
Create a global index on date, the RANGE partition key:
CREATE index sales_date_idx_global ON sales(date) global;Verify the query plan:
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)