Debug Query Lambat yang Hilang Saat EXPLAIN Dijalankan

Debug query lambat yang hilang saat EXPLAIN dijalankan adalah masalah yang nyata di sistem produksi: query timeout, CPU melonjak, atau request menumpuk, tetapi saat diperiksa dengan EXPLAIN, EXPLAIN ANALYZE, atau dijalankan ulang secara manual, query terlihat baik-baik saja. Ini mirip gejala bug yang menghilang saat diamati: tindakan observasi mengubah kondisi sistem.

Penyebabnya biasanya bukan satu hal. Cache yang sudah hangat, parameter query yang berbeda, statistik planner yang berubah, lock yang sudah lepas, atau koneksi dari pool dengan state berbeda dapat membuat bottleneck seolah menghilang. Karena itu, debugging query lambat harus fokus pada kondisi eksekusi sebenarnya, bukan hanya teks SQL-nya.

Mengapa query lambat bisa “sembuh” saat di-debug

1. Cache hangat mengubah biaya I/O

Eksekusi pertama mungkin membaca banyak halaman dari disk, sementara eksekusi kedua memanfaatkan cache OS atau buffer database. Akibatnya, query yang tadinya lambat terlihat cepat saat diuji ulang.

Ini sangat terasa pada:

Join ke tabel besar yang jarang diakses
Scan index yang menyentuh banyak halaman
Query agregasi pada rentang data besar

Implikasinya: satu hasil EXPLAIN ANALYZE pada sistem yang sudah hangat tidak cukup untuk menyimpulkan akar masalah produksi.

2. EXPLAIN dan EXPLAIN ANALYZE bukan kondisi produksi yang identik

EXPLAIN hanya menunjukkan rencana eksekusi yang dipilih planner. Ia tidak menjalankan query. EXPLAIN ANALYZE menjalankan query dan mengukur waktu aktual, tetapi tetap tidak menjamin kondisi identik dengan request asli.

Perbedaannya bisa datang dari:

Parameter yang tidak sama dengan request produksi
Waktu eksekusi berbeda, sehingga beban sistem berbeda
Lock dan kontensi sudah hilang saat diuji
State session atau transaction level tidak sama

Catatan: di produksi, EXPLAIN ANALYZE pada query berat bisa menambah beban karena query benar-benar dijalankan. Gunakan dengan hati-hati.

3. Parameter berbeda, plan berbeda

Dua query dengan bentuk sama bisa menghasilkan plan yang berbeda jika parameter nilainya berbeda. Misalnya, filter pada tenant kecil mungkin cocok memakai index scan, tetapi tenant besar lebih cepat dengan sequential scan atau plan join lain.

Contoh query:

SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = $1
  AND status = $2
  AND created_at >= $3
ORDER BY created_at DESC
LIMIT 50;

Jika satu tenant memiliki 1.000 baris dan tenant lain 50 juta baris, planner bisa memilih plan yang berbeda walau SQL-nya sama. Inilah sebabnya men-debug dengan parameter “contoh” sering menyesatkan.

4. Statistik planner tidak akurat atau belum mutakhir

Planner database memilih plan berdasarkan statistik distribusi data. Jika data berubah cepat, statistik bisa tertinggal. Akibatnya planner memperkirakan jumlah baris secara keliru dan memilih plan yang buruk.

Gejala yang umum:

Perkiraan baris sangat kecil, tetapi aktualnya sangat besar
Nested loop dipilih pada hasil intermediate besar
Index dipilih untuk predicate yang sebenarnya tidak selektif

Perbedaan besar antara estimated rows dan actual rows pada EXPLAIN ANALYZE sering menjadi petunjuk utama.

5. Lock dan blocking sudah hilang

Terkadang query lambat bukan karena plan buruk, tetapi karena menunggu lock. Saat Anda masuk untuk memeriksa, transaction yang memblokir mungkin sudah commit atau rollback, sehingga query kembali normal.

Kasus umum:

Update besar menahan row lock
DDL atau maintenance menunggu atau memegang lock tertentu
Transaction aplikasi terlalu lama sehingga menahan resource

