Baca rencana query

Pertanyaan

Bagaimana database benar-benar menjalankan query ini, dan apa buktinya?

Setelah baca ini, kamu bisa membaca output EXPLAIN ANALYZE, membedakan seq scan dari index scan, membandingkan estimasi dengan baris nyata, dan memutuskan perbaikan dari bukti.

Prasyarat dan contoh

Kamu sudah tahu cara kerja B-tree dan indeks komposit (M04.03).

Kamu menambahkan indeks (user_id, created_at) dan yakin query sudah cepat. Besoknya dasbor tetap menunjukkan query itu 900 ms. Kamu nggak salah merancang indeks, tapi belum memeriksa apakah database memakainya. Rencana query adalah satu-satunya tempat kamu melihat keputusan database, bukan keputusanmu.

Mekanisme

EXPLAIN vs EXPLAIN ANALYZE. EXPLAIN menampilkan rencana dan estimasi tanpa menjalankan query. EXPLAIN (ANALYZE, BUFFERS) benar-benar menjalankan query dan menambahkan waktu serta baris nyata. Hati-hati: untuk UPDATE atau DELETE, ANALYZE benar-benar mengubah data. Bungkus dengan transaksi lalu ROLLBACK.

Contoh output yang disederhanakan untuk query R2 tanpa indeks:

Limit  (cost=412000..412001 rows=20)
  ->  Sort  (cost=412000..412030 rows=12000)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (cost=0..410000 rows=12000)
              Filter: (user_id = 4217)
              Rows Removed by Filter: 9988000
Execution Time: 880 ms

Yang terbaca: Seq Scan membaca seluruh tabel, Rows Removed by Filter: 9988000 berarti hampir seluruh kerja terbuang, dan ada Sort karena urutan tidak datang dari indeks.

Setelah indeks (user_id, created_at DESC):

Limit  (cost=0.56..45 rows=20)
  ->  Index Scan using orders_user_created on orders  (rows=20)
        Index Cond: (user_id = 4217)
Execution Time: 0.4 ms

Tanpa Sort, tanpa filter yang membuang baris. Waktu turun dari 880 ms ke 0,4 ms.

Jenis akses yang perlu dikenali.

• Seq scan: baca seluruh tabel. Bagus untuk tabel kecil atau query yang membutuhkan sebagian besar baris. Bukan otomatis salah.
• Index scan: pakai indeks lalu ambil baris dari tabel.
• Index-only scan: semua dijawab dari indeks (covering index).
• Bitmap scan: kumpulkan lokasi dari indeks, lalu baca tabel berurutan. Biasa untuk hasil menengah.

Join. Nested loop bagus saat satu sisi kecil dan sisi lain terindeks. Hash join membangun tabel hash dari sisi kecil, bagus untuk set besar tanpa indeks. Merge join butuh dua sisi terurut. Perencana memilih berdasarkan estimasi ukuran, jadi estimasi yang salah menghasilkan jenis join yang salah.

Estimasi vs nyata. Bandingkan rows= yang diperkirakan dengan actual rows=. Selisih 10x atau lebih adalah tanda bahaya: statistik usang (jalankan ANALYZE), kolom saling berkorelasi yang nggak diketahui perencana, atau distribusi miring. Rencana yang dipilih dari estimasi buruk bisa buruk meski semua indeks ada.

Bukti nyata. Rencana di data uji 1.000 baris bisa beda total dengan produksi 10 juta baris: untuk tabel kecil, seq scan memang lebih murah dan perencana benar memilihnya. Ukur di data berukuran dan distribusi mirip produksi. Dan satu eksekusi cepat belum membuktikan banyak: cache hangat bisa menyembunyikan I/O. Lihat angka Buffers: shared read untuk membaca dari disk, bukan hanya waktu.

Saat rancangan ini gagal

• Mengoptimasi dari tebakan. Menambah indeks tanpa melihat rencana. Query mungkin lambat karena sort atau join, bukan filter.
• Percaya EXPLAIN tanpa ANALYZE. Estimasi cost bukan waktu, dan estimasi baris bisa meleset jauh.
• Menguji di data kecil. Rencana "bagus" di 1.000 baris berubah jadi seq scan di 10 juta, atau sebaliknya.
• Mengabaikan variasi parameter. Rencana untuk user dengan 3 order beda dari user dengan 3 juta order (akun besar). Satu rencana yang dicache bisa buruk untuk salah satunya.
• Menyamakan cepat dengan murah. Query 5 ms yang jalan 5.000 kali per detik memakai lebih banyak CPU daripada query 500 ms yang jalan sekali per menit.

Contoh tandingan: query yang melakukan seq scan memang bisa jadi pilihan terbaik. Laporan yang membaca 60% tabel lebih murah dengan seq scan berurutan daripada lompat acak lewat indeks.

Latihan

Output ini dari query SELECT * FROM orders WHERE lower(email) = 'a@b.id' di tabel 10 juta baris yang punya indeks pada email:

Seq Scan on orders  (cost=0..520000 rows=50000)
  Filter: (lower(email) = 'a@b.id')
  Rows Removed by Filter: 9999999
  actual rows=1  time=1400 ms

Apa masalahnya, dan apa dua perbaikan yang mungkin?

Lihat pembahasan+

Tanda masalahnya: seq scan di tabel 10 juta baris, hampir semua baris dibuang (Rows Removed by Filter: 9999999), hanya 1 baris yang cocok. Perencana memperkirakan 50.000 baris tapi nyatanya 1, jadi estimasi juga meleset 50.000 kali.

Penyebabnya: filter memanggil lower(email). Indeks biasa pada email mengurutkan nilai asli, bukan nilai setelah lower, jadi tidak bisa dipakai.

Dua perbaikan:

1. Buat indeks ekspresi: CREATE INDEX ON orders (lower(email)). Query tetap sama dan sekarang bisa memakai indeks. Harga: satu indeks lagi pada jalur tulis.
2. Simpan email yang sudah dinormalisasi (huruf kecil) saat menulis, lalu query dengan email = 'a@b.id'. Indeks yang ada langsung terpakai, tapi semua jalur tulis harus menjamin normalisasi, idealnya dengan constraint.

Setelah perbaikan, verifikasi dengan EXPLAIN (ANALYZE, BUFFERS) bahwa rencana berubah jadi index scan dan waktu turun dari 1.400 ms ke hitungan milidetik. Tanpa verifikasi itu, perbaikannya baru dugaan.

Terkait

• M01.06 Kenali batas infrastruktur menekankan bukti terukur sebelum menambah komponen, prinsip yang sama dengan membaca rencana.
• M04.03 menjelaskan indeks yang rencananya dibaca di sini.
• M05 Transaksi dan konkurensi bahas lock yang bisa membuat query lambat tanpa terlihat di rencana.
• Lab L04 melatih membaca rencana dan memilih indeks.

Catatan

• Asumsi: format output gaya PostgreSQL, disederhanakan. MySQL, SQL Server, dan lainnya punya istilah berbeda tapi gagasan yang sama.
• Yang nggak dibahas: hint optimizer, plan cache, parallel query, dan tuning konfigurasi.
• Angka waktu pada contoh ilustratif.
• Terakhir ditinjau: 26 Oktober 2026.