Bedah Query Lambat dengan EXPLAIN: Kapan Index Tidak Menolong

Query lambat pada database yang datanya terus tumbuh sering memancing solusi refleks: tambah index. Masalahnya, asumsi ini tidak selalu benar. Dalam banyak kasus, index justru tidak dipakai, atau dipakai tetapi tetap lambat karena bentuk query, urutan kolom, pola filter, sorting, dan pagination tidak cocok dengan cara kerja optimizer.

Kalau Anda ingin mendiagnosis bottleneck secara akurat, alat utamanya bukan tebakan, melainkan EXPLAIN dan bila tersedia EXPLAIN ANALYZE. Dari sana kita bisa melihat apakah database melakukan full scan, memakai index scan, membaca terlalu banyak row, melakukan sort mahal, atau membuang banyak row setelah filter. Artikel ini fokus pada satu pertanyaan praktis: kapan index tidak menolong, dan apa yang perlu diubah selain sekadar menambah index.

Kenapa menambah index tidak selalu mempercepat query

Index membantu jika ia bisa mengurangi jumlah data yang perlu dibaca atau menghindari pekerjaan tambahan seperti sort. Kalau tidak, optimizer bisa menilai bahwa membaca tabel secara penuh tetap lebih murah.

Beberapa penyebab umum:

Selectivity rendah: kolom yang nilainya terlalu seragam, misalnya status = 'active' pada 95% row.
Cardinality buruk: jumlah nilai unik sedikit, sehingga index tidak banyak membantu mempersempit hasil.
Query mengambil porsi besar tabel: kalau hasilnya memang banyak, full scan kadang lebih efisien daripada lompat-lompat lewat index.
Filter dan sort tidak selaras dengan urutan kolom pada index.
Fungsi pada kolom membuat index sulit dipakai, misalnya DATE(created_at).
Offset pagination besar: index mungkin dipakai, tetapi database tetap harus melewati banyak row sebelum sampai ke halaman yang diminta.
Statistik optimizer tidak akurat: planner salah memperkirakan biaya karena statistik sudah usang atau distribusi data berubah.

Intinya, bentuk yang tampak “rapi” atau “sudah di-index” belum tentu efisien. Yang harus diuji adalah perilaku aktual query pada data nyata.

Mulai dari gejala bottleneck, bukan dari solusi

Sebelum membuat index baru, kumpulkan gejala yang konkret. Ini penting agar Anda tidak mengobati hal yang salah.

Gejala yang sering muncul di production

Latency endpoint naik seiring pertumbuhan data.
CPU database tinggi saat jam sibuk.
I/O meningkat karena banyak pembacaan row atau page.
Query tertentu mendominasi slow query log.
Halaman awal cepat, tetapi halaman pagination besar sangat lambat.
Setelah menambah index, performa tidak berubah atau malah write menjadi lebih berat.

Data yang perlu dikumpulkan

SQL mentah yang benar-benar dieksekusi aplikasi.
Nilai parameter nyata, bukan contoh sintetis.
Rencana eksekusi dari EXPLAIN atau EXPLAIN ANALYZE.
Jumlah row total, distribusi nilai, dan perkiraan selectivity filter.
Apakah query dominan di-read, sort, join, atau pagination.

Kesalahan umum tim backend adalah langsung berkata, “kolom ini sering dipakai di WHERE, berarti harus di-index”. Itu belum cukup. Kolom yang sering difilter tapi ber-selectivity rendah belum tentu layak menjadi index tunggal.

Membaca EXPLAIN: pertanyaan yang harus dijawab

Format output EXPLAIN berbeda antar database, tetapi prinsip membacanya kurang lebih sama. Fokus pada hal berikut:

Access path: apakah full table scan, index scan, range scan, atau lookup.
Estimated rows dan, bila ada, actual rows: berapa banyak row yang diperkirakan dan benar-benar dibaca.
Filter setelah akses: apakah index hanya membantu sebagian, lalu banyak row dibuang setelahnya.
Sort: apakah database masih perlu filesort, external sort, atau sort operator terpisah.
Coverage: apakah semua kolom yang dibutuhkan tersedia di index, atau database harus kembali membaca tabel utama.
Join order: tabel mana yang dibaca dulu dan bagaimana relasinya mempengaruhi biaya.

