SQL Query Optimization: Membaca EXPLAIN & Menghindari Query Lambat

Aug 5, 202616 min readAdvanced

SQLOptimasiEXPLAINPerformance

Saat API tiba-tiba lambat di produksi, pertanyaan pertama yang muncul biasanya "query mana yang bermasalah?". Hampir selalu jawabannya bukan soal kecepatan internet atau hardware server, melainkan cara kita menulis query — perintah untuk mengambil atau mengubah data — yang membuat database (tempat data disimpan) bekerja jauh lebih keras daripada yang seharusnya. Artikel ini adalah panduan lapangan untuk menemukan, membaca, dan memperbaiki query lambat: mulai dari cara melihat EXPLAIN (output penjelasan cara database mengeksekusi query), memilih index yang tepat, sampai teknik menulis ulang query di Spring Data JPA. Ini topik lanjutan dari Query, Pagination, Index & SQL Injection dan saudara dari PostgreSQL Indexing Strategies — di sini kita lebih fokus ke alur berpikir optimasi query secara umum, bukan index semata.

Kenali Gejalanya: API Lambat

Gejala paling klasik: satu endpoint tertentu terasa lambat, kadang hanya saat data sudah banyak, dan menjadi parah saat trafik ramai. Jangan langsung menebak — cari bukti dulu. Ada dua cara utama menemukan query yang bermasalah:

Slow query log: hampir semua database punya fitur ini. Di PostgreSQL, set log_min_duration_statement (parameter yang mencatat statement yang lebih lama dari durasi tertentu, misalnya 250ms), dan setiap query yang lewat batas akan tercatat. Di MySQL, aktifkan slow_query_log. Log ini memberi tahu Anda query apa yang lambat, berapa lama, dan berapa sering dieksekusi.
Monitoring & observability: alat seperti Prometheus + Grafana atau APM (Application Performance Monitoring, alat pemantau performa aplikasi) menampilkan durasi tiap endpoint. Jika satu endpoint memburuk bersamaan dengan membesarnya tabel, itu petunjuk kuat bahwa full table scan (pemindaian seluruh baris tabel) adalah biang keladinya.

Aturan pertama optimasi: jangan mengoptimalkan sesuatu yang belum Anda buktikan lambat. Profiling (pengukuran performa) dulu, baru perbaiki. Optimasi tanpa bukti biasanya hanya membuang waktu.

Perjalanan Sebuah Query di Dalam Database

Sebelum membahas EXPLAIN, kita perlu paham apa yang terjadi di balik layar saat Anda mengirim query. Setiap database relasional memproses query dalam tiga tahap besar:

Tahap	Nama	Tugasnya
1	`Parser`	Memeriksa sintaks SQL, menerjemahkan teks menjadi struktur internal yang bisa dipahami mesin
2	`Planner / Optimizer`	Menyusun `execution plan` — rencana langkah-langkah pengambilan data — dan memilih rencana yang dianggap termurah
3	`Executor`	Menjalankan rencana itu, baris demi baris, lalu mengembalikan hasil

Kunci dari semuanya ada di tahap 2. Si planner memutuskan bagaimana data diambil: memindai seluruh tabel, menggunakan index, menggabungkan dua tabel dengan cara A atau cara B, dan seterusnya. Keputusan ini yang menentukan cepat-lambatnya query. Dan di sinilah EXPLAIN berperan — ia membuat keputusan si planner menjadi terlihat.

Membaca EXPLAIN

EXPLAIN adalah perintah di semua database besar (PostgreSQL, MySQL, dan lainnya) yang menampilkan rencana eksekusi tanpa benar-benar menjalankan query. Versi yang lebih berguna adalah EXPLAIN ANALYZE, yang benar-benar mengeksekusi query lalu melaporkan apa yang terjadi sebenarnya. Dokumen resmi PostgreSQL menjelaskan keduanya di using-explain, sedangkan MySQL mendokumentasikan format outputnya di explain-output.