Jika hanya melihat query setelah kejadian lewat, Anda bisa salah menyimpulkan bahwa sumber masalah adalah SQL, padahal akar masalahnya adalah kontensi.

6. Koneksi pool dan state session bisa memengaruhi hasil

Di aplikasi, query berjalan melalui pool koneksi. Session tertentu bisa membawa state yang tidak terlihat saat Anda menguji manual, misalnya:

Transaction masih terbuka
Isolation level berbeda
Prepared statement atau generic/custom plan berbeda
Parameter session tertentu memengaruhi planner atau timeout

Di sisi aplikasi, pool yang terlalu kecil juga bisa membuat gejala seolah-olah query lambat, padahal waktu habis untuk menunggu koneksi, bukan eksekusi SQL itu sendiri.

Prinsip investigasi aman di produksi

Tujuan investigasi adalah menangkap kondisi nyata tanpa memperburuk insiden. Mulailah dari observabilitas yang pasif, lalu naik ke teknik yang lebih invasif hanya jika perlu.

Urutan yang disarankan

Kumpulkan bukti dari slow query log dan metrik database
Identifikasi query fingerprint yang paling mahal atau paling sering timeout
Ambil parameter contoh dari request nyata
Bandingkan execution plan pada parameter lambat vs cepat
Cek kontensi: lock, blocking, dan antrian koneksi pool
Evaluasi index, urutan kolom, dan pola akses data
Verifikasi hasil perbaikan dengan data dan beban yang relevan

Apa yang sebaiknya dihindari saat insiden berlangsung

Menjalankan EXPLAIN ANALYZE berulang pada query berat tanpa batas
Mengubah index atau statistik secara spontan tanpa memahami dampaknya
Membandingkan hasil dari data sampel yang tidak representatif
Menganggap satu eksekusi cepat berarti masalah selesai

Membaca slow query log dengan benar

Slow query log adalah titik awal terbaik karena ia menangkap query pada saat masalah benar-benar terjadi. Yang dicari bukan hanya SQL-nya, tetapi konteksnya:

Durasi total
Waktu tunggu lock, jika tersedia
Jumlah baris yang dipindai dan dikembalikan
Parameter atau pola parameter
Frekuensi kejadian
Host aplikasi atau worker yang terlibat

Contoh pola yang perlu dicurigai:

duration=8421ms rows=50
SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 500000;

Dari log seperti ini, Anda bisa langsung melihat dua sinyal:

OFFSET sangat besar
Query hanya mengembalikan 50 baris, tetapi kemungkinan harus melewati ratusan ribu baris lebih dulu

Jika sistem Anda menyimpan query fingerprint, kelompokkan berdasarkan bentuk query, lalu lihat distribusi durasi per parameter. Sering kali masalah hanya terjadi pada subset tenant, status, atau rentang waktu tertentu.

Cara membandingkan execution plan tanpa tertipu

Bandingkan plan untuk parameter cepat dan parameter lambat

Jangan hanya menjalankan query dengan satu parameter. Ambil minimal dua kasus nyata:

Kasus lambat dari slow log
Kasus normal yang bentuk query-nya sama

Lalu bandingkan:

Metode scan: sequential scan vs index scan vs bitmap scan
Urutan join
Perkiraan baris vs aktual
Sort eksplisit vs hasil sudah sesuai urutan index
Biaya terbesar di node mana

Contoh query dan analisis plan

EXPLAIN ANALYZE
SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Jika planner memilih scan pada index yang sesuai urutan created_at DESC dan berhenti setelah 50 baris cocok, itu bagus. Namun jika ia harus menyaring banyak baris setelah scan, atau melakukan sort besar, index yang ada mungkin tidak cocok dengan pola query.

Misalnya, query di atas lebih cocok dengan index komposit yang mengikuti pola akses:

CREATE INDEX idx_orders_tenant_status_created_at
ON orders (tenant_id, status, created_at DESC);

Mengapa ini membantu:

tenant_id dan status dipakai untuk menyaring
created_at DESC dipakai untuk urutan hasil
Database bisa mengambil baris teratas tanpa sort tambahan

