PolarDB：Buat indeks global

Sintaksis

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 ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

Deskripsi

• Untuk membuat indeks global, Anda harus menentukan kata kunci GLOBAL alih-alih kata kunci LOCAL dalam pernyataan CREATE INDEX.

• Jika Anda tidak menentukan kata kunci GLOBAL atau LOCAL, indeks lokal akan dibuat secara default.

• Anda dapat menentukan CONCURRENTLY dalam pernyataan CREATE INDEX yang membuat indeks global.

• Indeks global tidak dapat dibuat untuk tabel non-partisi atau tabel anak dari tabel partisi.

• Indeks global tidak dapat dibuat berdasarkan ekspresi.

• Indeks global tidak dapat dibuat berdasarkan kolom kunci partisi dari tabel partisi.

Indeks global memberikan manfaat sebagai berikut:

• Indeks global dapat memberikan kendala unik pada kolom kunci non-partisi dari tabel partisi.

• Indeks global dibuat untuk tabel partisi menggunakan kolom kunci non-partisi, memungkinkan percepatan kueri pada tabel partisi dalam skenario di mana kunci partisi tidak ditentukan.

• Anda dapat menggunakan fitur eksekusi paralel lintas node untuk mempercepat pembuatan indeks global dari Indeks B-tree. Untuk informasi lebih lanjut, lihat Percepat Pembuatan Indeks.

Contoh

Dalam contoh berikut, sebuah tabel dipartisi berdasarkan waktu. Partisi dibuat secara berkala untuk menggantikan partisi sebelumnya.

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');
CREATE TABLE partition_range_part04 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part04 FOR VALUES FROM ('2020-03-01 00:00:00') TO ('2020-04-01 00:00:00');
CREATE TABLE partition_range_part05 (
    id integer,
    a int,
    b int,
    created_date timestamp without time zone
);
ALTER TABLE ONLY partition_range ATTACH PARTITION partition_range_part05 FOR VALUES FROM ('2020-04-01 00:00:00') TO ('2020-05-01 00:00:00');

Ketika terdapat sejumlah besar tabel partisi dan kunci partisi created_date tidak ditentukan dalam kueri, kinerja kueri berkurang.

EXPLAIN (costs off) SELECT * FROM partition_range WHERE ID = 6;

Hasil berikut dikembalikan:

                QUERY PLAN                
------------------------------------------
 Append
   ->  Seq Scan on partition_range_part01
         Filter: (id = 6)
   ->  Seq Scan on partition_range_part02
         Filter: (id = 6)
   ->  Seq Scan on partition_range_part03
         Filter: (id = 6)
(7 rows)

Untuk menyelesaikan masalah ini, Anda dapat membuat indeks global untuk meningkatkan kinerja kueri. Jalankan pernyataan berikut untuk membuat indeks global:

CREATE UNIQUE INDEX idx_partition_range_global ON partition_range(id) global;

Setelah indeks global dibuat, kinerja kueri ditingkatkan, seperti yang ditunjukkan dalam contoh berikut:

EXPLAIN (costs off) SELECT * FROM partition_range WHERE ID = 6;

Hasil berikut dikembalikan:

                              QUERY PLAN                               
-----------------------------------------------------------------------
 Global Index Scan using idx_partition_range_global on partition_range
   Index Cond: (id = 6)
(2 rows)

Anda dapat melampirkan partisi ke atau melepas partisi dari tabel partisi tempat indeks global dibuat.

• Lampirkan partisi baru.

  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');

• Lepaskan partisi sebelumnya.

  ALTER TABLE partition_range DETACH PARTITION partition_range_part01;