Momen yang tepat memakai EXPLAIN ANALYZE: ketika Anda ingin mengukur — di database pengembangan atau dengan BEGIN; ... ROLLBACK; jika query mengubah data. Di produksi, lebih aman pakai EXPLAIN biasa karena tidak mengeksekusi apa pun.

Cara Membaca Output

Output EXPLAIN berbentuk pohon node. Setiap node (simpul) adalah satu langkah eksekusi. Aturan bacanya:

Baca dari node paling dalam, lalu keluar. Node paling dalam dieksekusi lebih dulu; hasilnya mengalir ke node di atasnya.
Perhatikan actual time, rows, dan loops — angka hasil pengukuran nyata (di EXPLAIN ANALYZE). actual time diukur dalam milidetik per baris sampai langkah selesai; rows adalah perkiraan vs aktual jumlah baris; loops menunjukkan berapa kali langkah diulang.
Bandingkan perkiraan (cost) dengan aktual (actual). Beda jauh = statistik database basi, biasanya karena ANALYZE (perintah memperbarui statistik) belum dijalankan.

Contoh output sederhana:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'hendro@mail.com';

Index Scan using users_email_key on users  (cost=0.28..8.30 rows=1 width=73) (actual time=0.021..0.022 rows=1 loops=1)
  Index Cond: (email = 'hendro@mail.com'::text)
Planning Time: 0.080 ms
Execution Time: 0.100 ms

Interpretasinya: database menemukan email lewat Index Scan (pencarian memakai index), hanya memproses 1 baris, dan selesai dalam 0,1 ms. Itulah plan yang sehat.

Node yang Sering Muncul

Planner punya banyak "peralatan". Berikut yang paling sering Anda temui dan artinya:

Node	Arti	Kesan
`Seq Scan`	Memindai seluruh tabel baris demi baris	Wajar untuk tabel kecil; tanda bahaya pada tabel besar
`Index Scan`	Mencari lewat index, lalu mengambil baris dari tabel	Bagus untuk filter selektif
`Index Only Scan`	Semua kolom yang dibutuhkan sudah ada di index, tabel tak perlu dibaca	Terbaik, hemat I/O
`Bitmap Index Scan` + `Bitmap Heap Scan`	Mencari banyak baris lewat index, lalu mengambilnya sekaligus	Baik untuk filter yang memilih banyak baris
`Nested Loop`	Untuk setiap baris di satu sisi, cari pasangannya di sisi lain	Oke jika sisi luar kecil
`Hash Join`	Membangun tabel hash untuk mencocokkan baris	Bagus untuk menggabungkan dua set data besar
`Sort`	Mengurutkan hasil sebelum dikirim	Bisa lambat jika dataset besar; index bisa menghilangkannya

Tanda bahaya utama: Seq Scan pada tabel besar tanpa index. Artinya database membaca puluhan juta baris hanya untuk menemukan beberapa baris yang Anda butuhkan.

Studi Kasus: Sebelum dan Sesudah Index

Mari lihat praktiknya. Anggap tabel orders punya 200.000 baris dan kita mencari pesanan milik user_id = 5:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 5;

Sebelum ada index:

Seq Scan on orders  (cost=0.00..3546.00 rows=812 width=48) (actual time=0.012..8.421 rows=812 loops=1)
  Filter: (user_id = 5)
  Rows Removed by Filter: 199188
Planning Time: 0.120 ms
Execution Time: 9.100 ms

Lihat baris Rows Removed by Filter: 199188 — database membaca 200.000 baris, membuang 199.188 di antaranya. Hanya 812 baris yang lolos. Efisiensinya sangat buruk.

Sesudah dibuat index:

