Create a global index - PolarDB - Alibaba Cloud Documentation Center

Global indexing is an indexing technique for partitioned tables. You can create global indexes on partitioned tables by using non-partition keys. Global indexes can provide unique constraints.

Syntax

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
[ GLOBAL/LOCAL/global_partitioned_index ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]
global_partitioned_index:
    GLOBAL PARTITION BY {RANGE (column_list) (index_partitioning_clause) |
                            HASH (column_list) (hash_partitions_by_quantity)}

Description

To create a global index, you must specify the GLOBAL keyword instead of the LOCAL keyword in the CREATE INDEX statement.
If you do not specify the GLOBAL or LOCAL keyword, a local index is created by default.
You can specify CONCURRENTLY in a CREATE INDEX statement that creates a global index.
You cannot create global indexes for non-partitioned tables or the child tables of partitioned tables.
You cannot create global indexes based on expressions.
You cannot create global indexes based on the partition key columns of partitioned tables.

Global indexes provide the following benefits:

Global indexes can provide unique constraints on the non-partition key columns of partitioned tables.
Global indexes are created for partitioned tables by using non-partition key columns. This allows global indexes to accelerate queries on partitioned tables in scenarios in which no partition keys are specified.
You can use the cross-node parallel execution feature to accelerate the process of creating global indexes of B-tree indexes. For more information, see Use cross-node parallel execution to accelerate index creation.

Global partitioned indexes:

Range-partitioned or hash-partitioned global indexes can be created. The syntaxes are similar to that for partitioning tables.
List-partitioned global indexes cannot be crated.
Global partitioned indexes can be created only for the parent tables of partitioned tables. They can be created based on the partition key columns of partitioned tables.
Global partitioned indexes support globally unique constraints.
A global partitioned index is partitioned on a left prefix of the index columns.
Range-partitioned global indexes support ordered scanning.
Hash-partitioned global indexes do not support ordered scanning.
Hash-partitioned global indexes can be used to implement partition pruning that is based on an equivalent condition.
Range-partitioned global indexes can be used to implement partition pruning based on optimizers and executors.

Examples

In the following examples, a table is partitioned by time. Partitions are created on a regular basis to replace previous partitions.

CREATE TABLE partition_range (    id integer,
    a int,
    b int,
    created_date timestamp without time zone
)
PARTITION BY RANGE (created_date);
CREATE TABLE partition_range_part01 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part01 FOR VALUES FROM (MINVALUE) TO ('2020-01-01 00:00:00');
CREATE TABLE partition_range_part02 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part02 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');
CREATE TABLE partition_range_part03 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part03 FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00');

When a large number of partitioned tables exist, if the created_date partition key is not specified in queries, the query performance is reduced.

SELECT * FROM partition_range WHERE id = $1

To resolve this issue, you can create global indexes to improve query performance. Execute the following statement to create a global index:

CREATE UNIQUE INDEX idx_partition_range_global ON partition_range(id) global;

After the global index is created, the query performance is improved, as shown in the following example:

EXPLAIN (costs off) SELECT * FROM partition_range WHERE ID = 6;
                              QUERY PLAN
-----------------------------------------------------------------------
 Global Index Scan using idx_partition_range_global on partition_range
   Index Cond: (id = 6)
(2 rows)

You can attach partitions to or detach partitions from partitioned tables for which global indexes are created.

Attach a new partition.

