global secondary index - Hologres

Hologres V4.0 dan versi yang lebih baru mendukung indeks sekunder global. Fitur ini ideal untuk skenario yang memerlukan kueri key-value efisien pada kolom non-primary key. Berbeda dengan indeks primary key, indeks sekunder tidak mengharuskan data bersifat unik, tetapi dapat meningkatkan performa kueri secara signifikan pada kolom tertentu.

Prasyarat

Instans Hologres Anda harus menggunakan V4.0 atau versi yang lebih baru. Jika instans Anda menggunakan versi sebelum V4.0, lihat Upgrade an instance.

Batasan

Kolom indeks sekunder global hanya mendukung tipe data TEXT dan INTEGER.
Anda tidak dapat memodifikasi indeks sekunder global.
Kolom indeks dan kolom INCLUDE tidak boleh mengandung kolom duplikat.
Tabel utama harus memiliki kunci primer untuk mendukung indeks sekunder global.
Jumlah total kolom indeks dan kolom INCLUDE dalam indeks sekunder global tidak boleh melebihi 256.
Hanya tabel internal standar yang mendukung indeks sekunder global. Tabel partisi fisik dan logis tidak mendukung fitur ini.
Anda tidak dapat menghapus atau memodifikasi kolom di tabel utama jika kolom tersebut merupakan bagian dari indeks sekunder global.
Anda tidak dapat memodifikasi table group atau melakukan reshard pada tabel utama yang memiliki indeks sekunder global.
Secara default, indeks sekunder global hanya mendukung penyimpanan Standard (hot storage).
Mode penyimpanan (row store, column store, atau row-column hybrid store) dari indeks sekunder global sama dengan tabel utamanya. Rinciannya sebagai berikut:
- Jika tabel utama menggunakan row store, indeks sekunder global-nya juga menggunakan row store secara default.
- Jika tabel utama menggunakan column store, indeks sekunder global-nya juga menggunakan column store secara default.
- Jika tabel utama menggunakan row-column hybrid store, indeks sekunder global-nya juga menggunakan row-column hybrid store secara default.

Buat indeks sekunder global

Sintaksis

CREATE GLOBAL INDEX [ IF NOT EXISTS ] nama_indeks
  ON [nama_skema.]nama_tabel (nama_kolom_indeks [, ...])
  [ INCLUDE (nama_kolom_include[, ...]) ]

Deskripsi parameter

Parameter	Diperlukan	Deskripsi
index_name	Ya	Nama indeks sekunder global.
schema_name	Tidak	Nama skema dari tabel utama. Jika Anda tidak menentukan parameter ini, nama skema default akan digunakan.
table_name	Ya	Nama tabel utama.
index_column_name	Ya	Kolom indeks dari global secondary index. Tetapkan ke kolom filter yang digunakan untuk point query pada non-primary key.
include_column_name	Tidak	Kolom yang akan disertakan dalam indeks sekunder global.

Catatan Penggunaan
- Setelah Anda meng-commit pernyataan CREATE SQL, sistem mulai membangun indeks. Pernyataan CREATE GLOBAL INDEX baru selesai setelah indeks selesai dibangun dan menjadi terlihat.
- Karena pembuatan indeks setara dengan menulis beberapa salinan data, hal ini memengaruhi performa penulisan. Semakin besar volume data pada tabel utama, semakin besar dampaknya terhadap performa penulisan. Selain itu, semakin banyak kolom yang dimiliki indeks, semakin besar pula dampaknya.
- Saat membuat indeks sekunder global, Anda tidak dapat menentukan skema. Indeks sekunder global dibuat dalam skema yang sama dengan tabel utamanya.
- Agar suatu kueri dapat menggunakan indeks sekunder global, indeks tersebut harus sepenuhnya mencakup semua kolom yang terlibat dalam kueri. Artinya, kolom-kolom tersebut harus berupa kolom indeks atau kolom yang disertakan (included columns).

Hapus indeks sekunder global

Sintaksis
```
DROP INDEX [schema_name.]nama_indeks
```
Deskripsi parameter
Parameter
Diperlukan
Deskripsi
schema_name
Tidak
Nama skema indeks sekunder global. Jika Anda tidak menentukan parameter ini, skema default akan digunakan.
index_name
Ya
Nama indeks sekunder global.

Lihat global secondary index

Lihat indeks sekunder global dalam database saat ini

SELECT 
    n.nspname AS table_namespace,
    t.relname AS table_name,
    i.relname AS index_name
FROM 
    pg_class t
JOIN 
    pg_index ix ON t.oid = ix.indrelid
JOIN 
    pg_class i ON i.oid = ix.indexrelid
JOIN 
    pg_am am ON am.oid = i.relam
JOIN
    pg_namespace n ON n.oid = t.relnamespace 
WHERE 
    t.relkind = 'r'  -- Kueri hanya tabel standar
    AND am.amname = 'globalindex'

Lihat penyimpanan yang digunakan oleh indeks sekunder global
Dalam pernyataan, global_index_name adalah nama indeks sekunder global.
```
SELECT pg_relation_size('schema_name.global_index_name');
```

Lihat kolom yang disertakan dalam indeks sekunder global

SELECT pg_catalog.pg_get_indexdef('global_index_name'::regclass, 0, true);

Contoh

Asumsikan sebuah aplikasi pesanan perlu sering melakukan kueri data berdasarkan prioritas pesanan tertentu. Tabel contoh orders ditunjukkan di bawah ini:

