Cara Berpikir Saat Memburu Query Lambat di Database Produksi

Cara berpikir saat memburu query lambat di database produksi bukan dimulai dari menebak-nebak indeks yang kurang, melainkan dari disiplin membaca gejala, membatasi ruang masalah, lalu menguji hipotesis satu per satu. Di produksi, query yang lambat sering bukan semata karena SQL “jelek”, tetapi bisa muncul dari pertumbuhan data, distribusi nilai yang berubah, pola akses aplikasi, sorting yang mahal, atau pagination dengan OFFSET besar.

Jika Anda sedang menghadapi lonjakan latensi, CPU database tinggi, atau timeout pada endpoint tertentu, tujuan pertama bukan langsung “mengoptimalkan query”, tetapi menjawab tiga pertanyaan: query mana yang benar-benar bermasalah, mengapa planner memilih rencana itu, dan apakah bottleneck ada di query, skema, atau pola akses aplikasi. Dari sana, barulah optimasi menjadi terarah dan aman.

Mulai dari gejala, bukan dari solusi

Kesalahan paling umum saat investigasi adalah langsung menambah indeks atau mengubah SQL tanpa bukti. Pendekatan yang lebih sehat adalah membaca gejala seperti seorang operator sistem:

Apakah latensi naik mendadak atau bertahap?
Apakah semua endpoint melambat, atau hanya satu fitur?
Apakah bottleneck terjadi pada waktu tertentu, misalnya jam sibuk, batch job, atau setelah deploy?
Apakah query lambat ini sering dieksekusi, atau hanya sesekali tetapi sangat mahal?
Apakah masalah muncul setelah data tumbuh melewati ukuran tertentu?

Dua query dengan durasi sama belum tentu memiliki akar masalah yang sama. Query yang lambat karena full scan pada tabel besar memerlukan penanganan berbeda dari query yang lambat karena sorting di memori habis, lock contention, atau aplikasi melakukan N+1 queries.

Pisahkan gejala aplikasi dan gejala database

Timeout di API belum tentu berarti SQL-nya buruk. Bisa jadi:

Aplikasi menembakkan query yang sama berkali-kali.
Connection pool terlalu kecil atau terlalu besar.
Transaksi terlalu lama menahan lock.
Hasil query besar, tetapi bottleneck ada di serialisasi JSON atau jaringan.
Database cepat untuk satu query, tetapi lambat saat concurrency tinggi.

Karena itu, jangan hanya melihat durasi query tunggal di laptop atau staging. Produksi punya data, distribusi, dan beban yang berbeda.

Metrik pertama yang perlu dicek

Sebelum membuka EXPLAIN, kumpulkan konteks operasional. Tujuannya adalah memahami apakah masalahnya lokal pada satu query atau gejala sistemik.

Metrik tingkat aplikasi

Endpoint atau job mana yang melambat.
P95/P99 latensi, bukan hanya rata-rata.
Jumlah request atau job per menit.
Jumlah query per request.
Error timeout, retry, atau circuit breaker.

Metrik tingkat database

CPU, memori, dan I/O disk.
Jumlah koneksi aktif dan yang menunggu.
Throughput baca/tulis.
Lock wait, deadlock, atau transaction age yang panjang.
Slow query log atau query digest berdasarkan total waktu dan frekuensi.

Dari sini, prioritaskan query berdasarkan dampak total, bukan hanya durasi maksimum. Query 200 ms yang dipanggil 10.000 kali per menit sering lebih berbahaya daripada query 3 detik yang muncul sekali sehari.

Catatan praktis: urutkan investigasi berdasarkan kombinasi frequency × latency × business impact. Ini membantu menghindari optimasi yang menarik secara teknis tetapi kecil dampaknya.

Bentuk hipotesis sebelum menyentuh query

Setelah gejala terkumpul, bentuk hipotesis kerja. Bukan tebakan liar, tetapi dugaan yang bisa dibuktikan atau dipatahkan. Contohnya:

