Query SQL Makin Lambat? Audit Plan Cache, Index, dan Pagination

Query SQL makin lambat biasanya bukan karena databasenya tiba-tiba “jelek”, tetapi karena asumsi lama sudah tidak berlaku: jumlah baris bertambah, distribusi data berubah, execution plan berganti, cache tidak lagi membantu, atau pola pagination memaksa database membaca terlalu banyak row. Gejalanya sering muncul sebagai endpoint yang dulu stabil di puluhan milidetik lalu naik menjadi ratusan milidetik atau detik saat traffic dan data tumbuh.

Kalau masalahnya muncul bertahap, fokus audit sebaiknya ada pada tiga area: execution plan dan plan cache, kualitas index, dan strategi pagination. Di banyak kasus, akar masalahnya bukan satu query yang “rusak”, melainkan perubahan internal seperti cardinality estimate yang meleset, planner memilih akses data yang lebih mahal, atau penggunaan OFFSET besar yang tetap memaksa database berjalan melewati banyak row.

Kenapa query yang dulu cepat bisa melambat?

Degradasi performa query sering mirip dengan sistem yang awalnya efisien lalu berubah perilakunya karena kondisi internal berubah. Di database, analoginya adalah:

Execution plan berubah: planner memilih sequential scan, hash join, atau urutan join yang berbeda karena statistik terbaru menunjukkan estimasi lain.
Cardinality estimate meleset: database memperkirakan hasil filter sedikit, padahal ternyata besar, atau sebaliknya.
Index tidak lagi cocok: index lama masih ada, tetapi tidak mendukung pola WHERE, ORDER BY, dan JOIN saat ini.
Plan cache tidak ideal: query yang diparameterisasi bisa memakai plan generik yang aman untuk banyak nilai, tetapi buruk untuk nilai tertentu.
Pagination tidak skalabel: OFFSET besar membuat database membuang banyak hasil sebelum mengembalikan page yang diminta.
Table bloat atau data historis menumpuk: query operasional harus memindai data aktif bercampur dengan data lama yang jarang dipakai.

Intinya, performa query bukan hanya soal sintaks SQL. Yang menentukan biaya nyata adalah bagaimana optimizer menerjemahkan SQL menjadi rencana eksekusi terhadap ukuran dan distribusi data saat ini.

Gejala nyata yang perlu dicurigai

1. Endpoint lambat hanya pada page besar

Contoh klasik: page 1 cepat, page 1000 lambat. Ini hampir selalu mengarah ke penggunaan LIMIT ... OFFSET ... pada dataset besar.

2. Query lambat hanya untuk parameter tertentu

Misalnya pencarian untuk status umum seperti active lambat, tetapi untuk status langka cepat. Ini tanda kuat adanya perbedaan selektivitas yang tidak tertangani dengan baik oleh planner atau index.

3. Query fluktuatif: kadang cepat, kadang lambat

Bisa dipengaruhi perubahan plan, cache yang berbeda, kontensi I/O, atau statistik yang belum menggambarkan distribusi data terbaru.

4. CPU atau I/O tinggi, tetapi hasil sedikit

Kalau query mengembalikan puluhan row namun membaca ratusan ribu atau jutaan row, biasanya ada masalah di akses data: scan terlalu luas, filter datang terlambat, atau index tidak efektif.

Cara audit cepat: mulai dari EXPLAIN dan ANALYZE

Langkah pertama jangan menebak. Ambil query nyata dari log aplikasi atau slow query log, lalu lihat execution plan. Secara umum:

EXPLAIN menunjukkan rencana yang dipilih optimizer.
EXPLAIN ANALYZE atau padanannya menunjukkan apa yang benar-benar terjadi saat query dijalankan.

Catatan: sintaks dan detail output berbeda antar database engine, tetapi prinsip bacanya sama: lihat metode akses, estimasi vs aktual, urutan join, operasi sort, dan jumlah row yang dibaca.

Apa yang perlu diperhatikan saat membaca plan