Trade-off: index komposit menambah biaya write, ruang penyimpanan, dan maintenance. Jangan menambah index hanya karena satu query, jika index itu jarang dipakai atau tumpang tindih dengan index lain.

Fokus pada mismatch estimasi

Jika estimated rows jauh lebih kecil daripada actual rows, biasanya ada salah satu dari masalah berikut:

Statistik distribusi data tidak cukup baik
Korelasi antar kolom tidak tertangkap oleh statistik sederhana
Predicate sangat skewed untuk subset data tertentu

Dalam situasi ini, menambah index belum tentu menyelesaikan masalah jika planner tetap salah menaksir cardinality. Anda perlu memastikan statistik database diperbarui dan, bila relevan, mengevaluasi apakah pola query atau desain data perlu diubah.

Lock, blocking, dan koneksi pool: sering disalahartikan sebagai query lambat

Membedakan waktu eksekusi dan waktu menunggu

Satu request API bisa selesai dalam 10 detik, tetapi query intinya mungkin hanya membutuhkan 200 ms. Sisanya habis untuk:

Menunggu koneksi pool
Menunggu lock
Menunggu I/O akibat beban node lain

Karena itu, penting memisahkan metrik berikut:

Waktu tunggu koneksi database
Waktu dihabiskan dalam transaction
Waktu eksekusi statement
Waktu tunggu lock

Contoh anti-pattern di aplikasi

BEGIN;
SELECT * FROM orders WHERE id = 123 FOR UPDATE;
-- aplikasi memanggil layanan eksternal di sini
-- butuh beberapa detik
UPDATE orders SET status = 'paid' WHERE id = 123;
COMMIT;

Masalahnya bukan hanya query SELECT ... FOR UPDATE, tetapi transaction yang terlalu lama menahan lock sambil menunggu operasi non-database. Query lain yang menyentuh baris sama akan tampak “lambat”, padahal sebenarnya “terblokir”.

Tanda bahwa pool koneksi adalah bottleneck

Durasi request tinggi, tetapi query individual tidak terlalu mahal
Lonjakan concurrency menyebabkan timeout sebelum query dikirim
Worker aplikasi menumpuk pada fase acquire connection

Perbaikannya berbeda dengan tuning SQL: Anda perlu meninjau ukuran pool, durasi transaction, dan pola akses database dari aplikasi.

Memilih index yang tepat, bukan sekadar menambah index

Mulai dari pola query nyata

Index yang baik mengikuti cara data diakses: filter, join, dan urutan hasil. Banyak tim menambah index per kolom, padahal query produksi lebih sering membutuhkan index komposit.

Contoh query:

SELECT id, user_id, created_at
FROM invoices
WHERE account_id = 10
  AND state = 'open'
ORDER BY due_date ASC
LIMIT 100;

Index yang lebih relevan daripada index terpisah per kolom:

CREATE INDEX idx_invoices_account_state_due_date
ON invoices (account_id, state, due_date ASC);

Mengapa bukan cukup (account_id) dan (state) terpisah? Karena query membutuhkan kombinasi filter dan urutan. Dengan index komposit yang sesuai, planner punya peluang lebih besar untuk menghindari scan besar dan sort tambahan.

Anti-pattern umum saat memilih index

Mengindeks semua kolom filter tanpa melihat urutan dan selektivitas
Mengabaikan ORDER BY pada query yang memakai LIMIT
Menambah banyak index mirip yang saling tumpang tindih
Tidak menghapus index yang tidak terpakai

Setelah menambah index, verifikasi bahwa query benar-benar menggunakannya dan durasi membaik pada parameter yang sebelumnya lambat.

Masalah klasik: OFFSET besar dan solusi keyset pagination

OFFSET besar sering menjadi sumber query lambat yang tampak acak. Halaman awal cepat, tetapi halaman jauh di belakang makin mahal. Penyebabnya sederhana: database tetap harus melewati banyak baris sebelum mengembalikan hasil yang diminta.

Contoh query bermasalah

SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 500000;