Query melakukan scan terlalu banyak baris karena tidak ada indeks yang cocok.
Indeks ada, tetapi planner salah pilih karena cardinality atau statistik tidak merepresentasikan data saat ini.
Sorting mahal karena ORDER BY tidak didukung indeks.
Pagination dengan OFFSET besar memaksa database melewati banyak baris.
Pola akses aplikasi memecah satu kebutuhan menjadi puluhan query kecil.
Query sebenarnya baik, tetapi data sudah tumbuh melewati asumsi desain awal.

Hipotesis membantu Anda membaca EXPLAIN dengan niat. Tanpa hipotesis, orang sering tenggelam dalam istilah planner tanpa tahu apa yang dicari.

Membaca EXPLAIN: cari jumlah kerja yang dilakukan

EXPLAIN bukan sekadar alat untuk melihat apakah indeks dipakai. Fungsinya adalah memahami berapa banyak kerja yang dilakukan database: berapa baris dibaca, bagaimana join dijalankan, apakah ada sort tambahan, dan seberapa jauh estimasi planner berbeda dari kenyataan.

Contoh query nyata

SELECT id, user_id, total_amount, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

Secara bisnis query ini tampak biasa. Tetapi ada beberapa titik rawan:

Filter gabungan tenant_id, status, dan created_at.
ORDER BY created_at DESC bisa memaksa sort mahal jika indeks tidak mendukung.
OFFSET 5000 berarti database mungkin tetap harus melewati ribuan baris sebelum menemukan 50 yang dikirim.

Apa yang dicari di hasil EXPLAIN

Apakah terjadi sequential scan atau index scan pada tabel besar?
Berapa estimasi baris dan berapa baris aktual jika tersedia actual plan?
Apakah ada langkah sort terpisah?
Apakah join dilakukan dengan urutan yang masuk akal?
Apakah ada operasi yang membuang sangat banyak baris setelah dibaca?

Perhatikan terutama perbedaan antara rows estimated dan rows actual. Jika planner mengira akan memproses puluhan baris tetapi kenyataannya ratusan ribu, masalahnya sering bukan “SQL salah” melainkan statistik, distribusi data, atau cardinality yang tidak sesuai asumsi planner.

Contoh interpretasi

Misalkan planner memilih indeks pada status saja. Secara teori ada indeks, tetapi kalau nilai status = 'paid' mencakup sebagian besar tabel, indeks itu tidak selektif. Database lalu tetap harus membaca banyak baris, memfilter lagi berdasarkan tenant_id dan created_at, lalu melakukan sort. Hasilnya: indeks dipakai, tetapi query tetap lambat.

Inilah alasan mengapa pertanyaan yang benar bukan “apakah indeks dipakai?”, melainkan apakah indeks yang dipakai benar-benar mengurangi kerja.

Membedakan jenis masalah yang sering tertukar

1. Masalah indeks

Ini kasus paling klasik: filter atau join tidak didukung indeks yang relevan. Gejalanya bisa berupa scan banyak baris pada tabel besar, waktu respons memburuk seiring pertumbuhan data, dan latensi sangat sensitif terhadap ukuran tabel.

Contoh query:

SELECT *
FROM invoices
WHERE customer_id = 91827
  AND issued_at >= '2025-01-01';

Jika hanya ada indeks pada issued_at, database mungkin tetap membaca rentang tanggal besar lalu memfilter customer_id. Dalam banyak kasus, indeks komposit lebih berguna daripada beberapa indeks tunggal yang tidak selaras dengan pola filter dan sort nyata.

Pertanyaan praktis:

Kolom mana yang paling selektif?
Apakah urutan kolom pada indeks sesuai dengan pola WHERE dan ORDER BY?
Apakah query mengambil terlalu banyak kolom sehingga indeks tidak cukup membantu?

2. Masalah cardinality dan statistik