EXPLAIN menunjukkan rencana yang diperkirakan optimizer. EXPLAIN ANALYZE, jika didukung, lebih kuat karena mengeksekusi query dan menunjukkan waktu aktual serta jumlah row aktual. Ini berguna untuk menemukan salah estimasi akibat statistik atau distribusi data yang tidak merata.

Contoh 1: index ada, tapi full scan tetap dipilih

Misalkan ada tabel transaksi yang terus tumbuh:

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  total NUMERIC(12,2) NOT NULL
);

CREATE INDEX idx_orders_status ON orders(status);

Lalu ada query:

SELECT id, customer_id, total
FROM orders
WHERE status = 'paid';

Secara intuitif, index pada status terlihat masuk akal. Tetapi bila 90% data berstatus paid, query ini tetap membaca hampir seluruh tabel. Optimizer bisa memilih full scan karena:

Menggunakan index lalu kembali ke tabel untuk hampir semua row bisa lebih mahal.
Pembacaan berurutan dari tabel penuh kadang lebih murah daripada banyak lookup acak.
Filter tidak cukup selektif untuk memangkas data secara signifikan.

Di sinilah konsep selectivity penting. Index paling berguna ketika kondisi filter menyisakan sebagian kecil row. Kalau hasil query sudah mendekati mayoritas tabel, full scan bukan berarti optimizer “bodoh”; justru bisa jadi itu keputusan yang benar.

Apa yang perlu diuji

Berapa persentase row untuk setiap nilai status?
Apakah query benar-benar membutuhkan banyak row, atau seharusnya ada filter tambahan seperti rentang waktu?
Apakah yang dicari sebenarnya data terbaru saja?

Sering kali perbaikannya bukan “paksa pakai index”, melainkan mempersempit query agar lebih selektif.

Contoh 2: index tunggal tidak cukup untuk filter + sort

Kasus yang sangat umum:

SELECT id, customer_id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Banyak tim membuat dua index terpisah:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Masalahnya, dua index tunggal tidak otomatis menyelesaikan kebutuhan query di atas. Database mungkin memakai index pada customer_id untuk memfilter, tetapi tetap harus menyortir hasil berdasarkan created_at. Jika seorang customer punya banyak order, biaya sort masih bisa mahal.

Index yang lebih sesuai biasanya index komposit yang mengikuti pola akses query:

CREATE INDEX idx_orders_customer_created_at
ON orders(customer_id, created_at DESC);

Kenapa lebih baik?

Filter dimulai dari customer_id.
Urutan created_at sudah tersedia dalam index untuk subset customer tersebut.
LIMIT 20 bisa berhenti lebih cepat tanpa sort besar.

Kapan urutan kolom index komposit penting

Urutan kolom dalam index komposit bukan kosmetik. Ia harus mengikuti pola akses utama:

Kolom equality filter seperti customer_id = ? biasanya ditempatkan lebih dulu.
Kolom untuk range atau sort seperti created_at diletakkan setelahnya jika memang query memanfaatkannya.
Index (customer_id, created_at) berbeda efeknya dengan (created_at, customer_id).

Kesalahan umum adalah membuat index dari daftar kolom yang “sering dipakai”, tanpa memikirkan urutan dan cara optimizer menavigasi index tersebut.

Contoh 3: covering index mengurangi lookup tambahan

Anggap query yang sering dipakai:

SELECT customer_id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Jika index hanya (customer_id, created_at), database mungkin masih perlu membaca tabel utama untuk mengambil total. Pada volume besar, lookup tambahan ini bisa terasa.

Dalam beberapa database, Anda bisa mempertimbangkan index yang mencakup kolom yang diambil query agar menjadi covering index. Bentuk pastinya bergantung pada mesin database, tetapi prinsipnya sama: semua data yang dibutuhkan query tersedia di struktur index, sehingga akses ke tabel utama bisa dikurangi.

Trade-off-nya:

Ukuran index lebih besar.
Biaya INSERT/UPDATE/DELETE meningkat.
Tidak semua query layak diberi covering index.

Jadi, covering index cocok untuk query yang sangat sering, sangat kritis, dan bentuknya relatif stabil.

Contoh 4: fungsi pada kolom membuat index sulit dipakai

Perhatikan query berikut:

SELECT id, total
FROM orders
WHERE DATE(created_at) = '2025-07-01';