CREATE TABLE partition_range_part06 (    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part06 FOR VALUES FROM ('2020-05-01 00:00:00') TO ('2020-06-01 00:00:00');

Detach a previous partition.

ALTER TABLE partition_range DETACH PARTITION partition_range_part01;

Create a unique range-partitioned global index:

CREATE TABLE partition_range (
    a int,
    b int,
    id integer,
    created_date timestamp without time zone
)
PARTITION BY RANGE (created_date);
CREATE TABLE partition_range_part01 (
    a int,
    b int,
    id integer,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part01 FOR VALUES FROM (MINVALUE) TO ('2020-01-01 00:00:00');
CREATE TABLE partition_range_part02 (
    a int,
    b int,
    id integer,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part02 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');

CREATE UNIQUE INDEX idx_partition_range_global ON partition_range(id) global
PARTITION BY range (id)
(
PARTITION idx_gpi_1_min_5 values less than(5),
PARTITION idx_gpi_2_5_10 values less than(10),
PARTITION idx_gpi_3_10_max values less than(maxvalue)
);

Create a unique hash-partitioned global index:

CREATE UNIQUE INDEX global_hash_idx ON partition_range(id) global

PARTITION BY HASH(id) PARTITIONS 8;

Prune partitions.

Range-partitioned global indexes can be used to implement partition punning within specified ranges.

explain (costs off) select id, to_char(created_date, 'YYYYMMDD') from partition_range where id >= 5 and id < 7;

Sample result:

                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Global Partitioned Index Scan(1/3) using idx_partition_range_global on partition_range
   Index Cond: ((id >= 5) AND (id < 7))
(2 rows)

Hash-partitioned global indexes can be used to implement partition punning that is based on an equivalent condition.

explain (costs off) select id, to_char(created_date, 'YYYYMMDD') from partition_range where id = 6;

Sample result:

                              QUERY PLAN
------------------------------------------------------------------------
 Global Partitioned Index Scan(1/3) using idx_global on partition_range
   Index Cond: (id = 6)
(2 rows)

Partitioned global indexes used for pruning: The PREPARE statement uses the initial prune of the executor.

INSERT INTO partition_range (id,a,b,created_date) VALUES(1, 1,1,'2020-01-02');
INSERT INTO partition_range (id,a,b,created_date) VALUES(2, 1,1,'2020-04-02');
INSERT INTO partition_range (id,a,b,created_date) VALUES(3, 1,1,'2020-02-02');
INSERT INTO partition_range (id,a,b,created_date) VALUES(4, 1,1,'2020-05-02');
INSERT INTO partition_range (id,a,b,created_date) VALUES(5, 1,1,'2020-03-02');
INSERT INTO partition_range (id,a,b,created_date) VALUES(6, 1,1,'2019-12-12');
INSERT INTO partition_range (id,a,b,created_date) VALUES(7, 1,1,'2020-05-02');

CREATE UNIQUE INDEX idx_partition_range_global ON partition_range(id) global
PARTITION BY range (id)
(
PARTITION idx_gpi_1_min_5 values less than(5),
PARTITION idx_gpi_2_5_10 values less than(10),
PARTITION idx_gpi_3_10_max values less than(maxvalue)
);

set polar_comp_custom_plan_tries =0;
prepare e1(int) as select * from partition_range where id = $1;
EXECUTE e1(3);

Sample result:

NOTICE:  global partitioned index idx_partition_range_global do initial prune (1/3)
id | a | b |    created_date
----+---+---+--------------------
  3 | 1 | 1 | 02-FEB-20 00:00:00
(1 row)

Partitioned global indexes used for pruning: The PREPARE statement uses the exec prune of the executor.

set polar_comp_custom_plan_tries =0;
prepare e2(int,int) as select * from partition_range where id between $1 and $2 and id >= (select 6);
EXECUTE e2(1,9);

Sample result:

NOTICE:  global partitioned index idx_partition_range_global do exec prune (1/3)
 a | b | id |       created_date
---+---+----+--------------------------
 1 | 1 |  6 | Thu Dec 12 00:00:00 2019
 1 | 1 |  7 | Sat May 02 00:00:00 2020
(2 rows)

Partitioned global indexes used for pruning: Subqueries use the exec prune of the executor.

create table tbl1(col1 int);
insert into tbl1 values (501), (505), (5);
create table tprt (col1 int, col2 int) partition by range (col1);
create table tprt_1 partition of tprt for values from (1) to (501);
create table tprt_2 partition of tprt for values from (501) to (1001);
create table tprt_3 partition of tprt for values from (1001) to (2001);
insert into tprt values (10,501), (550,505),(20,5), (1010, 500);
create index tprt1_idx on tprt_1 (col1);
create index tprt2_idx on tprt_2 (col1);
create index tprt3_idx on tprt_3 (col1);
create index idx_global_tprt on tprt(col2) global
PARTITION BY range (col2)
(
PARTITION idx_global_tprt_less_100 values less than(100),
PARTITION idx_global_tprt_101_200 values less than(200),
PARTITION idx_global_tprt_201_max values less than(maxvalue)
);
set enable_hashjoin = off;
set enable_mergejoin = off;
set enable_material=off;
explain (costs off) select (select col1 from tprt a where a.col2 = b.col1) as x ,b.col1 from tbl1 b;

Sample result:

                                 QUERY PLAN
----------------------------------------------------------------------------
 Seq Scan on tbl1 b
   SubPlan 1
     ->  Global Partitioned Index Scan(3/3) using idx_global_tprt on tprt a
           Index Cond: (col2 = b.col1)
(4 rows)

select (select col1 from tprt a where a.col2 = b.col1) as x ,b.col1 from tbl1 b;

Sample result:

NOTICE:  global partitioned index idx_global_tprt do exec prune (1/3)
NOTICE:  global partitioned index idx_global_tprt do exec prune (1/3)
NOTICE:  global partitioned index idx_global_tprt do exec prune (1/3)
  x  | col1
-----+------
  10 |  501
 550 |  505
  20 |    5
(3 rows)

Partitioned global indexes used for pruning: Joining multiple tables uses the exec prune of the executor.

explain (costs off) select /*+ leading(b t) */ t.col2 ,b.col1 from tbl1 b, tprt t where t.col2 = b.col1;

Sample result:

NOTICE:  prune partition child table tprt_1 after choose global index
NOTICE:  prune partition child table tprt_2 after choose global index
NOTICE:  prune partition child table tprt_3 after choose global index
                                QUERY PLAN
--------------------------------------------------------------------------
 Nested Loop
   ->  Seq Scan on tbl1 b
   ->  Global Partitioned Index Scan(3/3) using idx_global_tprt on tprt t
         Index Cond: (col2 = b.col1)
(4 rows)

select /*+ leading(b t) */ t.col2 ,b.col1 from tbl1 b, tprt t where t.col2 = b.col1;

Sample result:

NOTICE:  prune partition child table tprt_1 after choose global index
NOTICE:  prune partition child table tprt_2 after choose global index
NOTICE:  prune partition child table tprt_3 after choose global index
NOTICE:  global partitioned index idx_global_tprt do exec prune (1/3)
NOTICE:  global partitioned index idx_global_tprt do exec prune (1/3)
NOTICE:  global partitioned index idx_global_tprt do exec prune (1/3)
 col2 | col1
------+------
  501 |  501
  505 |  505
    5 |    5
(3 rows)