PolarDB:Indeks Global

Applicability

Fitur ini didukung pada kluster PolarDB for PostgreSQL yang menjalankan PostgreSQL 14 dengan versi mesin minor 2.0.14.9.15.0 atau lebih baru.

Batasan

• Anda tetap dapat ATTACH atau DETACH subpartisi dari tabel partisi yang memiliki indeks global.

• Tentukan kata kunci GLOBAL dalam pernyataan `CREATE INDEX` untuk membuat indeks global. Jika kata kunci ini tidak ditentukan, indeks lokal akan dibuat secara default.

• Anda dapat membuat indeks global secara konkuren dengan menentukan kata kunci CONCURRENTLY.

• Anda tidak dapat membuat indeks global pada tabel non-partisi atau pada tabel anak yang berisi subpartisi.

• Indeks global tidak mendukung indeks ekspresi.

• Anda tidak dapat membuat indeks global pada kolom kunci partisi dari tabel partisi.

Sintaks

Anda dapat membuat indeks global.

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 ]

Contoh

Percepat query kunci non-partisi

1. Buat tabel partisi dengan kolom waktu sebagai kunci partisi.

   CREATE TABLE partition_range (
       id INT,
       a INT,
       b INT,
       created_date TIMESTAMP WITHOUT TIME ZONE
   ) PARTITION BY RANGE (created_date);
   
   CREATE TABLE partition_range_part01 PARTITION OF partition_range FOR VALUES FROM (MINVALUE) TO ('2020-01-01 00:00:00');
   CREATE TABLE partition_range_part02 PARTITION OF partition_range FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');
   CREATE TABLE partition_range_part03 PARTITION OF partition_range FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00');

2. Lakukan kueri pada tabel partisi berdasarkan kondisi kunci non-partisi.

   EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

   Hasilnya menunjukkan bahwa semua subpartisi dipindai. Fitur pemangkasan partisi tidak dapat digunakan.

                           QUERY PLAN
   ------------------------------------------------------------
    Append
      ->  Seq Scan on partition_range_part01 partition_range_1
            Filter: (id = 1)
      ->  Seq Scan on partition_range_part02 partition_range_2
            Filter: (id = 1)
      ->  Seq Scan on partition_range_part03 partition_range_3
            Filter: (id = 1)
   (7 rows)

3. Buat indeks lokal pada tabel partisi, lalu jalankan kueri lagi.

   CREATE INDEX partition_range_idx_local ON partition_range(id);
   
   EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

   Hasilnya menunjukkan bahwa indeks lokal dari semua subpartisi tetap dipindai karena indeks lokal dibuat pada setiap subpartisi.

                                               QUERY PLAN
   --------------------------------------------------------------------------------------------------
    Append
      ->  Index Scan using partition_range_part01_id_idx on partition_range_part01 partition_range_1
            Index Cond: (id = 1)
      ->  Index Scan using partition_range_part02_id_idx on partition_range_part02 partition_range_2
            Index Cond: (id = 1)
      ->  Index Scan using partition_range_part03_id_idx on partition_range_part03 partition_range_3
            Index Cond: (id = 1)
   (7 rows)

4. Gunakan kata kunci GLOBAL untuk membuat indeks global pada tabel partisi, lalu jalankan kueri lagi.

   CREATE INDEX partition_range_idx_global ON partition_range(id) GLOBAL;
   
   EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

   Hasilnya menunjukkan bahwa database menggunakan indeks global untuk langsung menemukan subpartisi yang berisi data tersebut.

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

Kendala unik untuk kunci non-partisi

Gunakan tabel partisi yang sama seperti pada contoh sebelumnya. Kunci partisinya adalah created_date, sedangkan kolom yang memerlukan kendala unik adalah id.

CREATE UNIQUE INDEX partition_range_id_unique_idx ON partition_range(id);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "partition_range" lacks column "created_date" which is part of the partition key.

Namun, pembatasan ini tidak berlaku saat Anda menambahkan kendala UNIK ke indeks global, seperti yang ditunjukkan di bawah ini.

CREATE UNIQUE INDEX partition_range_id_unique_idx ON partition_range(id) GLOBAL;

Uji Performa

Anda dapat menggunakan pgbench untuk menghasilkan data dengan faktor skala 80000 serta membuat tabel partisi dan tabel non-partisi.

Performa kueri titik pada kunci non-partisi

Performa TPC-B pada kunci non-partisi

Kesimpulan

Indeks global dapat meningkatkan kinerja kueri titik dan pernyataan DML pada tabel partisi hingga satu orde besar.