Bagaimana cara menggunakan EXPLAIN dan EXPLAIN ANALYZE?

Question

Accepted Answer

**`EXPLAIN`** menampilkan **rencana eksekusi kueri** PostgreSQL — bagaimana ia akan menjalankan kueri (scan, join, index mana yang digunakan). **`EXPLAIN ANALYZE`** benar-benar menjalankan kueri dan menampilkan waktu nyata dan jumlah baris. Ini adalah alat utama untuk mendiagnosis kueri yang lambat.

## EXPLAIN — rencana (tanpa menjalankan)

```sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;
-- shows the PLAN: scan type, estimated cost, estimated rows — but doesn't run it
```

## EXPLAIN ANALYZE — rencana dengan data eksekusi NYATA

```sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
-- ACTUALLY runs it → shows REAL time, ACTUAL rows, and where time was spent
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;   -- also shows buffer/cache usage
```

`EXPLAIN ANALYZE` lebih berguna karena menampilkan performa *aktual* (waktu nyata, jumlah baris nyata) — mengungkapkan di mana kueri benar-benar menghabiskan waktu, bukan hanya estimasi.

## Membaca rencana: hal-hal kunci yang dicari

```text
SCAN TYPES (most important):
  Seq Scan      → FULL TABLE SCAN (reads every row) — slow on big tables, often the problem
  Index Scan    → uses an index to find rows — fast
  Index Only Scan → reads only the index (doesn't touch the table) — fastest
  Bitmap Scan   → for combining indexes / many matches

WATCH FOR:
  ✗ Seq Scan on a large table being filtered → likely a MISSING INDEX
  ✗ Estimated rows VERY different from actual rows → outdated statistics (run ANALYZE)
  ✗ Expensive joins, sorts spilling to disk ("external merge")
  ✗ The node with the highest actual time = the bottleneck
```

## Contoh diagnostik

```sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
-- "Seq Scan on orders ... actual time=120ms" → a full scan, slow

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
-- "Index Scan using idx_orders_customer ... actual time=0.05ms" → fixed!
```

## Mengapa ini penting

`EXPLAIN` dan `EXPLAIN ANALYZE` adalah alat utama yang penting untuk **mendiagnosis dan mengoptimalkan kueri lambat** di PostgreSQL — performa kueri sangat penting untuk aplikasi, dan alat ini adalah cara Anda memahami *mengapa* kueri lambat dan memverifikasi perbaikan, jadi menguasainya adalah pengetahuan tingkat senior yang penting untuk pekerjaan performa database.

Nilai fundamental adalah **visibilitas ke rencana eksekusi**: daripada menebak mengapa kueri lambat, `EXPLAIN ANALYZE` (yang menjalankan kueri dan menampilkan waktu nyata dan jumlah baris) mengungkapkan persis bagaimana Postgres menjalankannya dan di mana waktu dihabiskan.

Hal-hal paling penting untuk dikenali adalah **tipe scan** — terutama mengidentifikasi **`Seq Scan` (pemindaian tabel penuh)** pada tabel yang difilter besar, yang biasanya menunjukkan **indeks yang hilang** (masalah performa kueri paling umum dan berdampak), versus **`Index Scan`** yang cepat.

Memahami cara membaca rencana (tipe scan, membandingkan baris estimasi vs aktual untuk mendeteksi statistik ketinggalan zaman, mengidentifikasi node paling mahal sebagai bottleneck, memperhatikan sort yang menumpah ke disk) adalah keterampilan kunci untuk optimasi kueri.

Alur kerja diagnostik — jalankan `EXPLAIN ANALYZE`, identifikasi bottleneck (sering kali Seq Scan), terapkan perbaikan (biasanya menambahkan indeks), dan jalankan ulang untuk memverifikasi — adalah pendekatan standar berbasis pengukuran untuk optimasi kueri.

Karena kueri lambat adalah penyebab utama performa database yang buruk, dan karena `EXPLAIN`/`EXPLAIN ANALYZE` adalah *alat* untuk mendiagnosis mereka (mengungkapkan indeks yang hilang, rencana buruk, dan bottleneck) dan memverifikasi optimasi, menguasai mereka adalah pengetahuan penting dan bernilai tinggi untuk performa PostgreSQL — ini adalah cara Anda secara sistematis menemukan dan memperbaiki kueri lambat daripada menebak, keterampilan penting untuk database yang performan dan topik umum untuk pekerjaan optimasi database.