CREATE INDEX idx_orders_user_id ON orders (user_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 5;

Index Scan using idx_orders_user_id on orders  (cost=0.29..31.11 rows=812 width=48) (actual time=0.022..0.410 rows=812 loops=1)
  Index Cond: (user_id = 5)
Planning Time: 0.080 ms
Execution Time: 0.480 ms

Perbedaannya dramatis: dari 9,1 ms menjadi 0,48 ms, dan yang dibaca hanya 812 baris yang relevan, bukan seluruh tabel. Inilah kenapa index adalah senjata pertama melawan query lambat.

Full Table Scan vs Index

Setelah membaca node Seq Scan, jangan langsung panik. Ada situasi di mana pemindaian penuh wajar dan bahkan lebih cepat daripada pakai index:

Kondisi	Contoh	Rekomendasi
Tabel sangat kecil (ratusan baris)	tabel `status_code`	`Seq Scan` wajar; index tidak memberi keuntungan
Query mengambil mayoritas baris tabel	`SELECT * FROM orders WHERE status = 'ACTIVE'` padahal 80% baris aktif	`Seq Scan` lebih cepat dari index
Query tanpa filter	`SELECT * FROM audit_log`	`Seq Scan` pasti
Query dengan filter selektif (ambil < 5% baris)	`WHERE user_id = 5`	Pakai index

Logikanya sederhana: index bekerja seperti daftar isi buku. Untuk membaca seluruh buku, daftar isi tidak membantu — Anda tetap harus membuka semua halamannya. Index baru berguna ketika Anda hanya butuh sebagian kecil dari seluruh baris.

Database menggunakan index secara otomatis jika ia menilai itu lebih murah. Tugas kita bukan memaksa index, melainkan menyediakan index yang benar lalu membiarkan planner memilih.

Selektivitas & Kardinalitas

Kenapa index di kolom user_id sangat efektif? Karena kolom itu selektif — nilainya beragam sehingga satu nilai hanya mencocokkan sedikit baris. Konsep ini terkait kardinalitas (cardinality): jumlah nilai unik dalam sebuah kolom.

Kardinalitas tinggi (mis. id, email, created_at): hampir setiap baris punya nilai berbeda. Index sangat berguna.
Kardinalitas rendah (mis. boolean, atau kolom status yang hanya punya 2–3 nilai): setiap nilai mencocokkan sebagian besar baris. Index hampir tidak menolong.

Contoh nyata: kolom status yang hanya bernilai 'NEW' atau 'DONE'. Filter WHERE status = 'NEW' bisa mengembalikan setengah tabel — index hanya akan memindai separuh index, lalu tetap mengambil separuh tabel. Total pekerjaannya sama dengan Seq Scan.

Kolom	Jumlah nilai unik	Index berguna?	Alasan
`id`	jutaan	Sangat	Satu baris per nilai
`email`	jutaan	Sangat	Satu baris per nilai
`created_at`	jutaan	Sangat	Nilai hampir selalu unik
`status` (2–3 nilai)	2–3	Hampir tidak	Setiap nilai memilih banyak baris
`is_active` (boolean)	2	Tidak	Membagi tabel jadi dua bagian

Pengecualian penting: jika sebagian besar data berstatus 'DONE' dan hanya segelintir yang 'NEW', index parsial (index yang hanya mencakup subset baris, misalnya WHERE status = 'NEW') bisa sangat berguna — detail lengkapnya ada di PostgreSQL Indexing Strategies.

Membangun Index yang Efektif

Memilih kolom untuk index bukan tebak-tebakan. Ada aturan main yang perlu dipahami.

Composite Index & Urutan Kolom

Composite index (index gabungan) adalah index atas beberapa kolom sekaligus, misalnya (user_id, status). Aturan pentingnya disebut leftmost prefix (prefiks paling kiri): index hanya bisa dipakai jika query menyaring kolom paling kiri. Artinya urutan kolom sangat menentukan.

Bandingkan dua index untuk query WHERE user_id = ? AND status = ?:

Index	Bisa dipakai?	Alasan
`(user_id, status)`	Ya	Filter dimulai dari kolom paling kiri (`user_id`)
`(status, user_id)`	Tidak optimal	Kolom paling kiri adalah `status` yang tidak selektif; filter `user_id` tak bisa memanfaatkan index secara penuh

Aturan praktisnya:

Kolom paling kiri = kolom dengan kondisi kesetaraan (=) yang paling selektif.
Kolom setelahnya = kolom range (>, <, BETWEEN, ORDER BY). Index mendukung range hanya pada kolom di posisi akhir.

Contoh:

CREATE INDEX idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);

