Query yang melambat di lingkungan produksi sering terlihat sebagai gejala penurunan pengalaman pengguna. Analisis Indexing secara sistematis membantu menemukan bottleneck tanpa mengorbankan stabilitas. Dalam 1-2 paragraf ini, kita langsung menjawab: identifikasi query lambat melalui explain plan dan log, lalu perbaiki index berdasarkan pola akses untuk memperkecil scan.

Mengidentifikasi Query Lambat di Produksi

Explain Plan untuk Memahami Jalan Eksekusi

Jalankan EXPLAIN (ANALYZE) untuk query yang bermasalah agar mengetahui apakah database memilih sequential scan, nested loop, atau sort yang mahal. Perhatikan kolom Total Cost, Rows, dan Actual Time. Contoh hasil yang menunjukkan sequential scan mengindikasikan tidak ada index yang digunakan:

EXPLAIN (ANALYZE) SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';

Jika hasil menunjukkan Seq Scan dan Filter menyaring banyak baris, itu sinyal untuk index baru.

Monitoring Slow Query Log

Aktifkan slow query log pada DBMS (misalnya log_min_duration_statement di PostgreSQL atau long_query_time di MySQL) untuk menangkap query yang menempuh waktu lebih lama dari ambang. Gunakan tool seperti pgBadger atau pt-query-digest untuk mengelompokkan query berdasarkan teks, sehingga Anda tahu mana yang paling tenggelam.

Metik Resource sebagai Indikator Pendukung

Perhatikan metrik CPU, I/O, dan buffer hit rate pada periode terjadinya query lambat. Kombinasikan dengan explain plan: jika CPU tinggi sementara query mengakses banyak halaman disk, indeks yang lebih selektif akan mengurangi I/O. Gunakan pg_stat_statements atau performance_schema untuk melihat durasi dan frequency.

Strategi Indexing untuk Mengurangi Scan

Covering Index untuk Menghindari Lookup Tambahan

Index covering menyertakan semua kolom yang dibutuhkan oleh query sehingga engine tidak perlu mengakses tabel utama (heap). Contoh: jika query hanya membaca kolom order_date dan total untuk customer tertentu, buat index:

CREATE INDEX idx_orders_customer_date_total ON orders(customer_id) INCLUDE (order_date, total);

Dengan begitu, query SELECT customer_id, order_date, total FROM orders WHERE customer_id = ? selesai hanya lewat index. Trade-off: index lebih besar, jadi pantau disk dan update cost.

Indeks Komposit untuk Filter Multi-Kolom

Jika query mencantumkan beberapa kondisi, susun index komposit sesuai urutan filter/pengurutan. Misalnya query berdasar status dan created_at:

CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

Pastikan urutan kolom mencerminkan filtering atau sorting utama. Jika status berpola banyak nilai, taruh kolom dengan selektivitas tinggi di depan.

Partial Index untuk Data Skew

Partial index memperkecil ukuran dengan hanya mencakup subset pasangan nilai. Misalnya jika 90% order memiliki status = 'pending', tetapi query sering mencari 'failed':

CREATE INDEX idx_orders_failed_date ON orders(created_at) WHERE status = 'failed';

Partial index mengurangi scan waktu otomatis, tapi pastikan kondisi WHERE selalu cocok dengan query, karena index tidak berlaku fu untuk nilai lain.

Dampak Index pada Pagination dan Batch Read

Pagination berbasis offset bisa mengakses banyak baris tidak efisien. Gunakan index yang mendukung WHERE + ORDER BY untuk cursor pagination:

SELECT * FROM orders WHERE customer_id = ? AND created_at < ? ORDER BY created_at DESC LIMIT 20;

Index komposit (customer_id, created_at DESC) memungkinkan database berjalan dari index tanpa scan besar. Untuk batch read ukuran besar, pertimbangkan covering index agar scan tetap sequential dan menghindari lookup tambahan per baris.

Studi Kasus: Optimasi Query Daftar Order

Tim melihat query laporan order per customer lambat setelah data tumbuh. Explain menunjukkan Seq Scan dan scanning puluhan ribu baris untuk filter customer_id dan status, lalu sort. Pertama, tambahkan index komposit:

CREATE INDEX idx_orders_customer_status_created ON orders(customer_id, status, created_at DESC);

Explain ulang menunjukkan Index Scan dengan cost lebih kecil. Karena query hanya menampilkan beberapa kolom, gunakan covering index dengan INCLUDE (total, shipped_at). Hasilnya: latency turun drastis, serta CPU/I/O turun pada metrik monitoring. Perhatikan trade-off: update statement sedikit lebih lambat karena index tambahan.

Audit Index Rutin untuk Pertumbuhan Data

Pertumbuhan data bisa mengubah pola akses sehingga index yang dulu optimal menjadi tidak relevan. Jadwalkan audit triwulanan:

  • Review slow query log terbaru dan explain plan. Apakah query baru muncul?
  • Gunakan pg_stat_user_indexes untuk melihat index dengan idx_scan rendah—index yang jarang digunakan bisa dihapus.
  • Periksa fragmentasi dan reindex jika perlu setelah load besar.
  • Document usage dan alasan pembuatan index agar tim lain tidak sembarang menambahkan index baru.

Audit ini menjaga indeks relevan dan mencegah beban tulang belakang (maintenance cost) yang tak perlu saat data meningkat.

Kesimpulan

Analisis Indexing untuk mempercepat query lambat membutuhkan langkah berurutan: identifikasi lewat explain plan, log, dan metrik; desain index yang tepat (covering, komposit, partial); serta evaluasi dampak terhadap pagination/batch. Lengkapi dengan studi kasus praktis dan roadmap audit agar kinerja tetap konsisten seiring pertumbuhan data.