Kadang indeks sudah ada, tetapi planner tetap memilih rencana buruk. Ini sering terjadi ketika distribusi data berubah: misalnya dulu setiap tenant memiliki ribuan baris, sekarang satu tenant besar memiliki puluhan juta baris. Secara logika query tidak berubah, tetapi estimasi planner tidak lagi akurat.

Gejalanya:

Rencana eksekusi “aneh” meski indeks tampak benar.
Performa kadang bagus, kadang buruk tergantung parameter.
Estimasi jumlah baris jauh dari kenyataan.

Dalam situasi ini, solusi belum tentu menambah indeks baru. Bisa jadi yang dibutuhkan adalah memperbarui statistik, meninjau distribusi data, atau memecah pola query agar tidak bergantung pada estimasi yang terlalu rapuh.

3. Masalah sort

ORDER BY adalah sumber biaya yang sering diremehkan. Query bisa terlihat sederhana, tetapi jika hasil yang cocok banyak dan database harus menyortir setelah filter, biayanya naik cepat.

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

Jika indeks mendukung tenant_id tetapi tidak urutan created_at yang dibutuhkan, database mungkin membaca banyak baris lalu melakukan sort. Pada volume besar, ini bisa menjadi bottleneck CPU atau memori kerja.

Tanda-tanda:

Ada langkah sort eksplisit di EXPLAIN.
Biaya meningkat drastis saat LIMIT dinaikkan.
Query cepat saat tanpa ORDER BY, tetapi lambat setelah sorting ditambahkan.

4. Masalah pagination OFFSET besar

OFFSET terasa nyaman di sisi API, tetapi semakin besar nilainya, semakin banyak baris yang harus dilewati. Database tidak “melompat” langsung ke baris ke-5000 tanpa kerja.

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

Walaupun hanya 50 baris dikembalikan, database bisa harus membaca dan membuang 5000 baris lebih dulu. Ini sering menjadi penyebab latensi yang memburuk perlahan seiring pengguna membuka halaman lebih jauh.

Alternatif yang lebih efisien adalah keyset pagination atau seek method:

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

Pendekatan ini memanfaatkan posisi terakhir sebagai cursor, sehingga database tidak perlu melewati ribuan baris yang dibuang.

Trade-off: keyset pagination lebih efisien, tetapi kurang cocok jika Anda butuh nomor halaman absolut seperti “halaman 123”.

5. Masalah pola akses aplikasi

Terkadang query individu terlihat baik, tetapi total pola akses buruk. Contoh klasiknya adalah N+1 query:

Ambil 100 order.
Untuk tiap order, ambil customer secara terpisah.
Untuk tiap customer, ambil alamat secara terpisah.

Setiap query mungkin hanya 5–20 ms, tetapi totalnya meledak. Dalam kasus seperti ini, bottleneck bukan di satu query lambat, melainkan di desain akses data aplikasi.

Dampak pertumbuhan data: query yang dulu sehat bisa menjadi sakit

Query produksi sering gagal bukan karena ada perubahan kode besar, tetapi karena asumsi lama diam-diam runtuh. Query yang baik untuk 100 ribu baris belum tentu baik untuk 100 juta baris.

Perhatikan pola berikut:

Filter yang dulu selektif menjadi umum.
Satu tenant atau pelanggan tumbuh jauh lebih besar dari yang lain.
Tabel append-only makin besar sehingga range scan ikut membesar.
Kolom status menjadi berat sebelah, misalnya hampir semua bernilai sama.
Data historis terus aktif tercampur dengan data panas yang sering diakses.

Ini alasan mengapa investigasi query lambat harus selalu mempertimbangkan bentuk data saat ini, bukan hanya definisi skema atau contoh data kecil.

Kapan bottleneck ada di query, skema, atau pola akses aplikasi?

Bottleneck ada di query jika

Satu SQL tertentu dominan di slow log.
Rencana eksekusi menunjukkan scan, sort, atau join yang tidak efisien.
Perbaikan kecil pada bentuk query langsung menurunkan biaya secara nyata.

