💡 ULASAN & REFERENSI • DIPERBARUI: 24 October 2026 • REDAKSI AZWINN

Analisis Execution Plan PostgreSQL dengan EXPLAIN ANALYZE

Analisis execution plan (rencana eksekusi) di PostgreSQL dilakukan menggunakan perintah EXPLAIN atau EXPLAIN ANALYZE untuk melihat bagaimana mesin basis data mengeksekusi sebuah kueri SQL. Perintah ini memetakan langkah-langkah fisik yang diambil oleh perencana kueri (query planner), mulai dari pemindaian tabel (sequential scan vs index scan), metode penggabungan data (nested loop, hash join, merge join), hingga estimasi biaya (cost), waktu eksekusi aktual, dan jumlah baris data yang diproses.

Memahami keluaran dari execution plan merupakan keahlian mutlak bagi pengembang basis data dan pengelola sistem (DBA) untuk mendiagnosis kueri lambat, mendeteksi ketiadaan indeks, dan merestrukturisasi SQL agar berjalan dengan performa optimal pada beban kerja produksi.

Sintaks Dasar dan Parameter Penting

Untuk menjalankan analisis mendalam, Anda tidak hanya cukup menggunakan EXPLAIN (yang hanya menampilkan estimasi perencanaan tanpa benar-benar mengeksekusi kueri), melainkan wajib menggunakan EXPLAIN ANALYZE agar basis data menjalankan kueri secara nyata dan melaporkan durasi waktu serta jumlah baris aktual.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT * FROM pesanan 
WHERE status = 'selesai' AND tanggal > '2026-01-01';

Parameter Tambahan dalam EXPLAIN

Parameter Fungsi Utama
ANALYZE Menjalankan kueri secara fisik dan menampilkan waktu serta baris aktual.
BUFFERS Menampilkan informasi penggunaan blok memori (shared hit, read, written).
VERBOSE Menampilkan informasi tambahan seperti kolom keluaran untuk setiap simpul (node).
COSTS Menampilkan estimasi biaya startup dan total biaya serta jumlah baris (diaktifkan secara default).
FORMAT Mengubah format keluaran menjadi TEXT, XML, JSON, atau YAML untuk pemrosesan otomatis.

Anatomi Pembacaan Node Eksekusi

Keluaran dari EXPLAIN dibaca dari tingkat terdalam (paling menjorok ke kanan atau simpul daun/leaf node) ke tingkat terluar. Setiap simpul merepresentasikan operasi relasional tunggal.

1. Sequential Scan (Seq Scan)

Menandakan bahwa PostgreSQL membaca seluruh tabel dari awal hingga akhir baris demi baris tanpa menggunakan indeks. Operasi ini sangat lambat pada tabel berukuran besar kecuali porsi data yang diambil mencakup sebagian besar isi tabel.

2. Index Scan dan Index Only Scan

Menandakan penggunaan indeks B-tree atau jenis indeks lainnya. Index Only Scan jauh lebih efisien karena mesin basis data mengambil data langsung dari struktur indeks tanpa harus mengakses tabel penyimpanan fisik (heap), dengan syarat halaman tabel yang bersangkutan telah ditandai sebagai terlihat (visibility map).

3. Bitmap Heap Scan & Bitmap Index Scan

Digunakan ketika kueri mengambil sejumlah besar baris tetapi masih memanfaatkan indeks. Bitmap Index Scan mengumpulkan lokasi halaman data ke dalam memori bitmap, kemudian Bitmap Heap Scan mengambil data dari halaman-halaman tersebut secara berurutan untuk meminimalkan pembacaan acak pada diska.

Metode Penggabungan (Join Methods)

Ketika kueri melibatkan tabel ganda (JOIN), perencana kueri memilih salah satu dari tiga strategi utama berdasarkan ukuran data dan keberadaan indeks:

  • Nested Loop: Sangat efisien jika tabel luar berukuran sangat kecil dan tabel dalam memiliki indeks pada kolom kunci penggabungan.
  • Hash Join: Metode tercepat untuk menggabungkan tabel berukuran besar yang tidak memiliki indeks. Mesin membuat tabel hash di memori untuk salah satu tabel, lalu memindai tabel kedua untuk mencari kecocokan.
  • Merge Join: Digunakan ketika data dari kedua tabel sudah terurut (misalnya melalui indeks atau pengurutan eksplisit). Sangat efisien untuk dataset berskala masif.

Langkah-Langkah Teknis Optimasi Berbasis Analisis

Setelah mendapatkan hasil dari EXPLAIN ANALYZE, lakukan langkah-langkah perbaikan berikut secara sistematis:

  1. Identifikasi Simpul Terlambat: Cari baris dengan nilai actual time tertinggi atau perbedaan masif antara rows (estimasi) dan actual rows (aktual).
  2. Perbaiki Statistik Planner: Jika estimasi baris meleset jauh dari kenyataan, jalankan perintah ANALYZE nama_tabel; untuk memperbarui statistik distribusi data.
  3. Tambahkan Indeks yang Relevan: Buat indeks komposit pada kolom yang sering menjadi kriteria WHERE atau JOIN. Pastikan urutan kolom pada indeks dimulai dari yang memiliki kardinalitas tertinggi.
  4. Evaluasi Penggunaan Memori: Jika Hash Join sering menggunakan penyimpanan sementara di diska (disk file), tingkatkan parameter work_mem pada berkas konfigurasi postgresql.conf.

Troubleshooting Masalah Umum

Seringkali perencana kueri memilih Sequential Scan meskipun indeks tersedia. Hal ini umumnya terjadi karena tabel terlalu kecil sehingga pemindaian sekuensial dianggap lebih murah oleh mesin, atau karena kondisi klausa WHERE membungkus kolom dengan fungsi (misalnya WHERE LOWER(email) = ...) yang membuat indeks standar tidak dapat digunakan. Solusinya adalah dengan menerapkan Functional Index atau Expression Index.

🔍 Orang Lain Juga Mencari Ini