Yaya kuke amfani da EXPLAIN da EXPLAIN ANALYZE?

Question

Accepted Answer

**`EXPLAIN`** yana nuna **tsarin aiwatar jimirar PostgreSQL** — yadda zai aiwatar jimirar (waɗin abin sakowa, haɗuwa, fihirisai). **`EXPLAIN ANALYZE`** yana aiwatar jimirar gaske kuma yana nuna lokacin ainihi da ƙididdige jeri. Waɗannan su ne kayan aiki na farko don gano jimirar da saurin gudu.

## EXPLAIN — tsari (ba tare da aiwatarwa ba)

```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 — tsari tare da bayanan aiwatarwa na GASKE

```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` ya fi fa'ida saboda yana nuna *ainihin* aiki (lokacin ainihi, ƙididdige jeri na ainihi) — wanda ke bubutar inda jimirar ta fi kasua gudu, ba riba kalomi ba.

## Karantawa na tsari: mahimman abubuwan da za'a hako

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

## Misalin bincike

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

## Me ya sa haka muhimmi

`EXPLAIN` da `EXPLAIN ANALYZE` su ne kayan aiki na muhimmi na farko don **gano da gyara jimirar da saurin gudu** a cikin PostgreSQL — gurin jimirar muhimmi ne don aikace-aikace, kuma waɗannan kayan aiki su ne yadda kuke fahimta *me ya sa* jimirar ta saurin gudu kuma tabbatar da gyare-gyare, saboda haka jera-jeran su muhimmi ne a gida a matsayin sanin ƙwararru game da aiki na jinsi.

Mahimman ƙima shine **ganin tsarin aiwatarwa**: banda hasashen dalilin saurin gudu na jimirar, `EXPLAIN ANALYZE` (wanda ke aiwatar jimirar kuma yana nuna lokacin ainihi da ƙididdige jeri) yana bubutar daidai yadda Postgres ke aiwatar ta kuma inda lokaci ya kasua.

Mahimman abubuwan da za'a fahimta shine **nau'ikan abin sakowa** — musamman gani da **`Seq Scan` (gaske sakowa teburin)** a kan teburin babbar da zai taimaka, wanda yawanci yana nuna **fihirisa mara sa** (matsala ta aiki na jimirar da fa'ida),   sakamakon ziyarci **`Index Scan`** mai sauri.

fahimta yadda zai karantawa tsari (nau'ukan abin sakowa, kwatanta kalomi da ƙididdige na gaske don gano bayani mara saura, sani don mafi tsada node azaman matsi, kula da abubuwan da ke ruɗe abubuwan) shine sikal muhimmin kuskure don gyara jimirar.

Aikin bincike — tuɓo `EXPLAIN ANALYZE`, sani matsi (yawanci Seq Scan), yi gyara (gabaɗaya saida fihirisa), da sake tuɓo don tabbatar — shine misalin daidai, yadda kuke motsa da auna don gyara jimirar.

Tunda jimirar da saurin gudu su ne gida mafi yawa na rashin aiki na jinsi, kuma tun da `EXPLAIN`/`EXPLAIN ANALYZE` su ne *kayan aiki* don gano su (bubutar fihirisa mara sa, tsari da ba daidai ba, da matsi) kuma tabbatar da gyare-gyare, jera-jeran su muhimmi, sanin ƙwararru ga PostgreSQL aiki — haka kuke gano da gyara jimirar da saurin gudu da iliki, sikal mahimmin kuskure don jinsi masu aiki kuma jaji na gida don aiki na gyara jimirar.