Membaca PostgreSQL EXPLAIN ANALYZE Seperti Pro

Setiap engineer pada akhirnya bertemu query yang baik-baik saja di staging tapi merangkak di produksi. Langkah pertama saya tidak pernah menambah index sembarangan atau menambah RAM ke server. Langkahnya adalah menjalankan EXPLAIN ANALYZE dan benar-benar membaca outputnya. Plan itu memberi tahu persis apa yang dilakukan PostgreSQL, berapa lama tiap langkah, dan ke mana waktunya pergi. Kebanyakan orang hanya melirik total waktu di bagian bawah lalu menebak. Tulisan ini tentang membaca pohon plan dengan benar.

Saya menjalankan PostgreSQL 16 di VPS untuk sebuah layanan yang saya kelola, dan saya sudah menguji PostgreSQL 18 sejak rilis September 2025. Ada satu perubahan yang perlu diketahui di awal: mulai PostgreSQL 18, EXPLAIN ANALYZE menyertakan BUFFERS secara default. Di versi 17 dan sebelumnya Anda masih harus mengetiknya. Flag satu itu adalah pembeda antara menebak dan mengetahui apakah Anda melihat plan lambat atau disk lambat.

Selalu jalankan dengan BUFFERS

EXPLAIN saja menampilkan estimasi planner. EXPLAIN ANALYZE benar-benar mengeksekusi query dan melaporkan waktu serta jumlah baris nyata. Tambahkan BUFFERS dan Anda juga mendapat shared block hits (data ditemukan di cache) versus reads (data diambil dari disk). Inilah angka yang memisahkan plan yang benar-benar mahal dari yang lambat hanya karena datanya dingin. Berikut perintah yang selalu saya pakai.

-- Works on all supported versions; on PG18 BUFFERS is already implied.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '30 days'
ORDER BY o.total DESC
LIMIT 50;

Baca pohonnya dari dalam ke luar

Sebuah plan adalah pohon node, dan ia tidak dieksekusi dari atas ke bawah. Node yang paling menjorok berjalan lebih dulu, mengalirkan barisnya ke induknya, dan seterusnya sampai ke root di paling atas. Jadi baca indentasinya seperti stack: temukan operasi terdalam, pahami apa mengalir ke apa, lalu ikuti baris ke atas. Setiap baris node membawa estimasi di tanda kurung pertama dan kenyataan di tanda kurung kedua.

Limit  (cost=812.44..812.56 rows=50 width=48) (actual time=6.112..6.128 rows=50 loops=1)
  ->  Sort  (cost=812.44..842.19 rows=11900 width=48) (actual time=6.110..6.118 rows=50 loops=1)
        Sort Key: o.total DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  Hash Join  (cost=38.00..416.62 rows=11900 width=48) (actual time=0.401..4.980 rows=11842 loops=1)
              Hash Cond: (o.customer_id = c.id)
              ->  Seq Scan on orders o  (cost=0.00..347.00 rows=11900 width=20) (actual time=0.012..2.140 rows=11842 loops=1)
                    Filter: (created_at >= (now() - '30 days'::interval))
                    Rows Removed by Filter: 8158
              ->  Hash  (cost=23.00..23.00 rows=1200 width=36) (actual time=0.377..0.378 rows=1200 loops=1)
                    ->  Seq Scan on customers c  (cost=0.00..23.00 rows=1200 width=36) (actual time=0.006..0.180 rows=1200 loops=1)
Planning Time: 0.210 ms
Execution Time: 6.180 ms

Membaca plan itu dari dalam ke luar: PostgreSQL memindai customers secara sekuensial ke dalam hash table, memindai orders secara sekuensial dengan menerapkan filter tanggal, menggabungkannya lewat hashing, mengurutkan hasil untuk mengambil 50 teratas berdasarkan total, dan mengembalikannya. Nilai cost adalah unit planner yang arbitrer, hanya berguna untuk membandingkan node dalam plan yang sama. Nilai actual time dalam milidetik dan bersifat kumulatif, jadi waktu induk sudah mencakup anak-anaknya.

