Paano mo ginagamit ang EXPLAIN at EXPLAIN ANALYZE?

Question

Accepted Answer

**`EXPLAIN`** menampilake rencana eksekusi kueri PostgreSQL — carane bakal mbukak kueri (scan mana, join, indeks). **`EXPLAIN ANALYZE`** bener-bener mbukak kueri lan menampilake wektu nyata lan jumlah baris. Iki minangka alat utama kanggo ngenali kueri sing alon.

## EXPLAIN — rencana (tanpa mbukak)

```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 kanthi 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` luwih migunani amarga nuduhake performa *nyata* (wektu nyata, jumlah baris nyata) — nunampilake dimane kueri bener-bener ngocok wektu, dudu mung taksiran.

## Maca rencana: babagan-babagan kunci sing perlu digatekake

```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
```

## Tuladha 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!
```

## Apa iki pentinge

`EXPLAIN` lan `EXPLAIN ANALYZE` minangka alat utama, penting kanggo **ngenali lan ngoptimalake kueri sing alon** ing PostgreSQL — performa kueri kritis kanggo aplikasi, lan alat-alat iki minangka carane ngerti *napa* kueri alon lan verifikasi perbaikan, dadi kudu bisa nggunakake kanthi mahir minangka pengetahuan tingkat senior kanggo karya performa basis data.

Nilai fundamental yaiku **katon rencana eksekusi**: tinimbang ngira-ngira napa kueri alon, `EXPLAIN ANALYZE` (sing mbukak kueri lan nuduhake wektu nyata lan jumlah baris) nuduhake persis carane Postgres ngeksekusi lan dimane wektu diganakake.

Babagan paling penting sing kudu dingerteni yaiku **tipe scan** — khususé ngenali **`Seq Scan` (pemindaian tabel lengkap)** ing tabel gedhe sing disaring, sing biasane nuduhake **indeks sing ilang** (masalah performa kueri paling umum lan berdampak), versus **`Index Scan`** sing cepet.

Paham carane maca rencana (tipe scan, bandingake baris taksir versus nyata kanggo ngenali statistik ketinggalan, ngenali node paling larang minangka bottleneck, ngati-ngati sort sing nggumplak disk) minangka katrampilan kunci kanggo optimasi kueri.

Alur diagnostik — mbukak `EXPLAIN ANALYZE`, ngenali bottleneck (asring Seq Scan), terapke perbaikan (biasane nambahe indeks), lan mbukak maneh kanggo verifikasi — minangka pendekatan standar, berbasis pengukuran kanggo optimasi kueri.

Sakarehing kueri alon minangka penyebab utama performa basis data sing buruk, lan `EXPLAIN`/`EXPLAIN ANALYZE` minangka alat *kangge* ngenali (nuduhake indeks ilang, rencana ala, lan bottleneck) lan verifikasi optimasi, kudu bisa nggunakake kanthi mahir minangka pengetahuan penting lan bernilai tinggi kanggo performa PostgreSQL — iki carane sistematis nemokake lan perbaiki kueri alon tinimbang ngira-ngira, katrampilan kritis kanggo basis data sing cepet lan topik umum kanggo karya optimasi basis data.