Apakah terjadi full scan atau sequential scan?
Jika filter sangat selektif tetapi planner tetap membaca hampir seluruh table, index mungkin tidak dipakai atau tidak cocok.
Bandingkan estimated rows vs actual rows
Perbedaan besar adalah sinyal estimasi kardinalitas buruk. Jika planner mengira 10 row tapi aktual 100000 row, keputusan join dan akses datanya bisa salah total.
Lihat biaya sort
Jika ORDER BY tidak didukung index, database bisa melakukan sort mahal setelah membaca banyak row.
Perhatikan rows removed by filter
Jika database membaca banyak row lalu membuang sebagian besar di filter, berarti filter tidak terdorong efisien ke akses index.
Lihat join yang meledak
Nested loop bisa sangat buruk jika tabel luar menghasilkan banyak row dan tabel dalam tidak punya index yang tepat.

Contoh query yang terlihat aman, tetapi bisa memburuk

SELECT id, customer_id, created_at, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 100000;

Query ini tampak sederhana, tetapi saat data bertambah:

filter status = 'paid' mungkin menghasilkan jutaan row,
ORDER BY created_at DESC bisa memaksa sort besar jika index tidak cocok,
OFFSET 100000 tetap memaksa database melewati 100000 row sebelum mengembalikan 50 row.

Plan cache dan parameter sensitivity

Pada banyak database, query yang sama dengan parameter berbeda bisa berbagi rencana eksekusi. Ini baik untuk mengurangi biaya parsing dan planning, tetapi ada trade-off: satu plan belum tentu optimal untuk semua nilai parameter.

Contoh pola masalah:

Untuk status = 'failed', data hanya 0.1% dan index scan sangat efisien.
Untuk status = 'paid', data 80% dan sequential scan mungkin justru lebih murah.
Jika plan yang tersimpan cocok untuk salah satu kasus, query dengan parameter lain bisa melambat.

Audit yang perlu dilakukan:

Bandingkan performa query untuk beberapa nilai parameter yang representatif.
Lihat apakah plan berubah antar parameter atau justru terkunci pada pola generik.
Pastikan statistik distribusi kolom tetap segar setelah lonjakan insert/update/delete.

Mitigasi praktis bergantung engine yang dipakai, tetapi prinsip amannya sama:

jangan langsung memaksa hint kecuali benar-benar paham dampaknya,
perbarui statistik bila perlu,
pecah query yang sangat berbeda pola selektivitasnya jika memang kasus bisnisnya berbeda,
evaluasi index yang lebih mencerminkan filter dominan.

Tanda index tidak efektif

1. Ada index, tetapi query tetap scan besar

Keberadaan index tidak otomatis membantu. Index akan efektif jika cocok dengan pola pencarian dan pengurutan. Misalnya query ini:

SELECT id, user_id, created_at
FROM events
WHERE tenant_id = 42
  AND event_type = 'login'
ORDER BY created_at DESC
LIMIT 100;

Kalau index hanya ada di created_at, database mungkin tetap harus membaca terlalu banyak row lalu memfilter tenant_id dan event_type belakangan. Dalam kasus ini, composite index lebih masuk akal.

-- contoh umum
CREATE INDEX idx_events_tenant_type_created_at
ON events (tenant_id, event_type, created_at DESC);

Kenapa ini membantu? Karena database bisa:

langsung mencari subset row sesuai tenant_id dan event_type,
mengambil hasil sesuai urutan created_at tanpa sort tambahan,
berhenti cepat setelah LIMIT terpenuhi.

2. Urutan kolom index tidak sesuai

Composite index sensitif terhadap urutan kolom. Umumnya, kolom yang paling sering dipakai untuk filter presisi tinggi diletakkan lebih awal, lalu kolom untuk pengurutan atau filter tambahan. Tetapi tidak ada aturan tunggal yang berlaku untuk semua beban kerja; selalu verifikasi dengan plan nyata.

3. Query mengambil kolom terlalu banyak

Kalau query hanya butuh beberapa kolom, covering index bisa membantu karena database tidak perlu bolak-balik ke table utama untuk mengambil kolom tambahan.

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

Jika ada index yang mencakup kolom filter, sort, dan kolom hasil yang dibutuhkan, pembacaan bisa jauh lebih efisien. Namun trade-off-nya:

ukuran index membesar,
biaya insert/update naik,
maintenance index bertambah.

4. Fungsi pada kolom mematikan peluang index

Anti-pattern umum:

SELECT *
FROM users
WHERE DATE(created_at) = '2025-01-01';