Seq scan vs index scan

Seq Scan membaca setiap baris di tabel. Itu tidak otomatis buruk. Jika query membutuhkan sebagian besar tabel, pembacaan sekuensial sering lebih cepat daripada melompat-lompat di index. Tanda bahayanya adalah Seq Scan dengan Rows Removed by Filter yang besar, seperti 8158 di atas. Artinya PostgreSQL membaca banyak baris hanya untuk membuangnya, yang justru bisa dicegah oleh index. Index Scan menelusuri B-tree ke entri yang cocok dan hanya mengambil baris-baris itu.

• Seq Scan yang membaca tabel kecil (beberapa ribu baris) dengan sedikit baris terbuang: biarkan saja, index tidak akan membantu.
• Seq Scan dengan Rows Removed by Filter jauh lebih besar dari baris yang dikembalikan: kandidat kuat untuk index pada kolom yang difilter.
• Index Scan yang tetap mengembalikan sebagian besar tabel: index menambah I/O acak tanpa manfaat; planner mungkin benar memilih seq scan.
• Bitmap Heap Scan: jalan tengah PostgreSQL saat index cocok dengan banyak baris tapi bukan mayoritas.

Nested loop vs hash join

PostgreSQL memilih strategi join berdasarkan berapa baris yang diperkirakan dari tiap sisi. Nested Loop mengambil tiap baris dari sisi luar dan menyelidiki sisi dalam, yang bagus sekali saat sisi luar sangat kecil dan sisi dalam punya index, dan bencana saat kedua sisi besar. Hash Join membangun hash table dari satu sisi dan menyelidikinya dengan sisi lain, yang tepat untuk join besar yang tidak terurut. Tabel di bawah adalah model mental yang saya pegang.

Kasus paling buruk yang terus saya temui adalah Nested Loop yang dipilih planner karena mengira ada dua baris dari sisi luar padahal ada dua puluh ribu. Lihat penghitung loops pada node dalam: jika tertulis loops=20000, node itu berjalan dua puluh ribu kali. Itu hampir selalu masalah statistik usang, dan itulah kenapa selisih estimasi-versus-aktual lebih penting daripada angka tunggal mana pun.

Menemukan bottleneck sebenarnya

Jangan terpaku pada Execution Time di bawah. Telusuri pohonnya dan temukan satu node dengan self time terbesar, yaitu actual time-nya dikurangi waktu anak-anaknya. Lalu perhatikan tiga hal pada node itu: rasio estimasi terhadap baris aktual, Rows Removed by Filter, dan jumlah buffer. Node dengan jutaan shared read block sedang mengakses disk; node dengan jutaan shared hit block terikat CPU di cache. Dua masalah itu punya solusi yang sangat berbeda.

1. Temukan node dengan self time tertinggi, bukan waktu kumulatif tertinggi.
2. Periksa estimasi vs baris aktual pada node itu; selisih lebih dari sekitar sepuluh kali berarti statistik buruk yang menghasilkan plan buruk.
3. Periksa Rows Removed by Filter; jika jauh lebih besar dari baris yang dikembalikan, Anda kekurangan index.
4. Baca buffers-nya: banyak reads berarti terikat disk, banyak hits berarti terikat CPU, dan Batches lebih dari satu berarti hash tumpah ke disk.
5. Perbaiki satu node itu, jalankan ulang EXPLAIN ANALYZE, dan pastikan plan berubah sebelum menyentuh apa pun yang lain.

Semua ini tidak menuntut menghafal setiap tipe node. Jalankan EXPLAIN ANALYZE dengan BUFFERS, baca pohonnya dari dalam ke luar, bandingkan yang diperkirakan planner dengan yang terjadi, dan ikuti self time ke satu node yang benar-benar menyakitkan. Sembilan puluh persen query lambat yang saya perbaiki berujung pada index yang hilang atau statistik usang, dan plan selalu memberi tahu saya persis yang mana.