PolarDB:Indeks Global

Informasi Latar Belakang

Indeks lokal adalah indeks yang ada di setiap partisi dari tabel terpartisi. Partisi indeks lokal sesuai dengan partisi tabel, sehingga indeks lokal hanya dapat berfungsi sebagai indeks unik jika mencakup kunci partisi tabel.

Indeks global adalah indeks yang dibuat pada tabel root dan dipetakan ke partisi tabel. Untuk tabel terpartisi, sistem harus mengambil data dari partisi fisik tabel yang tersebar di penyimpanan. Dengan indeks global, sistem dapat mengakses data dari beberapa partisi secara kolektif menggunakan indeks global, bukan mengakses partisi secara terpisah.

Indeks global adalah indeks Pohon-B yang dibangun pada tabel root dan tidak berada pada partisi. Indeks ini dapat dibuat pada kolom selain kunci partisi tabel terpartisi serta mendukung kendala unik.

Indeks Terpartisi Global

Indeks terpartisi global sering digunakan pada tabel terpartisi dengan volume data besar, sehingga lebih kompleks untuk dibangun. Indeks terpartisi global berbeda dari indeks global karena tidak dipartisi dengan logika yang sama seperti tabel datanya.

Membuat 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/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)}

Buat indeks global untuk tabel terpartisi.

CREATE unique index m_city_id_idx_global ON measurement(city_id) global;

Buat indeks terpartisi global untuk tabel terpartisi.

CREATE UNIQUE INDEX m_peaktemp_idx_global ON measurement(peaktemp) global
PARTITION BY range (peaktemp)
(
PARTITION m_peaktemp_idx_global_p1 values less than(1000),
PARTITION m_peaktemp_idx_global_p2 values less than(5000),
PARTITION m_peaktemp_idx_global_p3 values less than(MAXVALUE)
);

Catatan

• Sistem membuat indeks global ketika GLOBAL ditentukan.

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

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

• Anda tidak dapat membuat indeks global untuk tabel non-partisi atau tabel anak dari tabel terpartisi.

• Anda tidak dapat membuat indeks global berdasarkan ekspresi.

• Indeks terpartisi global dapat dipartisi berdasarkan rentang atau hash. Sintaksisnya mirip dengan partisi tabel data.

• Indeks terpartisi global tidak dapat dipartisi berdasarkan daftar.

• Indeks terpartisi global hanya dapat dibuat untuk tabel induk dari tabel terpartisi dan dapat dibuat berdasarkan kolom kunci partisi tabel terpartisi.

• Indeks terpartisi global mendukung kendala unik secara global.

• Indeks terpartisi global dipartisi pada awalan kiri kolom indeks.

• Indeks global berbasis rentang mendukung pemindaian terurut.

• Indeks global berbasis hash tidak mendukung pemindaian terurut.

• Indeks terpartisi global dapat digunakan untuk mengimplementasikan pemangkasan partisi berdasarkan pengoptimal dan pelaksana.

Pemindaian Indeks Global

Mirip dengan indeks Pohon-B, ketika sistem melakukan kueri pada kunci indeks, indeks global memberikan performa lebih baik melalui pemindaian indeks.

Indeks global mendukung jenis pemindaian indeks berikut:

• Pemindaian Indeks Global

• Pemindaian Indeks Terpartisi Global

• Pemindaian Hanya Indeks Global

• Pemindaian Hanya Indeks Terpartisi Global

• Pemindaian Bitmap Indeks Global

• Pemindaian Bitmap Indeks Terpartisi Global

• Pemindaian Paralel Indeks Global

• Pemindaian Hanya Indeks Paralel Global

Contoh

• Pemindaian Indeks Global

  explain select * from measurement where city_id = 5;
                                             QUERY PLAN                                           
  ------------------------------------------------------------------------------------------------
   Global Index Scan using m_city_id_idx_global on measurement  (cost=0.12..8.14 rows=1 width=20)
     Index Cond: (city_id = 5)
  (2 rows)

