PostgreSQL EXPLAIN ANALYZE'ni o'qish: reja daraxti, cost, rows va Buffers

Qisqacha: EXPLAIN — so'rov qanday bajarilishini ko'rsatadi (bajarmaydi). EXPLAIN (ANALYZE, BUFFERS) — so'rovni haqiqatan bajaradi va har qadamda qancha vaqt, nechta qator, nechta sahifa ketganini yozadi. Reja — daraxt: ichkaridan tashqariga, pastdan yuqoriga o'qiladi. Eng muhim uch narsa: qayerda vaqt ketdi (actual time × loops), taxmin haqiqatga mosmi (rows), nechta sahifa o'qildi (Buffers). Kanon misolida work_mem yetmagan saralash uch jarayonda ~30 MB dan diskka yozdi va 3.9 s ishladi.

Bu darsda

• EXPLAIN, ANALYZE va BUFFERS farqini bilasiz; UPDATE'ni xavfsiz tahlil qilasiz.
• Reja daraxtini to'g'ri tartibda o'qiysiz va tugun qatoridagi har raqamni tushuntirasiz.
• Seq, Index, Index Only va Bitmap scan'larni bir-biridan ajratasiz.
• Nested Loop, Hash Join va Merge Join qachon tanlanishini aytasiz.
• Diskka tushgan saralashni (external merge) va noto'g'ri taxminni rejada topasiz.

Oldin bilishingiz kerak: Indeks turlari: GIN, GiST, BRIN va pg_trgm, Qaysi ustunga va qanday tartibda indeks, JOIN'lar, SQL tili va buyruq turlari.

1. Nega bu kerak?

Oldingi to'rt darsda biz rejalarni qisman o'qidik: "mana Index Scan", "mana Sort yo'qoldi". Bu darsda rejani oxirigacha o'qishni o'rganamiz.