Bottleneck ada di skema jika

Indeks yang ada tidak merepresentasikan pola akses nyata.
Tabel terlalu lebar sehingga baca data mahal.
Distribusi data atau desain partisi tidak cocok lagi dengan volume sekarang.
Constraint atau relasi membuat update/insert mahal pada jalur panas.

Bottleneck ada di pola akses aplikasi jika

Query individu tidak terlalu mahal, tetapi jumlahnya berlebihan.
Aplikasi selalu mengambil data lebih banyak dari yang dibutuhkan.
Pola pagination, caching, atau prefetch buruk.
Transaksi terlalu panjang dan menahan resource penting.

Diagnosis yang benar sering berakhir pada kombinasi: sedikit ubah query, tambah indeks yang tepat, dan sederhanakan pola akses aplikasi.

Contoh investigasi langkah demi langkah

Temukan query prioritas. Ambil dari slow query log, APM, atau query digest. Urutkan berdasarkan total waktu dan frekuensi.
Petakan ke jalur bisnis. Query ini dipanggil dari endpoint mana? Apakah terkait list, dashboard, search, export, atau background job?
Ukur dengan parameter nyata. Jangan uji hanya dengan tenant kecil atau data kosong.
Baca EXPLAIN. Cari scan besar, sort, join mahal, dan perbedaan estimasi vs aktual.
Bentuk hipotesis tunggal. Misalnya: sorting mahal karena indeks tidak mendukung urutan.
Uji perubahan minimal. Coba rewrite query, kurangi kolom, atau uji indeks kandidat di lingkungan aman.
Perhatikan dampak tulis. Setiap indeks tambahan memperlambat insert/update/delete dan menambah storage.
Deploy aman. Gunakan strategi penambahan indeks yang meminimalkan lock sesuai kemampuan database Anda.
Verifikasi setelah rilis. Pastikan latensi turun pada traffic nyata, bukan hanya di benchmark lokal.

Contoh perbaikan yang sering efektif

Menyesuaikan indeks dengan filter dan sort

Untuk query:

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

Indeks tunggal pada tenant_id, status, dan created_at secara terpisah belum tentu cukup. Dalam banyak kasus, indeks komposit yang mengikuti pola filter dan urutan hasil akan lebih membantu daripada sekadar menumpuk indeks tunggal.

Namun jangan menerapkan aturan buta seperti “semua kolom WHERE masuk indeks”. Anda perlu memeriksa selektivitas, urutan akses, dan apakah pola query tersebut benar-benar hot path.

Mengganti OFFSET dengan cursor

Untuk daftar yang di-scroll pengguna atau API internal, keyset pagination sering memberi hasil jauh lebih stabil:

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

Tambahan kolom tie-breaker seperti id membantu urutan tetap deterministik ketika created_at sama.

Mengurangi lebar hasil

SELECT * memperbesar I/O, memori, dan transfer data. Pada tabel lebar, memilih kolom yang benar-benar dibutuhkan bisa cukup terasa dampaknya, apalagi jika query dipanggil sangat sering.

Menghindari fungsi pada kolom filter jika menghambat penggunaan indeks

Misalnya, membungkus kolom waktu dengan fungsi pada klausa filter bisa membuat database sulit memakai indeks secara efisien. Lebih aman menyusun kondisi rentang yang eksplisit jika memungkinkan.

Anti-pattern umum saat memburu query lambat

Langsung menambah indeks tanpa melihat EXPLAIN. Ini sering menghasilkan indeks mubazir.
Mengoptimalkan berdasarkan rata-rata. Masalah produksi sering muncul di P95/P99 atau tenant terbesar.
Menguji dengan data kecil. Query yang cepat di staging belum tentu sehat di produksi.
Mengabaikan jumlah eksekusi. Query “agak lambat” yang sangat sering bisa lebih mahal dari query sangat lambat yang jarang.
Memaksa hint atau trik spesifik terlalu cepat. Kadang perlu, tetapi sebaiknya menjadi opsi terakhir setelah memahami akar masalah.
Menambah terlalu banyak indeks. Baca memang cepat, tetapi tulis melambat, storage membesar, dan planner punya lebih banyak pilihan yang belum tentu baik.
Tidak memeriksa lock dan transaksi panjang. Query tampak lambat padahal sebenarnya menunggu resource.