Walaupun ada index pada created_at, penggunaan fungsi DATE() di sisi kolom sering membuat optimizer tidak bisa memanfaatkan index secara efektif. Database harus menghitung fungsi itu untuk banyak row terlebih dahulu.

Versi yang lebih ramah index biasanya berupa rentang waktu:

SELECT id, total
FROM orders
WHERE created_at >= '2025-07-01 00:00:00'
  AND created_at <  '2025-07-02 00:00:00';

Kenapa ini bekerja lebih baik? Karena index pada created_at bisa dipakai sebagai pencarian range, bukan evaluasi fungsi per row.

Offset pagination: index dipakai, tapi tetap makin lambat

Masalah klasik lain adalah pagination seperti ini:

SELECT id, customer_id, created_at, total
FROM orders
ORDER BY created_at DESC
LIMIT 50 OFFSET 50000;

Bahkan bila ada index pada created_at, query ini bisa tetap mahal. Alasannya sederhana: database tetap harus melewati banyak row untuk membuang 50.000 data pertama sebelum mengambil 50 berikutnya.

Jadi di sini masalahnya bukan sekadar “index ada atau tidak”, tetapi bentuk pagination itu sendiri.

Kapan keyset pagination lebih sehat

Jika Anda menampilkan data berurutan berdasarkan kolom yang stabil dan bisa dijadikan penanda, keyset pagination biasanya lebih efisien:

SELECT id, customer_id, created_at, total
FROM orders
WHERE created_at < '2025-07-01 10:15:00'
ORDER BY created_at DESC
LIMIT 50;

Jika ada kemungkinan nilai created_at duplikat, gunakan pasangan kunci yang lebih stabil, misalnya (created_at, id):