Walau hasil akhirnya hanya 50 baris, database mungkin perlu membaca atau melewati setengah juta baris lebih dulu. Saat cache dingin atau beban tinggi, dampaknya makin parah.

Perbaiki dengan keyset pagination

Alih-alih meminta “halaman ke-10001”, minta “50 baris setelah cursor terakhir”. Contoh:

SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND (created_at, id) < ('2025-01-10 12:00:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Agar efektif, sediakan index yang mendukung pola itu:

CREATE INDEX idx_orders_tenant_created_id
ON orders (tenant_id, created_at DESC, id DESC);

Mengapa keyset pagination lebih stabil

Tidak perlu melewati baris dalam jumlah besar
Biaya per halaman lebih konsisten
Lebih ramah untuk data yang terus bertambah

Trade-off: keyset pagination tidak cocok jika Anda benar-benar membutuhkan navigasi acak ke nomor halaman tertentu. Namun untuk feed, daftar transaksi, log, dan riwayat aktivitas, ini biasanya pilihan yang lebih baik.

Checklist diagnosis query lambat yang sulit direproduksi

Ambil contoh query dari slow log, bukan dari asumsi
Catat parameter asli yang memicu kelambatan
Bandingkan durasi eksekusi pertama dan eksekusi ulang
Periksa apakah ada lock atau blocking pada saat kejadian
Ukur waktu tunggu koneksi pool di aplikasi
Bandingkan execution plan untuk kasus lambat dan cepat
Lihat mismatch antara estimated rows dan actual rows
Evaluasi apakah ORDER BY, LIMIT, dan OFFSET didukung index yang sesuai
Periksa apakah transaction terlalu lama
Verifikasi apakah statistik planner mutakhir
Pastikan perbaikan diuji dengan parameter dan distribusi data yang relevan

Langkah verifikasi setelah perbaikan

Perbaikan belum selesai saat query terlihat cepat sekali di laptop atau staging. Anda perlu membuktikan bahwa bottleneck benar-benar hilang pada kondisi yang menyerupai produksi.

Yang perlu diverifikasi

Durasi p95/p99 query turun pada fingerprint yang sama
Plan stabil untuk parameter yang dulu bermasalah
Tidak ada lonjakan lock wait atau queue koneksi
Beban write tidak memburuk akibat index baru
Pola pagination baru tidak merusak API atau UX

Contoh verifikasi praktis

Ambil 10-20 parameter nyata dari slow log lama
Jalankan di lingkungan yang datanya representatif
Bandingkan plan sebelum dan sesudah perubahan
Pantau metrik database dan aplikasi selama beberapa jam atau hari setelah rilis

Jika perbaikannya adalah keyset pagination, ukur bukan hanya query tunggal, tetapi juga pola akses halaman berturut-turut. Jika perbaikannya adalah index baru, cek dampaknya pada operasi insert/update yang menulis ke tabel tersebut.

Kesalahan umum yang membuat investigasi meleset

Menguji query tanpa parameter asli dari produksi
Mengabaikan lock dan hanya fokus pada SQL text
Menyimpulkan dari satu hasil EXPLAIN ANALYZE
Menambah index tanpa memahami urutan kolom dan ORDER BY
Menganggap semua query lambat adalah masalah planner, padahal pool koneksi penuh
Tidak membedakan query lambat karena scan besar dengan query lambat karena offset besar

Penutup

Saat query lambat “menghilang” ketika diperiksa, biasanya masalahnya bukan sulap, melainkan kondisi eksekusi yang berubah. EXPLAIN tetap penting, tetapi harus dibaca bersama slow query log, parameter nyata, statistik planner, kondisi lock, dan perilaku koneksi pool.

Pendekatan yang paling efektif adalah: rekonstruksi konteks produksi sedekat mungkin, bandingkan plan pada kasus cepat dan lambat, lalu perbaiki pola akses data yang memang bermasalah. Dalam banyak sistem, hasil terbesar datang dari dua hal yang sangat praktis: memilih index komposit yang tepat dan mengganti OFFSET besar dengan keyset pagination.

Quick Search

shopping_cart Keranjang