Pertemuan ke-6: Optimasi Query dan Indexing di PostgreSQL

Hari ini, kita akan belajar bagaimana menganalisis performa query, menggunakan indeks dengan optimal, dan menghindari bottleneck dalam PostgreSQL.

1️⃣ Analisis Performa Query dengan EXPLAIN ANALYZE

Sebelum mengoptimalkan query, kita harus tahu bagaimana PostgreSQL mengeksekusinya.

Gunakan perintah berikut untuk melihat rencana eksekusi query:

EXPLAIN ANALYZE SELECT * FROM pelanggan WHERE email = 'johndoe@example.com';

Output: PostgreSQL akan menunjukkan apakah query menggunakan Sequential Scan (lambat) atau Index Scan (lebih cepat).

2️⃣ Jenis-Jenis Index di PostgreSQL

Index adalah struktur data yang mempercepat pencarian data. PostgreSQL mendukung beberapa jenis index:

3️⃣ Membuat dan Menggunakan Index

➤ Membuat Index B-Tree (Default)

CREATE INDEX idx_pelanggan_email ON pelanggan(email);

Coba jalankan kembali query:

EXPLAIN ANALYZE SELECT * FROM pelanggan WHERE email = 'johndoe@example.com';

Jika menggunakan Index Scan, berarti indeks bekerja dengan baik! 🚀

➤ Index Multi-Kolom (Composite Index)

Jika sering mencari data berdasarkan nama dan email, buat index multi-kolom:

CREATE INDEX idx_pelanggan_nama_email ON pelanggan(nama, email);

📌 Gunakan Composite Index jika query sering menggunakan kedua kolom secara bersamaan dalam WHERE!

➤ Index untuk Pencarian LIKE (%)

Jika menggunakan LIKE '%nama%', PostgreSQL tidak akan menggunakan indeks biasa.
Solusinya? Gunakan index GIN untuk full-text search!

CREATE INDEX idx_pelanggan_nama_gin ON pelanggan USING GIN (nama gin_trgm_ops);

Lalu, coba query berikut:

SELECT * FROM pelanggan WHERE nama ILIKE '%john%';

Pencarian LIKE akan jauh lebih cepat! 🚀

4️⃣ Menghapus Index yang Tidak Dipakai

Terlalu banyak indeks dapat memperlambat INSERT/UPDATE/DELETE.
Gunakan query ini untuk melihat indeks yang tidak digunakan:

SELECT relname AS index_name,
       idx_scan AS total_scans,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Jika ada indeks yang tidak pernah dipakai, hapus dengan:

DROP INDEX idx_pelanggan_nama_gin;

5️⃣ Tugas Praktik

1️⃣ Gunakan EXPLAIN ANALYZE untuk melihat performa query berikut:

SELECT * FROM pelanggan WHERE email = 'johndoe@example.com';

2️⃣ Buat indeks B-Tree pada email dan ulangi EXPLAIN ANALYZE.
3️⃣ Buat indeks GIN pada nama dan coba query LIKE berikut:

SELECT * FROM pelanggan WHERE nama ILIKE '%john%';

4️⃣ Cek apakah ada indeks yang tidak digunakan, lalu hapus jika perlu.

🎯 Kesimpulan

• Gunakan EXPLAIN ANALYZE untuk menganalisis performa query.
• Index B-Tree bagus untuk pencarian dengan perbandingan (=, <, >).
• Index GIN cocok untuk pencarian teks dan JSON.
• Jangan membuat terlalu banyak indeks karena dapat memperlambat INSERT/UPDATE/DELETE.