SELECT id, customer_id, created_at, total
FROM orders
WHERE (created_at, id) < ('2025-07-01 10:15:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Keuntungannya:

Tidak perlu membuang ribuan row dari halaman sebelumnya.
Lebih stabil untuk dataset yang terus bertambah.
Skala performanya lebih baik pada page depth besar.

Keterbatasannya:

Tidak cocok jika pengguna harus lompat bebas ke halaman nomor sekian.
Perlu cursor atau penanda hasil terakhir di level API.
Sort order harus jelas dan stabil.

Kenapa optimizer kadang benar memilih full scan

Developer sering menganggap full scan selalu buruk. Padahal tidak selalu. Optimizer bisa memilih full scan karena pertimbangan biaya yang valid:

Proporsi row yang lolos filter terlalu besar.
Index tidak menutupi kolom yang diambil, sehingga lookup ke tabel terlalu banyak.
Data tersimpan sedemikian rupa sehingga sequential read lebih murah.
Sort atau join tetap mendominasi biaya total, sehingga index pada filter tidak banyak mengubah hasil akhir.

Karena itu, target investigasi bukan “bagaimana memaksa index dipakai”, melainkan “bagaimana mengurangi total kerja query”. Kadang jawabannya index komposit, kadang rewrite query, kadang ubah pagination, kadang memang menerima full scan untuk laporan tertentu tetapi memindahkannya ke jalur terpisah.

Menguji asumsi dengan EXPLAIN dan EXPLAIN ANALYZE

Pendekatan yang sehat adalah menguji asumsi satu per satu, bukan membuat banyak perubahan sekaligus.

Contoh alur investigasi

Ambil query asli dari slow query log atau tracing aplikasi.
Jalankan EXPLAIN untuk melihat rencana dasar.
Perhatikan apakah akses utama adalah full scan, index scan, atau range scan.
Bandingkan jumlah row estimasi dengan row aktual lewat EXPLAIN ANALYZE bila tersedia.
Uji apakah bottleneck ada di filter, sort, join, atau pagination.
Buat satu perubahan kecil: misalnya rewrite filter tanggal, ubah urutan index komposit, atau ganti offset menjadi keyset.
Jalankan lagi EXPLAIN/ANALYZE dan bandingkan.

Dengan cara ini, Anda menghindari jebakan asumsi “bentuk query terlihat lebih benar, berarti pasti lebih cepat”. Sama seperti banyak masalah rekayasa lain, bentuk yang tampak ideal tidak selalu menghasilkan efisiensi nyata. Yang penting adalah hasil pengukuran pada kondisi produksi yang representatif.

Kapan index komposit lebih tepat daripada banyak index tunggal

Pilih index komposit jika query Anda berulang dengan pola yang konsisten, misalnya:

WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC LIMIT ?
WHERE customer_id = ? AND created_at >= ?
JOIN ... ON a.user_id = b.user_id WHERE a.org_id = ?

Index tunggal cocok jika kolom benar-benar dipakai terpisah oleh banyak query berbeda. Tetapi jika bottleneck utama datang dari satu query penting dengan kombinasi filter dan sort yang tetap, index komposit biasanya lebih efektif.

Hal yang perlu diperhatikan:

Jangan membuat terlalu banyak index “untuk berjaga-jaga”. Semua index punya biaya write dan storage.
Gunakan urutan kolom sesuai pola query dominan, bukan sekadar urutan alfabet atau kebiasaan tim.
Evaluasi apakah query butuh coverage atau cukup untuk filtering saja.

Kesalahan umum saat mendiagnosis query lambat

Hanya melihat ada/tidaknya index, bukan bagaimana index dipakai.
Mengabaikan selectivity dan cardinality kolom.
Membuat index tunggal terpisah untuk query yang sebenarnya butuh index komposit.
Memakai fungsi pada kolom terindeks sehingga range scan gagal dimanfaatkan.
Menganggap offset pagination aman karena query kecil di halaman awal terlihat cepat.
Tidak membandingkan estimated rows vs actual rows.
Mengoptimasi query contoh, bukan query nyata dengan parameter nyata.
Melupakan trade-off write saat menambah banyak index di tabel yang sibuk.

Checklist investigasi query lambat untuk tim backend di production

Gunakan checklist ini sebelum menambah index baru:

Identifikasi query asli
Ambil SQL dan parameter dari slow query log, APM, tracing, atau log aplikasi.
Ukur konteksnya
Catat ukuran tabel, pola pertumbuhan data, dan waktu eksekusi pada kondisi beban yang relevan.
Jalankan EXPLAIN
Lihat access path, estimated rows, join order, dan apakah ada sort terpisah.
Jalankan EXPLAIN ANALYZE jika tersedia
Bandingkan estimasi dengan hasil aktual untuk mendeteksi salah estimasi optimizer.
Periksa selectivity filter
Apakah kondisi WHERE menyisakan sedikit row atau justru mayoritas tabel?
Evaluasi cardinality kolom
Kolom dengan nilai unik rendah sering buruk sebagai index tunggal untuk filtering.
Cek pola filter + sort + limit
Apakah index saat ini benar-benar mendukung ketiganya sekaligus?
Tinjau kemungkinan index komposit
Susun urutan kolom sesuai equality filter, lalu range/sort yang paling dominan.
Periksa kebutuhan covering index
Apakah lookup balik ke tabel utama menjadi biaya besar?
Hindari fungsi pada kolom terindeks
Rewrite ke bentuk range atau kondisi yang lebih ramah index.
Audit pagination
Jika memakai OFFSET besar, uji keyset pagination.
Validasi trade-off write
Pastikan index baru tidak membebani INSERT/UPDATE pada tabel panas.
Uji satu perubahan per langkah
Jangan ubah query, schema, dan konfigurasi sekaligus; sulit mengetahui penyebab hasilnya.
Ulangi pengukuran
Bandingkan plan dan waktu sebelum/sesudah, bukan hanya berdasarkan intuisi.

Penutup

Bedah query lambat dengan EXPLAIN pada dasarnya adalah latihan menguji asumsi. Asumsi paling umum adalah “kalau lambat, tambah index”. Pada kenyataannya, index hanya membantu bila selaras dengan selectivity data, bentuk filter, urutan sort, kolom yang diambil, dan pola pagination.

Jadi, saat performa mulai turun karena database terus tumbuh, jangan berhenti pada pertanyaan “kolom mana yang belum di-index”. Ganti dengan pertanyaan yang lebih tepat: pekerjaan apa yang sebenarnya dilakukan query ini, dan bagaimana EXPLAIN membuktikannya? Dari situ, Anda akan lebih mudah menentukan apakah solusi terbaik adalah full scan yang memang wajar, index komposit, covering index, rewrite filter, atau keyset pagination.

Quick Search

shopping_cart Keranjang