Index di atas melayani filter WHERE user_id = 5 AND status = 'PAID' sekaligus ORDER BY created_at DESC dan LIMIT — database berhenti setelah 20 baris alih-alih mengambil dan mengurutkan semuanya.

Kapan Index Tidak Dipakai

Index bukan jawaban untuk semua filter. Ada tiga musuh klasik:

Pola query	Kenapa index gagal	Solusi
`WHERE name LIKE '%x%'`	Wildcard di depan membuat index tak bisa mencari dari awal	Hindari; untuk pencarian teks penuh gunakan `full-text search` (pencarian berbasis teks)
`WHERE YEAR(created_at) = 2026`	Fungsi pada kolom membuat index tidak bisa dipakai (nilai diubah sebelum dibandingkan)	Gunakan ekspresi index `(extract(year from created_at))`, atau tulis ulang jadi range: `created_at >= '2026-01-01' AND created_at < '2027-01-01'`
`WHERE phone = 8123456` padahal `phone` bertipe `VARCHAR`	Type coercion: nilai dibandingkan sebagai tipe berbeda, index diabaikan	Samakan tipe: `WHERE phone = '8123456'`

Dua solusi untuk masalah YEAR(created_at) tersebut:

-- Opsi A: tulis ulang jadi range (paling disarankan)
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- Opsi B: index ekspresi (jika tidak bisa menulis ulang query)
CREATE INDEX idx_orders_created_year ON orders ((extract(year from created_at)));

Index untuk ORDER BY & JOIN

ORDER BY yang tidak didukung index membuat database bekerja keras mengurutkan ratusan ribu baris (Sort node) hanya untuk mengembalikan 20 baris pertama. Begitu pula JOIN — mencocokkan dua tabel tanpa index di kolom penggabungnya berujung pada penggabungan yang lambat.

Buktikan sendiri dengan EXPLAIN:

EXPLAIN ANALYZE
SELECT o.id, o.total
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.email = 'hendro@mail.com'
ORDER BY o.created_at DESC
LIMIT 20;

Jika EXPLAIN menampilkan Sort di atas Seq Scan, itu sinyal ada kolom yang belum di-index. Setelah orders punya index (user_id, created_at DESC), plan berubah: Hash Join atau Nested Loop dengan Index Scan di sisi orders, dan node Sort menghilang.

N+1 dari Sisi Database

N+1 problem sering dibahas dari sisi aplikasi (pemanggilan repository dalam loop), tetapi akarnya ada di pola query. Bandingkan dua cara mengambil pesanan beserta itemnya:

-- Opsi 1: satu query dengan JOIN (efisien)
SELECT o.*, i.*
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.user_id = 5;

-- Opsi 2: satu query untuk orders, lalu satu query per order (boros)
SELECT * FROM orders WHERE user_id = 5;
SELECT * FROM order_items WHERE order_id = ?; -- diulang untuk setiap order

Opsi 2 mengirim 1 + N query (satu untuk header, N untuk item). Jika user punya 100 order, aplikasi mengirim 101 query. Pola ini biasanya muncul ketika kode menulis loop for dan memanggil repository di dalamnya — lihat JPA & Hibernate ORM untuk cerita lengkapnya.

Jumlah query yang dikirim adalah sinyal. Dalam log, jika satu HTTP request memicu puluhan query untuk pekerjaan yang seharusnya satu, Anda sedang menghadapi N+1.

Deep Pagination dan Keyset Pagination

OFFSET adalah cara termudah melakukan pagination: LIMIT 20 OFFSET 400. Sayangnya OFFSET besar itu mahal. Database harus membaca semua baris sebelumnya, membuangnya, baru mengembalikan halaman yang diminta. Makin dalam halamannya, makin lambat — ini disebut deep pagination (pagination dalam).

-- Halaman 20.000: database membaca 400.000 baris, membuang 399.980
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 399980;

