Membaca EXPLAIN ANALYZE — Belajar PostgreSQL

Bentuk Perintahnya

EXPLAIN (ANALYZE, BUFFERS)
SELECT p.id, p.total, u.nama
FROM pesanan p
JOIN pengguna u ON u.id = p.pengguna_id
WHERE p.status = 'menunggu'
  AND p.dibuat_pada >= now() - interval '7 days';

Opsi	Fungsi
`ANALYZE`	Benar-benar menjalankan kueri dan melaporkan waktu sesungguhnya
`BUFFERS`	Menampilkan berapa halaman dibaca dari cache vs disk — sangat penting
`VERBOSE`	Menampilkan kolom yang dikeluarkan tiap node
`SETTINGS`	Menampilkan parameter yang tidak bernilai bawaan

🚫 ANALYZE benar-benar menjalankan kueri

Menjalankan EXPLAIN ANALYZE pada DELETE atau UPDATE akan benar-benar mengubah data. Bila hanya ingin melihat rencananya, bungkus dalam transaksi yang dibatalkan:

BEGIN;
EXPLAIN ANALYZE DELETE FROM pesanan WHERE ...;
ROLLBACK;

Membaca Keluarannya

Hash Join  (cost=12.4..1842.3 rows=520 width=48)
           (actual time=0.412..18.221 rows=48213 loops=1)
  Hash Cond: (p.pengguna_id = u.id)
  Buffers: shared hit=1204 read=8891
  ->  Seq Scan on pesanan p  (cost=0..1420.0 rows=520 width=24)
                             (actual time=0.018..9.104 rows=48213 loops=1)
        Filter: (status = 'menunggu')
        Rows Removed by Filter: 951787

💡 Cara membacanya

Baca dari dalam ke luar — node terdalam berjalan lebih dulu.
cost adalah perkiraan dalam satuan sembarang; actual time adalah milidetik sungguhan.
loops=N berarti node dijalankan N kali — waktu yang ditampilkan adalah per-loop, jadi kalikan.
Rows Removed by Filter yang besar berarti banyak pekerjaan terbuang.

Petunjuk Terpenting: Selisih Estimasi

rows=520 (perkiraan)  vs  rows=48213 (kenyataan)
Meleset 93 kali lipat!

⚠️ Selisih besar adalah akar dari sebagian besar rencana yang buruk

Perencana memilih strategi berdasarkan perkiraan jumlah baris. Perkiraan yang meleset jauh membuatnya memilih nested loop padahal seharusnya hash join, atau sebaliknya. Penyebab yang biasa:

Statistik basi — jalankan ANALYZE nama_tabel;
Korelasi antar kolom — perencana menganggap kolom saling bebas.
Distribusi data yang miring — naikkan target statistik kolom tersebut.

CREATE STATISTICS stat_pesanan (dependencies)
ON status, jenis_pembayaran FROM pesanan;
ANALYZE pesanan;

ALTER TABLE pesanan ALTER COLUMN status SET STATISTICS 1000;

Jenis Node dan Artinya

Node	Arti	Perlu dikhawatirkan bila…
Seq Scan	Memindai seluruh tabel	Tabel besar tetapi hanya sedikit baris yang diambil
Index Scan	Menelusuri indeks lalu mengambil baris	Biasanya baik
Index Only Scan	Dijawab dari indeks saja	Terbaik
Bitmap Heap Scan	Kumpulkan lokasi dulu, baru baca terurut	Wajar untuk jumlah baris menengah
Nested Loop	Untuk tiap baris kiri, cari di kanan	Sisi kiri berisi banyak baris
Hash Join	Bangun tabel hash lalu telusuri	Muncul `Batches > 1` — kehabisan work_mem
Sort	Mengurutkan	Muncul `external merge` — tumpah ke disk

🚫 Seq Scan tidak selalu buruk

Bila kueri mengambil lebih dari sekitar 5–10% baris tabel, memindai seluruh tabel secara berurutan justru lebih cepat daripada melompat-lompat lewat indeks. Perencana tahu ini. Memaksa penggunaan indeks dengan SET enable_seqscan = off hanya berguna untuk menguji apakah indeks akan membantu — jangan pernah dipakai di produksi.

Tanda Tumpah ke Disk

Sort Method: external merge  Disk: 24560kB      ← work_mem kurang
Buckets: 1024  Batches: 8  Memory Usage: 4096kB ← hash join tumpah

SET work_mem = '256MB';   -- hanya untuk sesi ini, lalu bandingkan lagi

Menemukan Kueri yang Perlu Diperiksa

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    left(query, 80) AS kueri,
    calls,
    round(total_exec_time::numeric, 1) AS total_ms,
    round(mean_exec_time::numeric, 2)  AS rata_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

📌 Urutkan berdasarkan total, bukan rata-rata

Kueri yang butuh 5 detik tetapi hanya jalan sekali sehari kurang penting daripada kueri 50 milidetik yang dipanggil sejuta kali. total_exec_time menunjukkan di mana waktu server Anda sebenarnya habis — dan itulah yang paling layak dioptimalkan.

-- Mencatat kueri lambat ke log
ALTER SYSTEM SET log_min_duration_statement = '1000ms';
SELECT pg_reload_conf();