• Pemindaian Indeks Terpartisi Global

  explain select * from measurement where peaktemp = 5;
                                                      QUERY PLAN                                                    
  ------------------------------------------------------------------------------------------------------------------
   Global Partitioned Index Scan(1/3) using m_peaktemp_idx_global on measurement  (cost=0.50..8.52 rows=1 width=20)
     Index Cond: (peaktemp = 5)
  (2 rows)

• Pemindaian Hanya Indeks Global

  explain select city_id from measurement where city_id = 5;
                                               QUERY PLAN                                             
  ----------------------------------------------------------------------------------------------------
   Global Index Only Scan using m_city_id_idx_global on measurement  (cost=0.12..8.14 rows=1 width=4)
     Index Cond: (city_id = 5)
  (2 rows)

• Pemindaian Hanya Indeks Terpartisi Global

  explain select peaktemp from measurement where peaktemp = 5;
                                                        QUERY PLAN                                                      
  ----------------------------------------------------------------------------------------------------------------------
   Global Partitioned Index Only Scan(1/3) using m_peaktemp_idx_global on measurement  (cost=0.12..8.14 rows=1 width=4)
     Index Cond: (peaktemp = 5)
  (2 rows)

• Pemindaian Bitmap Indeks Global

  explain select * from measurement where city_id > 5 and city_id < 10000;
                                             QUERY PLAN                                           
  ------------------------------------------------------------------------------------------------
   Append  (cost=1.03..14.47 rows=32 width=20)
     ->  Bitmap Heap Scan on measurement_y2023q1  (cost=1.03..3.58 rows=8 width=20)
           Recheck Cond: ((city_id > 5) AND (city_id < 10000))
           Filter: ((city_id > 5) AND (city_id < 10000))
           ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((city_id > 5) AND (city_id < 10000))
     ->  Bitmap Heap Scan on measurement_y2023q2  (cost=1.03..3.58 rows=8 width=20)
           Recheck Cond: ((city_id > 5) AND (city_id < 10000))
           Filter: ((city_id > 5) AND (city_id < 10000))
           ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((city_id > 5) AND (city_id < 10000))
     ->  Bitmap Heap Scan on measurement_y2023q3  (cost=1.03..3.58 rows=8 width=20)
           Recheck Cond: ((city_id > 5) AND (city_id < 10000))
           Filter: ((city_id > 5) AND (city_id < 10000))
           ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((city_id > 5) AND (city_id < 10000))
     ->  Bitmap Heap Scan on measurement_y2023q4  (cost=1.03..3.58 rows=8 width=20)
           Recheck Cond: ((city_id > 5) AND (city_id < 10000))
           Filter: ((city_id > 5) AND (city_id < 10000))
           ->  Bitmap Global Index Scan on m_city_id_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((city_id > 5) AND (city_id < 10000))
  (21 rows)

• Pemindaian Bitmap Indeks Terpartisi Global

  explain select peaktemp from measurement where peaktemp > 5 and peaktemp< 1000;
                                                      QUERY PLAN                                                    
  ------------------------------------------------------------------------------------------------------------------
   Append  (cost=1.03..14.47 rows=32 width=4)
     ->  Bitmap Heap Scan on measurement_y2023q1  (cost=1.03..3.58 rows=8 width=4)
           Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
           Filter: ((peaktemp > 5) AND (peaktemp < 1000))
           ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
     ->  Bitmap Heap Scan on measurement_y2023q2  (cost=1.03..3.58 rows=8 width=4)
           Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
           Filter: ((peaktemp > 5) AND (peaktemp < 1000))
           ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
     ->  Bitmap Heap Scan on measurement_y2023q3  (cost=1.03..3.58 rows=8 width=4)
           Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
           Filter: ((peaktemp > 5) AND (peaktemp < 1000))
           ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
     ->  Bitmap Heap Scan on measurement_y2023q4  (cost=1.03..3.58 rows=8 width=4)
           Recheck Cond: ((peaktemp > 5) AND (peaktemp < 1000))
           Filter: ((peaktemp > 5) AND (peaktemp < 1000))
           ->  Bitmap Global Partitioned Index Scan(1/3) on m_peaktemp_idx_global  (cost=0.00..4.13 rows=1 width=0)
                 Index Cond: ((peaktemp > 5) AND (peaktemp < 1000))
  (21 rows)

