PostgreSQL, adalah system manajemen open-source database relasional yang popular, menawarkan berbagai macam tipe indeks untuk mengoptimalkan performa query dan meningkatkan efisiensi pengambilan data. Pada artikel ini, kita akan mempelajari bagaimana membuat tipe indeks yang berbeda di PostgreSQL. Ketika memungkinkan, indeks akan diaplikasikan ke "dvdrental" sample database gratis menggunakan baik pernyataan DML serta Navicat for PostgreSQL 16.
1. Index B-Tree:
Index B-Tree adalah tipe index default di PostgreSQL, cocok untuk berbagai macam tipe data, termasuk teks, numerik, dan timestamp. Ini mengorganisir data dalam struktur tree yang seimbang, memfasilitasi range query dengan efisien dan pencarian yang setara. Mari buat index B-Tree pada kolom “customer_id” pada tabel “payment”:
CREATE INDEX btree_customer_id_idx ON payment(customer_id);
Di Navicat Anda akan menemukan indeks pada tab “indexes” dari Table Designer. Untuk membuat indeks di atas, kita akan memasukkan “btree_customer_id_index” pada field Name, pilih “customer_id” untuk “Fields”, dan pilih “B-Tree” dari metode drop-down indeks:
Ini adalah indeks btree_customer_id_index dengan semua field diatas dihuni:
Klik tombol Simpan dengan lalu buat indeks.
2. Index Hash:
Indeks Hash optimal untuk pengecekan yang setara tapi kurang efektif untuk query range. Mereka menggunakan fungsi hash untuk map key untuk memasukkan indeks. Berikut adalah bagaimana untuk membuat indeks Hash pada kolom “film_id” pada tabel “inventory”, pertama menggunakan pernyataan DML:
CREATE INDEX hash_film_id_idx ON inventory USING HASH(film_id);
Dan sekarang menggunakan Navicat:
3. Index GiST:
Indeks Generalized Search Tree (GiST) support berbagai macam tipe data dan query kompleks, membuat mereka serbaguna untuk aplikasi seperti pencarian full-text dan tipe data geometri.
Berikut adalah contoh membuat indeks GiST pada kolom geometri:
CREATE INDEX index_geometry ON table_name USING GIST (geometry_column);
4. Index SP-GiST:
Indeks Space-Partitioned Generalized Search Tree (SP-GiST) cocok untuk tipe data dengan multidimensi atau struktrur hirarki. Mereka mengefisiensikan indeks tree yang tidak seimbang.
Berikut adalah contoh membuat indeks SP-GiST pada kolom tsvector:
CREATE INDEX index_text_search ON table_name USING SPGIST (tsvector_column);
5. Index GIN:
Generalized Inverted Index (GIN) ideal untuk kasus seperti pencarian full-text, tipe array, dan tipe data komposit. Mereka efisien untuk tipe data dengan beberapa key atau komponen. Mari membuat indeks GIN pada kolom “title” pada tabel “film” untuk pencarian full-text:
CREATE INDEX gin_title_idx ON film USING gin(to_tsvector('english', title));
Berikut adalah tab indeks dari tabel “film” di Navicat dengan indeks gin_title_idx ditambahkan:
6. Index BRIN:
Block Range Index (BRIN) cocok untuk tabel besar dengan data yang diurutkan, karena mengindeks range blok data daripada baris individu. Itu efisien untuk kolom dengan korelasi antara value yang berdekatan. Inilah cara membuat indeks BRIN pada kolom "rental_date" di tabel "sewa":
CREATE INDEX brin_rental_date_idx ON rental USING brin(rental_date);
Berikut adalah indeks brin_rental_date_idx di Navicat:
Kesimpulan
PostgreSQL menawarkan berbagai jenis indeks yang melayani beragam jenis data dan persyaratan permintaan. Memahami karakteristik masing - masing jenis indeks membantu administrator dan developer database untuk membuat keputusan yang tepat saat mengoptimalkan kinerja database. Sementara itu, menggunakan alat seperti Navicat for PostgreSQL 16 membuat bekerja dengan indeks lebih mudah.