Nama bidang	Tipe	Makna
O_ORDERKEY	BIGINT	Nomor pesanan (kunci primer).
O_CUSTKEY	INT	ID pelanggan (kunci asing terkait dengan tabel CUSTOMER).
O_ORDERSTATUS	CHAR(1)	Status pesanan ('F' = Selesai, 'O' = Terbuka, 'P' = Diproses).
O_TOTALPRICE	DECIMAL(15,2)	Total harga pesanan.
O_ORDERDATE	DATE	Tanggal pembuatan pesanan.
O_ORDERPRIORITY	TEXT	Prioritas pesanan ('1-URGENT', '2-HIGH', dll.).
O_CLERK	TEXT	ID karyawan yang memproses pesanan.
O_SHIPPRIORITY	INT	Prioritas pengiriman (nilai lebih besar menunjukkan prioritas lebih tinggi).
O_COMMENT	TEXT	Komentar pesanan.

Pernyataan SQL berikut digunakan untuk membuat tabel contoh orders.

CREATE TABLE ORDERS
(
    O_ORDERKEY      BIGINT      NOT NULL PRIMARY KEY,
    O_CUSTKEY       INT         NOT NULL,
    O_ORDERSTATUS   CHAR(1)         NOT NULL,
    O_TOTALPRICE    DECIMAL(15,2) NOT NULL,
    O_ORDERDATE     DATE NOT NULL,
    O_ORDERPRIORITY TEXT        NOT NULL,
    O_CLERK         TEXT        NOT NULL,
    O_SHIPPRIORITY  INT         NOT NULL,
    O_COMMENT       TEXT        NOT NULL
) WITH (
  orientation='row,column',
  segment_key='O_ORDERDATE',
  distribution_key='O_ORDERKEY',
  bitmap_columns='O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY',
  dictionary_encoding_columns='o_comment:off,o_orderpriority,o_clerk'
);
COMMENT ON TABLE ORDERS IS 'Tabel pesanan utama yang mencatat informasi dasar pesanan dan status';
COMMENT ON COLUMN ORDERS.O_ORDERKEY IS 'Nomor pesanan (kunci primer)';
COMMENT ON COLUMN ORDERS.O_CUSTKEY IS 'ID pelanggan (kunci asing terkait dengan tabel CUSTOMER)';
COMMENT ON COLUMN ORDERS.O_ORDERSTATUS IS 'Status pesanan (''F'' = Selesai, ''O'' = Terbuka, ''P'' = Diproses)';
COMMENT ON COLUMN ORDERS.O_TOTALPRICE IS 'Total harga pesanan';
COMMENT ON COLUMN ORDERS.O_ORDERDATE IS 'Tanggal pembuatan pesanan';
COMMENT ON COLUMN ORDERS.O_ORDERPRIORITY IS 'Prioritas pesanan (''1-URGENT'', ''2-HIGH'', dll.)';
COMMENT ON COLUMN ORDERS.O_CLERK IS 'ID karyawan yang memproses pesanan';
COMMENT ON COLUMN ORDERS.O_SHIPPRIORITY IS 'Prioritas pengiriman (nilai lebih besar menunjukkan prioritas lebih tinggi)';
COMMENT ON COLUMN ORDERS.O_COMMENT IS 'Komentar pesanan';

Pernyataan SQL berikut digunakan untuk sering melakukan kueri data berdasarkan prioritas pesanan tertentu:

SELECT 
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_ORDERPRIORITY,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'

Anda dapat menggunakan EXPLAIN untuk memeriksa rencana eksekusi pernyataan SQL tersebut:

EXPLAIN
SELECT 
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_ORDERPRIORITY,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'

Hasil berikut dikembalikan:

QUERY PLAN
Gather  (cost=0.00..1.00 rows=1 width=53)
  ->  Local Gather  (cost=0.00..1.00 rows=1 width=53)
        ->  Index Scan using Clustering_index on orders  (cost=0.00..1.00 rows=1 width=53)
              Bitmap Filter: (o_orderpriority = '1-URGENT'::text)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0

Tingkatkan efisiensi kueri dengan menambahkan indeks pada kolom O_ORDERPRIORITY.
Rencana eksekusi yang dikembalikan di atas menunjukkan bahwa kueri menggunakan bitmap index. Bitmap index memberikan peningkatan terbatas terhadap performa kueri. Jika Anda memerlukan queries per second (QPS) yang lebih tinggi, Anda dapat menambahkan indeks pada kolom O_ORDERPRIORITY dari tabel orders.
```
CREATE GLOBAL INDEX idx_orders ON orders(O_ORDERPRIORITY)
INCLUDE (
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
  );
```
Setelah menambahkan indeks, Anda dapat menjalankan pernyataan EXPLAIN lagi untuk memeriksa rencana eksekusi:
```
QUERY PLAN
Local Gather  (cost=0.00..1.76 rows=3035601 width=99)
  ->  Index Scan using Clustering_index on idx_orders  (cost=0.00..1.54 rows=3035601 width=99)
        Shard Prune: Eagerly
        Shards selected: 1 out of 20
        Cluster Filter: (o_orderpriority = '1-URGENT'::text)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0
```
Rencana sekarang menunjukkan bahwa objek Index Scan using Clustering_index on adalah indeks sekunder global idx_orders. Pemangkasan shard (shard pruning) juga digunakan. Hal ini secara signifikan meningkatkan QPS.
Gunakan rencana tetap (fixed plan) untuk meningkatkan QPS lebih lanjut.
```
SET hg_experimental_enable_fixed_dispatcher_for_scan = true;
```
Anda dapat melihat rencana eksekusi untuk melihat optimasi kueri titik (point query) yang menggunakan rencana tetap.