Query Slow? Cara Membaca EXPLAIN ANALYZE di PostgreSQL Tanpa Tebak-Tehe

Minggu lali seorang teammate mengirim saya output EXPLAIN ANALYZE sepanjang 40 baris dan bertanya, "Tomi, ini kenapa slow?" Dia sudah punya tool-nya, sudah punya datanya, tapi tersesat di teksnya. Ini persis kesalahan yang dulu juga saya alami: menjalankan EXPLAIN ANALYZE, lihat angka-angkanya, lalu langsung menambah index secara membabi buta karena kata Seq Scan terdengar jahat.

Setelah beberapa tahun debugging query di produksi SaaS multi-tenant, saya punya urutan baca yang konsisten. Artikel ini adalah proses itu — bukan daftar semua keyword, tapi cara saya menemukan bottleneck dalam 2-3 menit pertama.

EXPLAIN vs EXPLAIN ANALYZE: Bedanya Fundamental

EXPLAIN hanya menunjukkan rencana yang dipilih planner. EXPLAIN ANALYZE benar-benar mengeksekusi query dan menambahkan angka aktual di samping estimasi. Ini beda besar: banyak masalah performa justru terlihat dari selisih estimasi vs realita.

Peringatan yang sering dilupakan orang: EXPLAIN ANALYZE untuk UPDATE/DELETE benar-benar menjalankan modifikasi datanya. Kalau mau aman, bungkus dalam transaksi lalu rollback:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'draft';
ROLLBACK;

Saya pernah lihat orang "cuma nge-test" EXPLAIN ANALYZE DELETE di staging dan kehilangan data hari itu. Jangan jadi orang itu.

Baca Dari Dalam ke Luar, Bukan Dari Atas

Rencana dijalankan dari node paling dalam (paling menjorok ke kanan) ke luar. Setiap node menampilkan:

• cost — estimasi planner, dalam unit arbitrer: startup cost..total cost
• rows — estimasi jumlah baris
• actual time — startup..total dalam milidetik, hasil eksekusi nyata
• loops — berapa kali node ini dieksekusi

Contoh nyata dari query dashboard yang pernah saya debug:

Seq Scan on events  (cost=0.00..48211.00 rows=12 width=64)
  (actual time=0.041..1893.220 rows=84213 loops=1)
  Filter: ((tenant_id = 42) AND (created_at >= '2024-01-01'))
  Rows Removed by Filter: 1820000

Di sini masalahnya langsung terlihat: planner estimasi 12 baris, kenyataannya 84.213 baris. Estimasi yang meleset jauh seperti ini biasanya berarti statistik ANALYZE sudah basi — jalankan ANALYZE events; dan bandingkan lagi.

Trik: pakai BUFFERS

Saya hampir selalu menambahkan BUFFERS:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Outputnya menunjukkan shared hit vs read. Kalau read (baca dari disk) dominan, masalahnya I/O atau shared buffers, bukan query shape. Di server dengan RAM cukup, query yang lambat tapi hit dominan biasanya menunjuk ke CPU work (sorting, fungsi berat).

Pola-Pola yang Paling Sering Saya Temui

1. Seq Scan padahal ada index

Seq Scan sendiri bukan dosa — untuk tabel kecil atau query yang mengambil sebagian besar baris, sequential scan malah lebih cepat. Tapi kalau query Anda memfilter ke segelintir baris dari tabel besar dan planner tetap pilih Seq Scan, cek dua hal: apakah tipe data kolom di WHERE cocok dengan definisi kolom (casting implisit membunuh index), dan apakah statistik masih akurat.

2. Rows Removed by Filter yang raksasa

Angka Rows Removed by Filter: 1820000 di contoh di atas artinya database memindai 1,8 juta baris untuk membuangnya. Itu index yang hilang, hampir selalu.

3. Nested Loop dengan loops besar

Kalau Anda lihat loops=50000 dan actual time kecil per loop, total waktu sebenarnya adalah perkalian. Node dalam actual time=0.01..0.02 loops=50000 berarti ~1 detik total. Ini pola klasik join yang butuh index di sisi dalamnya.

4. Estimasi rows=1 padahal actual puluhan ribu

Ini biasanya kombinasi kolom berkorelasi (misal tenant_id + created_at di aplikasi multi-tenant, di mana setiap tenant aktif di rentang waktu berbeda). Statistik per-kolom tidak menangkap korelasi ini. Solusi pragmatis saya: extended statistics:

CREATE STATISTICS stats_tenant_created (dependencies)
  ON tenant_id, created_at FROM events;
ANALYZE events;

Ini trik yang menyelamatkan salah satu dashboard tenant terbesar kami dari query 8 detik ke 400ms.

Tools yang Membantu (Karena 40 Baris Teks Memang Menyiksa)

Untuk plan yang panjang, saya paste outputnya ke explain.depesz.com — tool lama tapi masih yang terbaik untuk memvisualkan node mana yang paling makan waktu dengan highlight warna. Ini juga sumber yang saya rekomendasikan ke teammate karena dokumentasi visualnya jauh lebih cepat dicerna daripada teks mentah. Thread diskusi yang membahas tool ini di Hacker News juga menarik untuk konteks tambahan.

Kalau Anda lebih suka bacaan sistematis, artikel Cybertec tentang interpretasi output EXPLAIN PostgreSQL adalah referensi yang saya bookmark bertahun-tahun.

Urutan Kerja yang Saya Pakai

Kalau diringkas jadi checklist, ini urutan yang saya jalankan setiap kali query lambat:

1. EXPLAIN (ANALYZE, BUFFERS) — lihat node dengan actual total time terbesar (kalikan dengan loops).
2. Bandingkan rows estimasi vs actual. Meleset >10x? Jalankan ANALYZE dulu sebelum apa pun.
3. Cek Rows Removed by Filter besar → kandidat index baru.
4. Cek read vs hit di BUFFERS → masalah I/O vs CPU.
5. Baru setelah itu pertimbangkan index, extended statistics, atau rewrite query.

Satu catatan jujur: EXPLAIN ANALYZE di staging dengan data sedikit sering menunjukkan plan yang berbeda total dengan produksi. Plan dipilih berdasarkan statistik, dan statistik bergantung volume data. Kalau bisa, jalankan di produksi (read-only query, tentu saja, atau lewat replica) sebelum menarik kesimpulan.

Satu hal terakhir yang sering diabaikan: sebelum menyalah-postgres, cek dulu apakah query-nya sendiri yang boros — SELECT * di tabel lebar, N+1 dari ORM, atau filter di kolom hasil fungsi. Saya sudah menulis soal pola integrasi di artikel webhook vs polling, dan prinsipnya sama: tool diagnosis bagus tidak menolong kalau desainnya memang salah dari awal.

Sumber

• How to understand an EXPLAIN ANALYZE — Stack Overflow — penjelasan komunitas tentang cara membaca output node per node
• PostgreSQL EXPLAIN Output Explained — Cybertec — referensi sistematis untuk semua jenis node dan artinya
• PostgreSQL's Explain Analyze Made Readable — explain.depesz.com — tool visualisasi execution plan yang saya pakai rutin
• Slow PostgreSQL query in production — Stack Overflow — contoh kasus nyata membaca plan di produksi