Solusinya adalah keyset / cursor pagination — mengingat nilai kolom terakhir yang terlihat, lalu mengambil data setelah nilai itu:

-- Halaman pertama
SELECT * FROM orders WHERE user_id = 5 ORDER BY id LIMIT 20;
-- Halaman berikutnya: id = 20 (id terakhir yang diterima klien)
SELECT * FROM orders WHERE user_id = 5 AND id > 20 ORDER BY id LIMIT 20;

Perbandingan keduanya pada tabel 1 juta baris:

Aspek	`OFFSET` pagination	Keyset pagination
Cara kerja	Lompati `N` baris	Ingat nilai terakhir, cari setelahnya
Kinerja halaman dalam	Semakin lambat (makin banyak baris dibuang)	Tetap konstan
Index yang dibutuhkan	Cukup `(user_id)`	`(user_id, id)` — bisa memanfaatkan index
Fleksibilitas	Bisa lompat ke halaman mana pun	Hanya maju/mundur berurutan
Konsistensi data baru di tengah	Data bisa bergeser antar halaman	Stabil terhadap data baru

Kapan memakai yang mana? Jika halaman jarang melampaui beberapa ribu, OFFSET masih cukup. Jika pengguna men-scroll tanpa batas (mis. feed, notifikasi, log), keyset pagination adalah pilihan yang tepat.

Anti-Pattern Query yang Sering Terlihat

Berbekal EXPLAIN, Anda bisa menilai dengan cepat apakah pola-pola ini ada di codebase:

Anti-pattern	Kenapa buruk	Perbaikan
`SELECT *` tanpa perlu	Mengambil semua kolom termasuk yang tak dipakai; memperbesar transfer dan menghalangi `Index Only Scan`	Sebutkan kolom yang dibutuhkan saja
Query di dalam loop (`SELECT` per baris)	N+1; latensi bertambah linear dengan jumlah data	Gabung jadi satu query, atau `join fetch`
`NOT IN (SELECT ...)`	Jebakan `NULL`: jika subquery mengembalikan `NULL`, hasilnya kosong; performa sering lebih buruk	Pakai `NOT EXISTS`, yang berhenti saat menemukan satu kecocokan
Fungsi pada kolom di `WHERE`	Index tidak terpakai (lihat tabel "Kapan index tidak dipakai")	Tulis ulang jadi range atau pakai index ekspresi
`JOIN` tak perlu	Menggandakan baris dan biaya pencocokan tanpa manfaat	Hapus join; ambil data secara terpisah jika tidak dibutuhkan bersamaan
Memuat data raksasa ke memori	Aplikasi JVM (Java Virtual Machine, mesin virtual tempat Java berjalan) bisa `OutOfMemory`	Batasi dengan `LIMIT` / pagination / streaming

Prinsip sederhana: jangan mengambil data yang tidak akan Anda gunakan. Setiap kolom ekstra dan setiap baris ekstra ada harganya.

Pendekatan Sistematis

Optimasi query adalah proses, bukan sekali tebak. Ulangi langkah-langkah ini secara disiplin:

Temukan query lambat — dari slow query log atau monitoring. Jangan menebak.
Jalankan EXPLAIN ANALYZE — identifikasi node mahal: Seq Scan pada tabel besar, Sort tak perlu, loops tinggi, perkiraan vs aktual yang melenceng jauh.
Cek index — apakah sudah ada index yang cocok dengan filter, ORDER BY, dan JOIN? Ingat aturan leftmost prefix dan kardinalitas.
Tulis ulang query — hapus fungsi pada kolom, ganti NOT IN dengan NOT EXISTS, ubah OFFSET menjadi keyset, ambil kolom secukupnya.
Ukur ulang — jalankan EXPLAIN ANALYZE lagi dan bandingkan dengan angka sebelumnya. Simpan sebelum dan sesudah sebagai catatan.

Langkah 5 sering dilupakan, padahal paling penting. Tanpa pengukuran sebelum dan sesudah, Anda tidak tahu apakah perubahan benar-benar menolong.