Indeks global sebagai kunci utama atau kunci asing

Dalam PolarDB for PostgreSQL (Kompatibel dengan Oracle), setiap kolom tabel terpartisi dapat menjadi kunci utama atau kunci asing, berkat dukungan indeks global. Hal ini memungkinkan karena agar suatu kolom dapat menjadi kunci utama atau kunci asing, kolom tersebut harus memiliki indeks unik. Namun, indeks lokal hanya dapat berfungsi sebagai indeks unik jika mencakup kunci partisi. Jika hanya indeks lokal yang didukung, kunci utama dan kunci asing harus mencakup kunci partisi. Karena indeks global tidak tunduk pada batasan ini, setiap kolom dapat menjadi kunci utama atau kunci asing.

Anda dapat menentukan kunci utama saat membuat tabel terpartisi, dan sistem akan memutuskan apakah akan membuat indeks lokal atau indeks global berdasarkan kunci utama yang ditentukan. Jika kunci utama mencakup kunci partisi, sistem membuat indeks lokal sebagai indeks unik. Jika tidak, indeks global dibuat untuk tujuan tersebut.

Contoh

• Buat tabel terpartisi dengan kunci utama yang ditentukan.

  -- indeks lokal
  CREATE TABLE pk_rel(a int primary key, b int, c int, d int) PARTITION BY RANGE (a);
  
  postgres=# \d pk_rel
                 Table "public.pk_rel"
   Column |  Type   | Collation | Nullable | Default 
  --------+---------+-----------+----------+---------
   a      | integer |           | not null | 
   b      | integer |           |          | 
   c      | integer |           |          | 
   d      | integer |           |          | 
  Partition key: RANGE (a)
  Indexes:
      "pk_rel_pkey" PRIMARY KEY, btree (a)
  Number of partitions: 0
  
  --- indeks global 
  CREATE TABLE pk_rel(a int, b int primary key, c int, d int) PARTITION BY RANGE (a);
  
  postgres=# \d pk_rel
                 Table "public.pk_rel"
   Column |  Type   | Collation | Nullable | Default 
  --------+---------+-----------+----------+---------
   a      | integer |           |          | 
   b      | integer |           | not null | 
   c      | integer |           |          | 
   d      | integer |           |          | 
  Partition key: RANGE (a)
  Indexes:
      "pk_rel_pkey" PRIMARY KEY, btree (b) GLOBAL
  Number of partitions: 0
  

  Catatan

  • Anda juga dapat menggunakan pernyataan ALTER TABLE xxx ADD PRIMARY KEY USING INDEX xxx untuk menentukan jenis indeks yang akan menjadi kunci utama.

  • PolarDB for PostgreSQL (Kompatibel dengan Oracle) memungkinkan setiap kolom tabel terpartisi menjadi kunci asing, tetapi hanya jika kolom tersebut adalah kunci utama atau indeks unik telah dibuat untuk kolom tersebut. Jika kolom tersebut adalah kunci partisi, indeks lokal dibuat. Jika tidak, indeks global dibuat.

• Buat tabel terpartisi dengan kunci asing yang ditentukan.

  CREATE TABLE fk_rel(a int, b int REFERENCES pk_rel(b), c int, d int) PARTITION BY RANGE (a);
  
  postgres=# \d fk_rel
                 Table "public.fk_rel"
   Column |  Type   | Collation | Nullable | Default 
  --------+---------+-----------+----------+---------
   a      | integer |           |          | 
   b      | integer |           |          | 
   c      | integer |           |          | 
   d      | integer |           |          | 
  Partition key: RANGE (a)
  Foreign-key constraints:
      "fk_rel_b_fkey" FOREIGN KEY (b) REFERENCES pk_rel(b)
  Number of partitions: 0

Tes Performa

pgbench digunakan untuk menghasilkan 80.000 baris data, serta membuat tabel terpartisi dan tabel non-partisi.

Kinerja kueri titik pada kunci non-partisi

Kinerja TPC-B pada kunci non-partisi

Kesimpulan

Indeks global dapat meningkatkan performa secara signifikan untuk kueri titik dan pernyataan DML.