Nega bu shunchalik muhim? SQL — deklarativ til (SQL tili darsida ko'rgandik): siz "nima kerak" deysiz, "qanday topish" ni baza o'zi hal qiladi. Bazaning ichidagi bu "qaror qiluvchi" — planner (rejalashtiruvchi). U bitta so'rov uchun o'nlab yo'lni ko'rib chiqadi va eng arzonini tanlaydi. Uning qarori — reja (plan).

So'rov sekin bo'lsa, taxmin qilish befoyda. "Indeks yo'qdir", "JOIN sekindir" — 50/50. Reja esa aniq aytadi: vaqt qaysi qadamda ketdi va nega.

O'xshatish: taksi navigatori. Siz manzilni aytasiz, u yo'lni o'zi tanlaydi. Yetib kelish 40 daqiqa olsa, sababini bilish uchun marshrut tarixini ochasiz: "Chilonzorda 25 daqiqa tiqilinchda turibmiz". EXPLAIN ANALYZE — so'rovning marshrut tarixi.

Darsdagi rejalar kanon bazasidan (PostgreSQL 18.6, Docker, 2 mln vazifa, 20 000 foydalanuvchi; sxemaning 012-fayldagi holati, 60-explain.txt). "Node qadami" dagi rejalar esa API ulangandan keyin qayta qurilgan bazadan (reja-64.txt) — alohida o'lchov. O'zimiz ishga tushirgan misollar alohida belgilangan (PostgreSQL 17.6).

2. Uch rejim: EXPLAIN, ANALYZE, BUFFERS

2.1 Faqat reja

EXPLAIN SELECT * FROM vazifalar WHERE egasi_id = 77;

Natija:

                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Bitmap Heap Scan on vazifalar  (cost=5.87..713.90 rows=186 width=104)
   Recheck Cond: (egasi_id = 77)
   ->  Bitmap Index Scan on vazifalar_egasi_id_idx  (cost=0.00..5.82 rows=186 width=0)
         Index Cond: (egasi_id = 77)
(4 rows)

So'rov bajarilmadi. Bu — plannerning niyati va taxmini: "186 qator chiqadi, narxi 713.90".

2.2 Reja + haqiqat

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM vazifalar WHERE egasi_id = 77;

Natija:

                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on vazifalar  (cost=5.87..713.90 rows=186 width=104) (actual time=0.404..4.003 rows=416.00 loops=1)
   Recheck Cond: (egasi_id = 77)
   Heap Blocks: exact=415
   Buffers: shared hit=3 read=418
   I/O Timings: shared read=0.710
   ->  Bitmap Index Scan on vazifalar_egasi_id_idx  (cost=0.00..5.82 rows=186 width=0) (actual time=0.121..0.121 rows=416.00 loops=1)
         Index Cond: (egasi_id = 77)
         Index Searches: 1
         Buffers: shared hit=3 read=3
         I/O Timings: shared read=0.025
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.066 ms
 Execution Time: 4.086 ms
(14 rows)

Endi har tugunda ikkinchi qavs bor: (actual time=… rows=… loops=…). Bu — haqiqatda nima bo'lgani. Taxmin 186 edi, haqiqatda 416 qator chiqdi. Bu farq — Statistika va planner darsining mavzusi.

BUFFERS — har tugunda o'qilgan sahifalar:

I/O Timings — o'qishga ketgan vaqt. U faqat track_io_timing = on bo'lsa chiqadi (kanon konteyneri shunday sozlangan). PostgreSQL 18 da EXPLAIN ANALYZE BUFFERS ni sukut bo'yicha qo'shadi (oddiy EXPLAIN da — yo'q, u so'rovni bajarmaydi); 17 va undan oldin uni alohida yozish kerak (PostgreSQL 18.0 release notes).

2.3 ANALYZE so'rovni BAJARADI

EXPLAIN ANALYZE bilan UPDATE yoki DELETE ni tahlil qilsangiz — ular haqiqatan bajariladi. Kanon buni tranzaksiya ichida qildi:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE vazifalar SET ustuvorlik = 'yuqori' WHERE egasi_id = 77;
ROLLBACK;

Natija:

BEGIN
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Update on vazifalar  (cost=5.87..713.90 rows=0 width=0) (actual time=19.503..19.504 rows=0.00 loops=1)
   Buffers: shared hit=4149 read=262 dirtied=583
   I/O Timings: shared read=2.257
   ->  Bitmap Heap Scan on vazifalar  (cost=5.87..713.90 rows=186 width=10) (actual time=0.165..0.603 rows=416.00 loops=1)
         Recheck Cond: (egasi_id = 77)
         Heap Blocks: exact=415
         Buffers: shared hit=418
         ->  Bitmap Index Scan on vazifalar_egasi_id_idx  (cost=0.00..5.82 rows=186 width=0) (actual time=0.038..0.038 rows=416.00 loops=1)
               Index Cond: (egasi_id = 77)
               Index Searches: 1
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.102 ms
 Trigger vazifa_tarixi_trg: time=14.161 calls=416
 Trigger vazifalar_yangilangan_trg: time=1.524 calls=416
 Execution Time: 33.784 ms
(17 rows)

ROLLBACK

416 qator yangilandi (dirtied=583 — sahifalar o'zgardi), keyin ROLLBACK hammasini qaytardi. Va qiziq topilma: rejaning oxirida trigger'lar vaqti. Jami 33.8 ms dan 14.2 ms — tarix trigger'i (tarix jadvallari darsida yozilgan). Qatorlarni topish atigi 0.6 ms. Sekin UPDATE'ning sababi ba'zan so'rovda emas, trigger'da.

Diqqat: EXPLAIN ANALYZE DELETE … ni tranzaksiyasiz yozmang. Sukut autocommit rejimida o'chirish darhol saqlanadi — "faqat ko'rmoqchi edim" degan uzr ma'lumotni qaytarmaydi.

3. Reja daraxti

3.1 O'qish tartibi

Reja — daraxt. Har -> — bitta tugun (node), ya'ni bitta amal. Chekinishi kattaroq tugun — ichkariroq. Ichki tugun natijasini tashqisiga uzatadi.

Bajarilish tartibi: ichkaridan tashqariga. Eng chuqur (eng o'ngga surilgan) tugunlar birinchi ishlaydi, ularning qatorlari yuqoriga "oqib" chiqadi. Yuqoridagi birinchi qator — oxirgi qadam, so'rovning natijasi.

Kanondagi "eng ko'p vazifali 3 foydalanuvchi" so'rovining rejasi (keyingi bo'limlarda to'liq ko'ramiz) daraxt shaklida:

flowchart BT
  A["Seq Scan<br/>foydalanuvchilar"] --> B["Hash<br/>20 000 qator xotirada"]
  C["Parallel Index Only Scan<br/>vazifalar_egasi_id_idx"] --> D["Hash Join"]
  B --> D
  D --> E["Partial HashAggregate<br/>har ishchida"]
  E --> F["Gather"]
  F --> G["Finalize HashAggregate"]
  G --> H["Sort: top-N"]
  H --> I["Limit 3"]

Diagramma pastdan yuqoriga o'qiladi — aynan reja bajarilishi tartibida. Ikki jadval o'qiladi, birlashtiriladi, guruhlanadi, saralanadi va birinchi 3 tasi olinadi.

3.2 Bitta tugun qatori

Bitmap Heap Scan on vazifalar  (cost=5.87..713.90 rows=186 width=104) (actual time=0.404..4.003 rows=416.00 loops=1)

Uch muhim nuqta:

1. Cost birligi millisekund emas. U shartli: "bitta sahifani ketma-ket o'qish = 1". Cost faqat bitta so'rov ichidagi variantlarni solishtirish uchun. Ikki xil so'rovning cost'ini taqqoslash ma'nosiz.
2. Ikki raqam: boshlash va tugatish. Sort da birinchi raqam katta bo'ladi — saralash tugamaguncha bitta ham qator chiqmaydi. Seq Scan da birinchi raqam 0 ga yaqin — birinchi qator darhol chiqadi.
3. actual time va rows — BITTA loop uchun. loops=5 bo'lsa, umumiy vaqt va qatorlarni 5 ga ko'paytiring. PostgreSQL 18 rows ni kasr bilan yozadi (416.00, 266986.33) — bu o'rtacha; 17 da butun songa yaxlitlanardi.

Uchinchi nuqta eng ko'p adashtiradi. Keling, bitta qatorni qayta ishlaydigan kichik funksiya yozamiz:

function tugunniOqi(qator) {
  const m = qator.match(
    /actual time=([\d.]+)\.\.([\d.]+) rows=([\d.]+) loops=(\d+)/,
  );
  if (!m) return null;
  const [, , oxiri, qatorlar, loops] = m.map(Number);
  return {
    birLoopMs: oxiri,
    jamiMs: +(oxiri * loops).toFixed(3),
    jamiQator: Math.round(qatorlar * loops),
  };
}

const ichki =
  "Materialize (actual time=0.010..0.010 rows=1.00 loops=5)";
const parallel =
  "Parallel Seq Scan on vazifalar (actual time=0.228..79.605 " +
  "rows=266986.33 loops=3)";

console.log(tugunniOqi(ichki));
console.log(tugunniOqi(parallel));
console.log(tugunniOqi("Seq Scan on t (cost=0.00..2.60 rows=13)"));

Konsolda:

{ birLoopMs: 0.01, jamiMs: 0.05, jamiQator: 5 }
{ birLoopMs: 79.605, jamiMs: 238.815, jamiQator: 800959 }
null

Birinchisi to'g'ri: tugun 5 marta, har safar 0.01 ms — jami 0.05 ms. Ikkinchisida ehtiyot bo'ling. Parallel tugunda loops=3 — bu ketma-ket uch marta emas, bir vaqtda ishlagan uch jarayon (asosiy + 2 ishchi). Qatorlarni ko'paytirish to'g'ri (800 959 — bajarilmagan vazifalar), lekin vaqtni ko'paytirish noto'g'ri: devor soati bo'yicha ~80 ms ketgan, 239 ms emas. Uchinchisi — ANALYZE siz reja qatori, unda actual yo'q.

Tekshirib ko'ring: Tugun: Index Scan … (actual time=0.020..0.035 rows=2.00 loops=1000). Bu tugun jami qancha vaqt va qancha qator berdi?

4. Scan turlari: jadvaldan qanday o'qiladi

To'rt asosiy tur. Har biri kanon rejasidan.

4.1 Seq Scan va Parallel Seq Scan

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM vazifalar WHERE NOT bajarildi;

Natija (qisqartirildi):

 Finalize Aggregate  (cost=49664.67..49664.68 rows=1 width=8) (actual time=103.488..105.682 rows=1.00 loops=1)
   ->  Gather  (cost=49664.46..49664.67 rows=2 width=8) (actual time=103.336..105.674 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=48664.46..48664.47 rows=1 width=8) (actual time=97.286..97.288 rows=1.00 loops=3)
               ->  Parallel Seq Scan on vazifalar  (cost=0.00..47826.33 rows=335250 width=0) (actual time=0.228..79.605 rows=266986.33 loops=3)
                     Filter: (NOT bajarildi)
                     Rows Removed by Filter: 399680
                     Buffers: shared hit=417 read=39076
 Execution Time: 105.728 ms

Seq Scan — jadvalni boshidan oxirigacha o'qish. 40 % qator kerak bo'lsa — bu eng arzon yo'l. Parallel — uch jarayon jadvalni bo'lib o'qidi; Gather ularning natijasini yig'di; Partial va Finalize Aggregate — har jarayon o'z qismini sanadi, keyin jami qo'shildi. 39 493 sahifa — butun jadval.

4.2 Index Scan

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM vazifalar WHERE id = 5;

Natija:

                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using vazifalar_id_egasi_id_key on vazifalar  (cost=0.43..8.45 rows=1 width=104) (actual time=6.064..6.066 rows=1.00 loops=1)
   Index Cond: (id = 5)
   Index Searches: 1
   Buffers: shared read=4
   I/O Timings: shared read=6.038
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.060 ms
 Execution Time: 6.081 ms
(9 rows)

Index Scan — indeksdan manzilni topib, jadval sahifasini ochish. 4 sahifa: 3 tasi indeks daraxtining qavatlari, 1 tasi jadval. 6 ms ning hammasi diskdan o'qish (I/O Timings: 6.038) — kesh "sovuq" edi.

Kanon topilmasi: planner vazifalar_pkey ni emas, vazifalar_id_egasi_id_key ni tanladi. Ikkalasi ham id bilan boshlanadi (chap prefiks), narxi teng — planner istalganini olishi mumkin. Rejada "kutilgan" indeks nomini ko'rmasangiz, xavotir olmang — avval narxga qarang.

4.3 Index Only Scan

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM vazifalar WHERE id BETWEEN 5 AND 500;

Natija (qisqartirildi):

 Index Only Scan using vazifalar_pkey on vazifalar  (cost=0.43..22.89 rows=523 width=8) (actual time=0.053..0.154 rows=496.00 loops=1)
   Index Cond: ((id >= 5) AND (id <= 500))
   Heap Fetches: 0
   Buffers: shared hit=178 read=5
 Execution Time: 0.196 ms

Faqat id kerak — u indeksning o'zida. Jadvalga borilmadi (Heap Fetches: 0). Covering indeks darsida bu holatni batafsil ko'rgandik.

4.4 Bitmap Index Scan + Bitmap Heap Scan

Ikki bosqichli o'qish — "Rejada uch rejim" bo'limidagi birinchi reja. Avval Bitmap Index Scan indeksdan hamma mos qatorlarning manzillarini oladi va ularni sahifa raqami bo'yicha tartiblangan "xarita" (bitmap) qiladi. Keyin Bitmap Heap Scan sahifalarni tartib bilan, har birini bir martadan ochadi.

Qachon tanlanadi? Qatorlar ko'p (yuzlab–minglab), lekin jadvalning kichik qismi. Index Scan har qator uchun sahifani alohida ochardi — bir sahifa bir necha marta. Bitmap esa har sahifani bir marta ochadi.

Recheck Cond va Heap Blocks:

• exact=415 — bitmap har qatorning aniq joyini eslab qoldi.
• lossy=7493 (BRIN darsida ko'rgandik) — xotira yetmagani yoki indeks turi sababli bitmap faqat "shu sahifada bor" ni eslaydi. Baza sahifadagi hamma qatorni qayta tekshiradi — Rows Removed by Index Recheck.

Tekshirib ko'ring: Rejada Bitmap Heap Scan … Heap Blocks: exact=415, rows=416. Bu raqamlar nima haqida gapiradi?

5. JOIN algoritmlari

JOIN'lar darsida nima birlashishini o'rgandik. Planner buni uch xil qanday bilan bajara oladi.

5.1 Nested Loop

EXPLAIN (ANALYZE, BUFFERS)
SELECT f.ism, v.matn
FROM foydalanuvchilar f JOIN vazifalar v ON v.egasi_id = f.id
WHERE f.id = 77 LIMIT 5;

Natija (qisqartirildi):

 Limit  (cost=0.71..21.19 rows=5 width=46) (actual time=0.068..0.076 rows=5.00 loops=1)
   ->  Nested Loop  (cost=0.71..762.30 rows=186 width=46) (actual time=0.066..0.074 rows=5.00 loops=1)
         ->  Index Scan using vazifalar_egasi_id_idx on vazifalar v  (cost=0.43..751.67 rows=186 width=35) (actual time=0.015..0.019 rows=5.00 loops=1)
               Index Cond: (egasi_id = 77)
         ->  Materialize  (cost=0.29..8.31 rows=1 width=27) (actual time=0.010..0.010 rows=1.00 loops=5)
               ->  Index Scan using foydalanuvchilar_pkey on foydalanuvchilar f  (cost=0.29..8.30 rows=1 width=27) (actual time=0.040..0.041 rows=1.00 loops=1)
                     Index Cond: (id = 77)
 Execution Time: 0.094 ms

Nested Loop (ichma-ich sikl) — JavaScript'dagi ichma-ich for kabi: tashqi tomonning har qatori uchun ichki tomondan mosini qidiradi. Ichki tomonda indeks bo'lsa va tashqi tomon kichik bo'lsa — eng tez. Bu yerda tashqi tomon 5 ta vazifa, ichki tomon — bitta foydalanuvchi (Materialize uni bir marta o'qib, 5 marta xotiradan bergan: loops=5). 0.094 ms.

Xavfi: tashqi tomon katta bo'lsa (millionlab), ichki tomon million marta bajariladi. Planner tashqi tomonni kam deb noto'g'ri baholasa, Nested Loop falokatga aylanadi.

5.2 Hash Join

"Eng ko'p vazifali 3 foydalanuvchi" so'rovi — butun jadvallar birlashadi:

EXPLAIN (ANALYZE, BUFFERS)
SELECT f.ism, count(*)
FROM foydalanuvchilar f JOIN vazifalar v ON v.egasi_id = f.id
GROUP BY f.ism ORDER BY 2 DESC LIMIT 3;

Natija (asosiy tugunlar, qisqartirildi):

 Limit  (cost=43562.89..43562.90 rows=3 width=27) (actual time=292.043..292.170 rows=3.00 loops=1)
   ->  Sort  (cost=43562.89..43612.89 rows=20000 width=27) (actual time=292.041..292.167 rows=3.00 loops=1)
         Sort Key: (count(*)) DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Finalize HashAggregate  (cost=43104.39..43304.39 rows=20000 width=27) (actual time=286.904..289.328 rows=20000.00 loops=1)
               ->  Gather  (cost=37864.39..42864.39 rows=48000 width=27) (actual time=278.171..281.133 rows=20002.00 loops=1)
                     ->  Partial HashAggregate  (cost=36864.39..37064.39 rows=20000 width=27) (actual time=274.254..275.271 rows=6667.33 loops=3)
                           ->  Hash Join  (cost=696.43..32697.73 rows=833333 width=19) (actual time=6.608..174.331 rows=666666.67 loops=3)
                                 Hash Cond: (v.egasi_id = f.id)
                                 ->  Parallel Index Only Scan using vazifalar_egasi_id_idx on vazifalar v  (cost=0.43..29813.76 rows=833333 width=8) (actual time=0.034..66.462 rows=666666.67 loops=3)
                                 ->  Hash  (cost=446.00..446.00 rows=20000 width=27) (actual time=6.414..6.415 rows=20000.00 loops=3)
                                       Buckets: 32768  Batches: 1  Memory Usage: 1506kB
                                       ->  Seq Scan on foydalanuvchilar f  (cost=0.00..446.00 rows=20000 width=27) (actual time=0.085..2.146 rows=20000.00 loops=3)
 Execution Time: 293.235 ms

Hash Join (hash bilan birlashtirish) ikki bosqichda:

1. Qurish. Kichik tomon (20 000 foydalanuvchi) o'qilib, xotirada hash jadvalga joylanadi: Hash … Memory Usage: 1506kB, Batches: 1 — hammasi xotiraga sig'di. JavaScript'dagi new Map(foydalanuvchilar.map(f => [f.id, f])) g'oyasi.
2. Tekshirish. Katta tomon (2 mln vazifa) bir marta o'qiladi; har qator uchun map.get(egasi_id).

Katta, indeks bo'yicha cheklanmagan birlashtirishlar uchun odatda eng yaxshisi. 2 mln qator 174 ms da birlashdi. Batches 1 dan katta bo'lsa — hash jadval work_mem ga sig'magan va diskka bo'lingan; bu sekinlik belgisi.

5.3 Merge Join

Kanon Hash Join'ni sinov uchun taqiqladi (SET enable_hashjoin = off) va oddiyroq so'rov yubordi:

SET enable_hashjoin = off;
EXPLAIN (ANALYZE)
SELECT count(*)
FROM foydalanuvchilar f JOIN vazifalar v ON v.egasi_id = f.id;
RESET enable_hashjoin;

Natija (asosiy tugunlar, qisqartirildi):

 Finalize Aggregate  (cost=43892.26..43892.27 rows=1 width=8) (actual time=187.521..189.895 rows=1.00 loops=1)
   ->  Gather  (cost=43892.04..43892.25 rows=2 width=8) (actual time=187.437..189.888 rows=3.00 loops=1)
         ->  Partial Aggregate  (cost=42892.04..42892.05 rows=1 width=8) (actual time=183.777..183.778 rows=1.00 loops=3)
               ->  Merge Join  (cost=0.71..40808.71 rows=833333 width=0) (actual time=0.058..144.377 rows=666666.67 loops=3)
                     Merge Cond: (v.egasi_id = f.id)
                     ->  Parallel Index Only Scan using vazifalar_egasi_id_idx on vazifalar v  (cost=0.43..29813.76 rows=833333 width=8) (actual time=0.020..60.559 rows=666666.67 loops=3)
                     ->  Index Only Scan using foydalanuvchilar_pkey on foydalanuvchilar f  (cost=0.29..528.29 rows=20000 width=8) (actual time=0.031..4.337 rows=19983.00 loops=3)
 Execution Time: 189.944 ms

Merge Join (birlashtirib yurish) — ikkala tomon bir xil tartibda (bu yerda ikkala indeks egasi_id/id bo'yicha tartiblangan). Ikki saralangan ro'yxatni ikki barmoq bilan yonma-yon yurgandek: kichigini oldinga suradi, teng bo'lsa — juftlik. Tomonlar allaqachon tartibda bo'lsa yoki natija ham shu tartibda kerak bo'lsa yaxshi.

Diqqat: Bu so'rov Hash Join so'rovidan farq qiladi (GROUP BY f.ism yo'q), shuning uchun 190 ms va 293 ms ni to'g'ridan-to'g'ri solishtirmang. enable_* sozlamalari — faqat sinov uchun. Production'da ularni o'zgartirish plannerni "ko'r" qiladi.

Tekshirib ko'ring: Rejada Nested Loop, ichki tomonda Seq Scan … loops=20000. Nima bo'lyapti va nima qilish kerak?

6. Sort va work_mem

Saralash uchun Postgres xotira oladi — work_mem (sukut 4 MB, har saralash tuguniga alohida). Sig'masa — vaqtinchalik fayllarga yozadi. Kanon 2 mln vazifadan 100 000 tasini matn bo'yicha saraladi:

SET work_mem = '4MB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM vazifalar ORDER BY matn LIMIT 100000;

Natija (asosiy qatorlar, qisqartirildi):

 Limit  (cost=153567.05..165213.70 rows=100000 width=35) (actual time=3754.781..3885.151 rows=100000.00 loops=1)
   Buffers: shared hit=775 read=38794, temp read=12415 written=22253
   ->  Gather Merge  (cost=153567.05..386499.89 rows=1999999 width=35) (actual time=3749.458..3871.498 rows=100000.00 loops=1)
         ->  Sort  (cost=152567.02..154650.36 rows=833333 width=35) (actual time=3702.256..3720.458 rows=33919.00 loops=3)
               Sort Key: matn
               Sort Method: external merge  Disk: 29152kB
               Worker 0:  Sort Method: external merge  Disk: 30232kB
               Worker 1:  Sort Method: external merge  Disk: 29512kB
 Execution Time: 3929.027 ms

external merge Disk: 29152kB — xotiraga sig'madi, uch jarayonning har biri ~30 MB diskka yozdi (temp written=22253 sahifa). 3.9 s.

work_mem ni 256 MB ga ko'tarib, xuddi shu so'rov:

 Limit  (cost=122199.83..133846.48 rows=100000 width=35) (actual time=2708.186..2847.413 rows=100000.00 loops=1)
   Buffers: shared hit=1057 read=38512
   ->  Gather Merge  (cost=122199.83..355132.67 rows=1999999 width=35) (actual time=2706.384..2835.753 rows=100000.00 loops=1)
         ->  Sort  (cost=121199.81..123283.14 rows=833333 width=35) (actual time=2677.673..2686.349 rows=33824.00 loops=3)
               Sort Key: matn
               Sort Method: top-N heapsort  Memory: 17933kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 17724kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 17729kB
 Execution Time: 2855.457 ms

top-N heapsort Memory — xotirada, temp yo'q. Lekin 2.9 s — hali ham sekin!

Kanon xulosasi: ORDER BY matn ning o'zi qimmat. Baza en_US.utf8 collation bilan matnlarni til qoidalari bo'yicha taqqoslaydi — har taqqoslash murakkab. 2 mln matnni saralash uchun millionlab taqqoslash. Xotira 1 soniya yutdi, lekin asosiy davo boshqa: bunday ro'yxatni indeks tartibida olish yoki saralanadigan qatorlarni kamaytirish.

Rejada JIT: bo'limi ham chiqdi (Functions: 7 … Total 13.176 ms). JIT (just-in-time kompilyatsiya) — katta so'rovlarda Postgres ifodalarni mashina kodiga o'giradi. 13 ms — 3.9 s ichida sezilmas. Kichik so'rovlarda JIT ishga tushmaydi.

Diqqat: work_mem ni butun server uchun 256 MB qilmang. U har saralash va har hash tuguni uchun, har ulanishda alohida. 100 ulanish × 3 tugun × 256 MB = 75 GB. Kerak bo'lsa — faqat bitta og'ir hisobot so'rovi uchun, tranzaksiya ichida SET LOCAL work_mem = '256MB'.

7. Taxmin va haqiqat

Rejadagi eng foydali taqqoslash — rows= (taxmin) va actual … rows= (haqiqat). Kanondan ikki misol:

Ikkinchi so'rov rejasi:

               ->  Parallel Seq Scan on vazifalar  (cost=0.00..47826.33 rows=297705 width=0) (actual time=0.358..99.450 rows=399680.33 loops=3)
                     Filter: (bajarildi AND (bajarilgan_vaqt IS NOT NULL))
                     Rows Removed by Filter: 266986

Planner ikki shartni mustaqil deb hisobladi: "60 % bajarilgan × 60 % da vaqt bor ≈ 36 %". Aslida ular bog'liq: bajarilgan vazifada doim vaqt bor (CHECK cheklovi shuni talab qiladi). Haqiqatda 60 %.

Bu yerda xato zararsiz — Seq Scan baribir to'g'ri tanlov. Lekin taxmin 10–100 marta xato bo'lsa, planner noto'g'ri JOIN algoritmini yoki noto'g'ri indeksni tanlaydi. Qoida: sekin rejada birinchi navbatda taxmin va haqiqat keskin farq qiladigan tugunni qidiring. Sabablari va davosi — keyingi darsda.

8. JSON format va vizualizatorlar

Katta reja (50+ tugun) matn sifatida o'qish qiyin. FORMAT JSON mashina o'qiydigan shakl beradi:

EXPLAIN (ANALYZE, FORMAT JSON)
SELECT id FROM vazifalar WHERE id = 5;

Natija (boshi, qisqartirildi):

 [                                               +
   {                                             +
     "Plan": {                                   +
       "Node Type": "Index Only Scan",           +
       "Parallel Aware": false,                  +
       "Async Capable": false,                   +
       "Scan Direction": "Forward",              +
       "Index Name": "vazifalar_id_egasi_id_key",+
       "Relation Name": "vazifalar",             +
       "Alias": "vazifalar",                     +
       "Startup Cost": 0.43,                     +
       "Total Cost": 4.45,                       +
       "Plan Rows": 1,                           +
       "Plan Width": 8,                          +
       "Actual Startup Time": 0.018,             +
       "Actual Total Time": 0.019,               +
       "Actual Rows": 1.00,                      +
       "Actual Loops": 1,                        +

Qator oxiridagi + — psql'ning "satr davom etadi" belgisi, JSON'ning qismi emas.

Bu JSON'ni explain.dalibo.com yoki explain.depesz.com kabi saytlarga qo'yib, rejani rangli daraxt ko'rinishida ko'rish mumkin: eng qimmat tugun qizil bilan belgilanadi. Kanon bu saytlarni sinamagan (tashqi xizmat).

Diqqat: Reja ichida so'rovdagi qiymatlar bor ('foydalanuvchi777@example.com', telefon raqamlari). Production rejasini begona saytga qo'yishdan oldin shaxsiy ma'lumotni olib tashlang yoki vizualizatorni o'z kompyuteringizda ishga tushiring (ikkala loyiha ham ochiq kodli).

9. Node qadami: rejani ilova ichidan olish shart emas

Bu darsda loyiha kodi o'zgarmaydi. EXPLAIN — dasturchi vositasi, API'ning qismi emas. Odatiy ish tartibi:

1. Sekin so'rovni topish (ilova logi yoki pg_stat_statements — keyingi darslardan birida).
2. So'rov matnini va haqiqiy parametrlarini psql'ga ko'chirish.
3. EXPLAIN (ANALYZE, BUFFERS) — tranzaksiya ichida, oxirida ROLLBACK.
4. Tuzatish (indeks, so'rovni qayta yozish) → qayta o'lchash.

Kanon 2-qadamni qo'lda emas, skript bilan qildi. PgOmbori SQL matnini o'zi yig'adi (filtr, saralash, kursor). Uni ko'z bilan ko'chirish xatoga olib keladi. olchov/reja-olchov.ts hovuzdagi har yangi ulanishning query metodini o'raydi. U ombor yuborgan matn va parametrlarni yig'adi, keyin ularni EXPLAIN bilan qayta yuboradi (kanondan, soddalashtirilgan — tiplar olib tashlangan):

let ushla = [];
pool.on("connect", (c) => {
  const asl = c.query.bind(c);
  c.query = (...a) => {
    if (typeof a[0] === "string" && Array.isArray(a[1]))
      ushla.push({ text: a[0], values: a[1] });
    return asl(...a);
  };
});
// … ombor.royxat(sorov) chaqiriladi, keyin har ushlangan so'rov:
const { rows } = await pool.query(
  `EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) ${s.text}`, s.values);

COSTS OFF — rejadan cost=… qavslari olib tashlanadi, actual qoladi. Katta bazada (013/014 bilan qayta qurilgan, eng ko'p vazifali egasi — 479 ta) teg filtri bilan ro'yxat (?teg=ish&bajarildi=false). Skript har rejaning faqat kalit qatorlarini chiqaradi (reja-64.txt, boshi):

=== teg=ish + bajarildi=false: royxat() 5.2 ms, so'rovlar 2
Limit (actual time=1.352..1.356 rows=21.00 loops=1)
  Buffers: shared hit=1741
  ->  Sort (actual time=1.351..1.353 rows=21.00 loops=1)
        Sort Key: v.tartib, v.id
        Sort Method: quicksort  Memory: 28kB
        Buffers: shared hit=1741
        ->  Bitmap Heap Scan on vazifalar v (actual time=0.197..1.334 rows=23.00 loops=1)
              Filter: ((ochirilgan_vaqt IS NULL) AND (NOT bajarildi) AND ((SubPlan 1) = '1'::bigint))
              Rows Removed by Filter: 456
              Buffers: shared hit=1741
              ->  Bitmap Index Scan on vazifalar_egasi_id_idx (actual time=0.039..0.040 rows=479.00 loops=1)
                    Buffers: shared hit=3
              SubPlan 1
                ->  Aggregate (actual time=0.004..0.004 rows=1.00 loops=207)

Shu darsdagidek o'qiymiz — ichkaridan tashqariga:

• Bitmap Index Scan — egasining 479 vazifasi topildi.
• SubPlan 1 … loops=207 — teg sharti ((SELECT count(*) … ) = 1) har nomzod qator uchun alohida bajarildi: 207 marta. Bitta bajarilish 0.004 ms, demak jami ~0.8 ms. Qolgan 272 qatorni undan oldingi ikki shart (o'chirilmagan, bajarilmagan) tashladi — AND dagi keyingi shart tekshirilmadi.
• Rows Removed by Filter: 456 — 479 dan 23 tasi qoldi; Sort ulardan 21 tasini berdi.

Butun so'rov 1.3 ms, 1 741 sahifa (keshdan). Bitta egasi uchun bu yetarli. Lekin loops=207 — eslab qoling: correlated ichki so'rov (Subquery va LATERAL) qatorlar soniga qarab ko'p marta bajariladi. Bu naqsh N+1 muammosi darsida qaytadi.

Sizning qadamingiz — o'z bazangizda (holat 40) kanon so'rovlaridan ikkitasini EXPLAIN (ANALYZE, BUFFERS) bilan ishga tushiring va rejani shu darsdagidek o'qing. 48 qatorda hamma joyda Seq Scan bo'ladi — bu to'g'ri. Kanondagi farqlarni ko'rish uchun 3-mashqdagi kabi katta sinov jadvali kerak.

10. Ko'p uchraydigan xatolar

11. Mashqlar

Mashqlar kurs/mashqlar/26/60-explain/ da. 3-mashq yechimi holat 40 bazasida tekshirilgan (biz ishga tushirdik, PostgreSQL 17.6).

1-mashq (oson): to'ldiring

• So'rovni bajarmasdan faqat reja ko'rsatadigan buyruq —
• O'qilgan sahifalarni ko'rsatadigan parametr —
• rows=3.00 loops=4 — tugun jami nechta qator berdi?
• Kichik tomondan xotirada hash jadval quradigan birlashtirish —
• Saralash xotiraga sig'maganda rejada yoziladigan usul — external

2-mashq (o'rta): rejani o'qing

Kanondagi reja (qisqartirilgan):

 Limit  (cost=714.91..714.96 rows=20 width=43) (actual time=6.097..6.102 rows=20.00 loops=1)
   ->  Sort  (cost=714.91..714.97 rows=25 width=43) (actual time=6.095..6.098 rows=20.00 loops=1)
         Sort Key: muddat
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Bitmap Heap Scan on vazifalar  (cost=5.83..714.32 rows=25 width=43) (actual time=2.722..6.043 rows=59.00 loops=1)
               Recheck Cond: (egasi_id = 77)
               Filter: ((NOT bajarildi) AND (muddat >= '2025-06-01 00:00:00+00'::timestamp with time zone))
               Rows Removed by Filter: 357
               Heap Blocks: exact=415
               ->  Bitmap Index Scan on vazifalar_egasi_id_idx  (cost=0.00..5.82 rows=186 width=0) (actual time=0.065..0.065 rows=416.00 loops=1)

Savollar: (a) qaysi tugun birinchi ishladi? (b) indeks nechta qator berdi va ulardan nechtasi filtrdan o'tdi? (c) qaysi tugunda taxmin haqiqatdan 2 marta ko'p xato? (d) vaqtning asosiy qismi qayerda? (e) qanday indeks Filter va Sort ni yo'qotadi?

3-mashq (qiyin): uch JOIN algoritmini o'zingiz chaqiring

2 000 mijoz va 200 000 buyurtmali ikki jadval yarating (mijoz_id ga indeks). Bitta so'rov shaklini uch holatda EXPLAIN (COSTS OFF) bilan ko'ring: (1) bitta mijoz (WHERE m.id = 7) — Nested Loop kutiladi; (2) hamma mijozlar bo'yicha sum — Hash Join; (3) enable_hashjoin va enable_nestloop o'chirilgan — Merge Join. Har natijani bir gap bilan tushuntiring.

CREATE TABLE t60_mijoz AS
SELECT g AS id, 'Mijoz ' || g AS ism
FROM generate_series(1, 2000) g;
ALTER TABLE t60_mijoz ADD PRIMARY KEY (id);

CREATE TABLE t60_buyurtma AS
SELECT g AS id, 1 + g % 2000 AS mijoz_id, g % 500 * 1000 AS summa
FROM generate_series(1, 200000) g;
CREATE INDEX ON t60_buyurtma (mijoz_id);
ANALYZE t60_mijoz, t60_buyurtma;

EXPLAIN (COSTS OFF)
SELECT m.ism, b.summa
FROM t60_mijoz m JOIN t60_buyurtma b ON b.mijoz_id = m.id
WHERE m.id = 7;

EXPLAIN (COSTS OFF)
SELECT m.ism, sum(b.summa)
FROM t60_mijoz m JOIN t60_buyurtma b ON b.mijoz_id = m.id
GROUP BY m.ism;

SET enable_hashjoin = off;
SET enable_nestloop = off;
EXPLAIN (COSTS OFF)
SELECT m.ism, sum(b.summa)
FROM t60_mijoz m JOIN t60_buyurtma b ON b.mijoz_id = m.id
GROUP BY m.ism;
RESET enable_hashjoin;
RESET enable_nestloop;
DROP TABLE t60_mijoz, t60_buyurtma;

                         QUERY PLAN                         
------------------------------------------------------------
 Nested Loop
   ->  Index Scan using t60_mijoz_pkey on t60_mijoz m
         Index Cond: (id = 7)
   ->  Bitmap Heap Scan on t60_buyurtma b
         Recheck Cond: (mijoz_id = 7)
         ->  Bitmap Index Scan on t60_buyurtma_mijoz_id_idx
               Index Cond: (mijoz_id = 7)
(7 rows)

                         QUERY PLAN                          
-------------------------------------------------------------
 Finalize HashAggregate
   Group Key: m.ism
   ->  Gather
         Workers Planned: 1
         ->  Partial HashAggregate
               Group Key: m.ism
               ->  Hash Join
                     Hash Cond: (b.mijoz_id = m.id)
                     ->  Parallel Seq Scan on t60_buyurtma b
                     ->  Hash
                           ->  Seq Scan on t60_mijoz m
(11 rows)

                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Finalize HashAggregate
   Group Key: m.ism
   ->  Gather
         Workers Planned: 1
         ->  Partial HashAggregate
               Group Key: m.ism
               ->  Merge Join
                     Merge Cond: (b.mijoz_id = m.id)
                     ->  Parallel Index Scan using t60_buyurtma_mijoz_id_idx on t60_buyurtma b
                     ->  Index Scan using t60_mijoz_pkey on t60_mijoz m
(10 rows)

12. Real ishda

• Har sekinlik tahlili shu yerdan boshlanadi: "rejani ko'rsat" — senior dasturchining birinchi savoli.
• ORM'lar (Prisma, Drizzle — 27-qism) yaratgan SQL'ni log'dan olib, xuddi shu tarzda EXPLAIN qilasiz. ORM'ning o'zi rejani ko'rsatmaydi.
• Code review'da yangi og'ir so'rov bilan birga EXPLAIN (ANALYZE, BUFFERS) chiqishini PR'ga qo'shish yaxshi odat.
• Intervyuda: "EXPLAIN va EXPLAIN ANALYZE farqi?", "Hash Join va Nested Loop qachon?", "Rejada nimaga birinchi qaraysiz?"

Xulosa

• EXPLAIN — reja va taxmin; ANALYZE — so'rovni bajaradi (UPDATE/DELETE — faqat BEGIN … ROLLBACK ichida); BUFFERS — sahifalar.
• Reja daraxti ichkaridan tashqariga o'qiladi; actual time va rows — bitta loop uchun (parallel tugunda loops — bir vaqtdagi jarayonlar).
• Scan: Seq (ko'p qator), Index (kam), Index Only (ustunlar indeksda), Bitmap (o'rtacha, sochilgan).
• JOIN: Nested Loop (kichik tashqi + indeks), Hash (katta, tartibsiz), Merge (ikkalasi tartibli).
• external merge Disk — work_mem yetmagan; kanonda 3.9 s → 2.9 s, lekin asosiy narx collation'li taqqoslashda.
• Birinchi navbatda taxmin va haqiqat keskin farq qilgan tugunni qidiring.

Keyingi dars: Statistika va planner — planner taxminlarni qayerdan oladi, nega 186 ta deb o'yladi va statistikani qanday tuzatish mumkin.

Manbalar

• PostgreSQL 18 hujjati — Using EXPLAIN: https://www.postgresql.org/docs/18/using-explain.html
• PostgreSQL 18 hujjati — EXPLAIN buyrug'i (BUFFERS sukut, FORMAT): https://www.postgresql.org/docs/18/sql-explain.html
• PostgreSQL 18 hujjati — Resource Consumption (work_mem): https://www.postgresql.org/docs/18/runtime-config-resource.html
• PostgreSQL 18 hujjati — Planner Method Configuration (enable_*): https://www.postgresql.org/docs/18/runtime-config-query.html
• explain.dalibo.com, explain.depesz.com — reja vizualizatorlari (sinalmagan).
• IlmHamroh kanon o'lchovi (PostgreSQL 18.6, 2 mln vazifa, 2026-10-24); 3-mashq va tugunniOqi — biz ishga tushirdik (PostgreSQL 17.6, Node 24.21.0, Windows 11).