Measure first. Optimasi tanpa bukti adalah dugaan. EXPLAIN adalah bukti yang paling mudah dibaca.

Praktik di Spring Data JPA

Semua konsep di atas bisa diterapkan langsung lewat Spring Data JPA, yang dokumentasi resminya ada di reference. Pola-pola berikut membuat aplikasi mengirim query yang efisien sejak awal.

Derived query — biarkan nama method menentukan query, lengkap dengan Pageable untuk pagination yang aman:

public interface OrderRepository extends JpaRepository<Order, Long> {

    Page<Order> findByUserId(Long userId, Pageable pageable);
}

@Query dengan JOIN FETCH — mencegah N+1 dengan mengambil relasi dalam satu query, bukan satu query per relasi:

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.id = :id")
    Optional<Order> findByIdWithItems(@Param("id") Long id);
}

Projection interface — ambil hanya kolom yang dibutuhkan, tanpa memuat entitas penuh. Ini setara dengan SELECT o.id, o.total, o.status — dan membuka peluang Index Only Scan:

public interface OrderSummary {
    Long getId();
    BigDecimal getTotal();
    String getStatus();
}

public interface OrderRepository extends JpaRepository<Order, Long> {

    List<OrderSummary> findByUserId(Long userId);
}

Catatan penting: Pageable di Spring Data JPA menghasilkan LIMIT + OFFSET. Untuk halaman yang sangat dalam, keyset pagination tetap lebih baik — Anda bisa memodelkannya sendiri dengan derived query seperti findByUserIdAndIdGreaterThan(Long userId, Long lastId, Pageable pageable).

Kapan DB Tuning Tidak Cukup

Ada kalanya query sudah seoptimal mungkin — EXPLAIN bersih, index lengkap, rencana eksekusi singkat — tetapi API masih terasa lambat karena datanya benar-benar dibaca berulang kali oleh banyak pengguna. Di titik ini, memperbaiki database tidak lagi menjadi jawaban:

Cache: jika data jarang berubah dan banyak dibaca, simpan hasilnya di memori (Redis atau cache aplikasi) agar request berikutnya tidak menyentuh database sama sekali. Strategi lengkapnya ada di Strategi Caching untuk Backend.
Denormalisasi (penyimpanan data rangkap yang disengaja): untuk laporan atau counter yang sering dibaca, menyimpan nilai agregat yang sudah dihitung lebih murah daripada menghitung ulang setiap request. Ini trade-off yang sadar antara konsistensi dan kecepatan.

Urutan yang disarankan: perbaiki query dulu, baru cache. Cache di atas query yang buruk hanya menyembunyikan masalah sementara.

Ringkasan

Query lambat hampir selalu bisa dilacak dengan EXPLAIN, dan hampir selalu bisa diperbaiki dengan tiga hal: index yang benar, query yang ditulis ulang, dan pengukuran yang jujur. Temukan query lambat lewat slow query log, baca plan dari node terdalam ke luar, perhatikan Seq Scan pada tabel besar sebagai tanda bahaya, dan ingat bahwa index hanya berguna untuk kolom yang selektif dengan urutan yang tepat pada composite index. Hindari fungsi pada kolom, OFFSET dalam, dan SELECT dalam loop. Di Spring Data JPA, biasakan JOIN FETCH, projection, dan pagination yang terukur. Dan yang terpenting: ukur dulu, baru optimasi — karena optimasi tanpa bukti hanyalah dugaan yang berpakaian rapi.

Lanjut membaca

Using EXPLAIN — PostgreSQL — dokumentasi resmi membaca output plan
EXPLAIN Output Format — MySQL — format output di MySQL
PostgreSQL Indexes — panduan resmi index, termasuk composite dan partial index
Spring Data JPA Reference — dokumentasi resmi repository, query method, dan pagination
PostgreSQL Indexing Strategies — pendalaman index: partial, covering, kapan berhenti menambah index
Query, Pagination, Index & SQL Injection — dasar derived query, pagination, dan keamanan query
Strategi Caching untuk Backend — langkah berikutnya ketika optimasi query sudah tidak cukup