Ketika kolom dibungkus fungsi, optimizer sering sulit memakai index biasa secara efisien. Lebih aman ubah menjadi rentang:

SELECT *
FROM users
WHERE created_at >= '2025-01-01 00:00:00'
  AND created_at <  '2025-01-02 00:00:00';

5. Predicate tidak sargable

Pencarian seperti LIKE '%kata', operasi aritmetika pada kolom, atau konversi tipe implisit juga sering membuat index kurang efektif. Ini bukan berarti selalu salah, tetapi perlu ekspektasi bahwa scan bisa membesar.

Masalah klasik: OFFSET besar makin mahal seiring data tumbuh

OFFSET tidak “melompat” langsung ke row ke-N. Database tetap harus berjalan melewati row sebelumnya sesuai urutan hasil. Karena itu, page awal bisa cepat, tetapi page jauh akan memburuk.

Contoh pagination yang bermasalah

SELECT id, created_at, title
FROM posts
WHERE tenant_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 200000;

Bahkan jika ada index, database sering tetap harus melintasi banyak entri index untuk membuang 200000 row pertama.

Kapan beralih ke keyset pagination

Jika use case Anda adalah next page atau infinite scroll, keyset pagination hampir selalu lebih stabil dibanding OFFSET. Prinsipnya: gunakan posisi terakhir sebagai cursor.

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

Dengan index yang sesuai, database bisa langsung melanjutkan dari posisi terakhir, bukan menghitung ulang dari awal dataset.

Index yang cocok untuk keyset pagination

CREATE INDEX idx_posts_tenant_created_id
ON posts (tenant_id, created_at DESC, id DESC);

Kenapa kolom id ikut dipakai? Karena saat created_at tidak unik, Anda butuh tie-breaker agar urutan stabil dan page tidak duplikat atau lompat.

Kapan OFFSET masih masuk akal

dataset kecil,
jumlah page terbatas,
kebutuhan bisnis memang harus lompat ke page arbitrer,
latensi masih dapat diterima berdasarkan pengukuran nyata.

Jadi, jangan menghapus OFFSET secara dogmatis. Gantilah saat ia sudah terbukti menjadi bottleneck.

Pilih mitigasi yang tepat: index, keyset, partitioning ringan, atau archiving?

1. Composite index

Pilih ini jika query punya kombinasi tetap antara WHERE, ORDER BY, dan LIMIT. Ini biasanya langkah pertama yang paling aman dan paling sering memberi dampak nyata.

Cocok untuk: query operasional yang sering dipanggil, pola filter relatif stabil, dan hasil kecil tetapi akses data besar.

2. Covering index

Pilih ini jika query sangat sering dieksekusi, kolom yang diambil sedikit, dan pembacaan dari table utama menjadi bottleneck.

Trade-off: menambah ukuran index dan overhead write.

3. Keyset pagination

Pilih ini jika masalah utama ada pada OFFSET besar dan alur pengguna cenderung maju/mundur berurutan, bukan lompat ke nomor halaman acak.

Trade-off: implementasi API lebih kompleks karena perlu cursor, dan UX page-number tradisional jadi kurang natural.

4. Partitioning ringan

Pilih ini jika data besar dan akses sangat dominan ke subset tertentu, misalnya berdasarkan tanggal atau tenant. Tujuannya bukan sekadar membagi tabel, tetapi mengurangi data yang perlu dipindai oleh query rutin.

Catatan penting: partitioning bukan obat universal. Jika query tidak memfilter kolom partisi, manfaatnya bisa kecil. Selain itu kompleksitas operasional meningkat.

5. Archiving

Pilih ini jika sebagian besar data bersifat historis dan jarang dibaca oleh alur utama aplikasi. Memindahkan data lama dari tabel operasional ke tabel arsip sering lebih sederhana daripada partitioning penuh.

Cocok untuk: log, event, transaksi lama, audit trail, atau notifikasi historis.

Contoh diagnosis dan perbaikan bertahap

Kasus

Endpoint daftar order untuk satu tenant melambat dari stabil menjadi sporadis. Query:

SELECT id, customer_id, status, created_at, total_amount
FROM orders
WHERE tenant_id = 10
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 50000;

Gejala