Trade-off penambahan indeks

Menambah indeks sering menjadi perbaikan paling nyata, tetapi ada harga yang harus dibayar:

Write amplification: setiap insert, update, dan delete harus memperbarui indeks terkait.
Storage: indeks besar memakan ruang yang tidak kecil.
Maintenance: semakin banyak indeks, semakin kompleks analisis performa.
Planner complexity: lebih banyak opsi tidak selalu berarti rencana lebih baik.

Karena itu, setiap indeks baru sebaiknya dibenarkan oleh query nyata dan metrik nyata. Jika indeks hanya membantu satu laporan bulanan, mungkin lebih baik memindahkan beban laporan ke replica, materialized summary, atau batch terpisah.

Langkah mitigasi aman tanpa downtime besar

Saat sistem sedang panas, Anda sering perlu mitigasi yang aman lebih dulu sebelum redesign penuh. Beberapa langkah yang biasanya realistis:

Batasi scope query. Tambahkan filter waktu atau tenant untuk mengurangi beban sementara.
Turunkan page size. Mengurangi jumlah baris per halaman bisa menstabilkan sistem sambil menunggu perbaikan permanen.
Alihkan endpoint berat ke asynchronous export. Jangan paksa dashboard interaktif memproses query analitik besar.
Tambahkan cache untuk jalur baca yang repetitif. Cocok jika datanya tidak harus benar-benar real-time.
Bangun indeks dengan metode yang meminimalkan lock sesuai engine yang dipakai. Uji dulu di lingkungan yang representatif.
Roll out bertahap. Ubah satu hal, ukur, lalu lanjut. Hindari menggabungkan rewrite query, indeks baru, dan perubahan aplikasi sekaligus jika tidak darurat.

Prinsip penting: pada produksi, perbaikan terbaik bukan yang paling canggih, tetapi yang paling kecil risikonya sambil memberi penurunan beban yang jelas.

Checklist diagnosis query lambat di produksi

Apakah saya tahu query mana yang paling mahal berdasarkan total dampak?
Apakah masalah ini konsisten atau hanya pada parameter tertentu?
Apakah saya sudah memetakan query ke endpoint atau job yang memanggilnya?
Apakah saya sudah melihat EXPLAIN dan memahami scan, join, sort, serta estimasi barisnya?
Apakah masalah utama ini indeks, cardinality, sort, OFFSET besar, atau pola akses aplikasi?
Apakah distribusi data produksi berbeda dari asumsi saat query dibuat?
Apakah solusi saya akan memperburuk performa tulis?
Apakah ada mitigasi berisiko rendah yang bisa dilakukan lebih dulu?
Apakah saya punya cara memverifikasi hasil setelah deploy?

Cara berpikir yang paling berguna

Saat memburu query lambat di database produksi, kemampuan terpenting bukan menghafal trik SQL, melainkan menjaga pikiran tetap tenang dan sistematis. Jangan jatuh ke refleks “tambahkan indeks” atau “rewrite besar-besaran” tanpa bukti. Mulailah dari gejala, ukur dampaknya, baca kerja nyata yang dilakukan database, lalu tanyakan apakah masalahnya ada pada query, skema, atau pola akses aplikasi.

Dengan cara berpikir ini, Anda tidak hanya memperbaiki satu query. Anda membangun kebiasaan investigasi yang tahan terhadap perubahan traffic, pertumbuhan data, dan kompleksitas produksi yang nyata.

Quick Search

shopping_cart Keranjang