Page awal cukup cepat, page jauh lambat.
EXPLAIN menunjukkan pembacaan row besar sebelum LIMIT terpenuhi.
Estimated rows dan actual rows untuk status = 'paid' berbeda jauh.

Mitigasi aman bertahap

Pastikan statistik up to date
Sebelum mengubah struktur, pastikan planner bekerja dengan statistik yang layak.

Tambahkan composite index yang sesuai

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

Ubah pagination dari OFFSET ke keyset

SELECT id, customer_id, status, created_at, total_amount
FROM orders
WHERE tenant_id = 10
  AND status = 'paid'
  AND (created_at, id) < ('2025-01-10 09:30:00', 123456)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Kurangi kolom jika tidak dibutuhkan
Jangan ambil semua kolom jika UI hanya menampilkan ringkasan.
Pertimbangkan archiving
Jika order lama jarang diakses, pindahkan data historis dari jalur operasional.

Pendekatan bertahap seperti ini lebih aman daripada langsung menambah banyak index atau memaksa hint tanpa verifikasi.

Checklist diagnosis untuk engineer backend

Ambil query nyata dari aplikasi, bukan query “yang kira-kira sama”.
Jalankan EXPLAIN dan bila aman, EXPLAIN ANALYZE.
Bandingkan estimated rows vs actual rows.
Lihat apakah ada scan besar, sort mahal, atau join yang meledak.
Periksa apakah WHERE, ORDER BY, dan LIMIT didukung index yang sama.
Cek apakah query memakai fungsi pada kolom atau predicate yang tidak sargable.
Uji beberapa nilai parameter yang distribusinya berbeda.
Periksa apakah bottleneck hanya muncul pada page dengan OFFSET besar.
Audit kolom yang benar-benar dibutuhkan aplikasi.
Evaluasi apakah data historis seharusnya diarsipkan atau dipisah.

Anti-pattern yang sering membuat query SQL makin lambat

Menambah index tanpa membaca plan
Hasilnya sering hanya memperlambat write tanpa membantu query inti.
Mengandalkan single-column index untuk query multi-kondisi
Planner tetap bisa memilih scan mahal jika kombinasi filter dan sort tidak terlayani.
Memakai OFFSET besar untuk semua daftar
Ini salah satu penyebab paling umum degradasi bertahap.
SELECT *
Mengambil kolom yang tidak diperlukan memperbesar I/O dan mengurangi peluang index-only access.
Memaksa hint terlalu dini
Hint bisa membantu sesaat, tetapi rawan rusak saat distribusi data berubah lagi.
Tidak memisahkan data aktif dan historis
Tabel operasional menjadi semakin berat tanpa manfaat untuk alur utama.

Debugging tips yang aman di produksi

Mulai dari observasi pasif: slow query log, metrik latensi, jumlah row, dan frekuensi query.
Uji perubahan index di staging atau replika bila memungkinkan.
Bandingkan plan sebelum dan sesudah perubahan, bukan hanya waktu total.
Perhatikan dampak ke write path: index tambahan bisa memperlambat insert/update/delete.
Lakukan perubahan satu per satu agar hubungan sebab-akibat jelas.

Prinsip penting: optimasi query yang aman bukan soal mencari trik paling canggih, tetapi memastikan planner membaca data sesedikit mungkin untuk menghasilkan hasil yang sama.

Kesimpulan

Saat query SQL makin lambat seiring pertumbuhan data, penyebabnya sering ada pada perubahan execution plan, estimasi kardinalitas yang tidak akurat, index yang tidak lagi sesuai, atau pagination berbasis OFFSET yang tidak skalabel. Cara menanganinya bukan dengan menebak, melainkan dengan audit plan, memeriksa estimated vs actual rows, memastikan index sesuai pola akses, dan mengganti OFFSET besar dengan keyset pagination bila cocok.

Mulailah dari perubahan yang paling aman dan terukur: segarkan statistik, baca EXPLAIN/ANALYZE, tambahkan composite index yang benar, kurangi kolom yang diambil, lalu evaluasi keyset pagination. Jika beban tetap tinggi karena volume data historis, pertimbangkan archiving atau partitioning ringan. Pendekatan ini biasanya lebih tahan lama daripada sekadar menambal satu query untuk kondisi hari ini.

Quick Search

shopping_cart Keranjang