PostgreSQL tarkibiy indeks: qaysi ustunga va qanday tartibda indeks qo'yish

Qisqacha: Indeks faqat so'rov shakliga mos bo'lsa foyda beradi. Tarkibiy indeksda (bir nechta ustunli) tartib hal qiladi: avval tenglik bilan qidiriladigan ustunlar, keyin diapazon yoki saralash ustuni. Kanon bazasida (2 mln vazifa) (egasi_id, bajarildi, muddat) indeksi "muddati yaqin vazifalar" so'rovini 424 sahifadan 23 sahifaga, 6.1 ms dan 0.18 ms ga tushirdi. Lekin har indeks joy egallaydi va har INSERT'ni sekinlatadi: bizning o'lchovda 3 ta indeks yozishni ~5.7 marta sekinlashtirdi.

Bu darsda

• Ustunning selektivligi ni pg_stats dan o'qib, unga indeks kerakmi yo'qmi baholaysiz.
• Tarkibiy indeks yaratasiz va ustunlar tartibini "tenglik → diapazon → saralash" qoidasi bilan tanlaysiz.
• Chap prefiks qoidasi bilan bitta indeks qaysi so'rovlarga yarashini aytasiz.
• Tashqi kalit ustunlarining indeksini katalog so'rovi bilan tekshirasiz.
• Indeks narxini — joy va yozish sekinlashuvini — raqam bilan ko'rsatasiz.

Oldin bilishingiz kerak: Indeks nima: B-tree ichidan, SELECT va WHERE, ORDER BY, LIMIT va OFFSET, Foreign key va referential actions.

1. Nega bu kerak?

Oldingi darsda bitta ustunga indeks qo'ydik va qidiruv 150 ms dan 0.08 ms ga tushdi. Shundan keyin "hamma ustunga indeks qo'yib chiqaman" degan fikr kelishi mumkin. Bu fikr ikki joyda xato.

Birinchidan, real so'rovlarda shart bitta emas. Aziz ilovada "muddati yaqin, hali bajarilmagan vazifalarim" ekranini ochadi. So'rov uch shartli va saralangan:

SELECT id, matn, muddat FROM vazifalar
WHERE egasi_id = 77 AND NOT bajarildi
  AND muddat >= '2025-06-01'
ORDER BY muddat
LIMIT 20;

Uchta alohida indeks bu so'rovni yaxshi yechmaydi. Bitta to'g'ri tuzilgan indeks esa yechadi.

Ikkinchidan, indeks bepul emas. U diskda joy oladi, har INSERT va UPDATE'da yangilanadi. Kanon bazasida vazifalar jadvalining indekslari jadvalning o'zidan katta chiqdi.

O'xshatish: kutubxona katalogi. "Muallif bo'yicha" katalog foydali. "Muqova rangi bo'yicha" katalog deyarli foydasiz — har rangda minglab kitob. Har yangi kitob kelganda esa kutubxonachi hamma kataloglarga kartochka yozadi. Katalog qancha ko'p bo'lsa, kitob qabul qilish shuncha sekin.

Bu darsda o'lchovlar ikki manbadan:

2. Selektivlik: ustun qanchalik "ajratadi"

2.1 Ta'rif

Selektivlik (selectivity) — shart jadvaldagi qatorlarning qancha qismini qoldirishi. Kam qator qoldirsa — selektivlik yuqori, indeks foydali. Ko'p qator qoldirsa — indeks foydasiz: baribir deyarli hamma sahifani o'qish kerak.

Kanon bazasida misollar:

Oldingi darsda WHERE bajarildi indeks bo'lsa ham Seq Scan bilan bajarilganini ko'rgandik. Sabab — selektivlik past. Kitobning 60 % sahifasini mundarija orqali birma-bir ochishdan ko'ra, kitobni boshidan oxirigacha varaqlash tezroq.

2.2 pg_stats: planner nimani biladi

Postgres har ustun haqida statistika yig'adi. Uni pg_stats ko'rinishidan o'qish mumkin (to'liq — Statistika va planner darsida):

SELECT attname, n_distinct, null_frac, avg_width, correlation,
       left(most_common_vals::text, 60) AS mcv,
       left(most_common_freqs::text, 60) AS mcf
FROM pg_stats
WHERE tablename = 'vazifalar'
  AND attname IN ('egasi_id', 'bajarildi', 'ustuvorlik', 'muddat')
ORDER BY 1;

Natija:

  attname   | n_distinct  | null_frac | avg_width |  correlation  |                             mcv                              |                             mcf                              
------------+-------------+-----------+-----------+---------------+--------------------------------------------------------------+--------------------------------------------------------------
 bajarildi  |           2 |         0 |         1 |    0.52899563 | {t,f}                                                        | {0.5977,0.4023}
 egasi_id   |       10428 |         0 |         8 | -0.0039972365 | {927,27,976,1519,2538,3305,3472,3707,175,892,1081,1097,1526, | {0.00056666665,0.0005,0.0005,0.0005,0.0005,0.0005,0.0005,0.0
 muddat     | -0.49839997 |    0.5016 |         8 |     0.6196889 |                                                              | 
 ustuvorlik |           3 |         0 |         4 |    0.37109822 | {orta,yuqori,past}                                           | {0.49706668,0.25236666,0.25056666}
(4 rows)

Hozircha uchta ustunga qarang:

• n_distinct — nechta har xil qiymat. bajarildi da 2, ustuvorlik da 3, egasi_id da ~10 ming. Manfiy son (muddat da −0.498) — "qatorlar sonining ulushi": har ikkinchi qatorda o'z qiymati bor.
• null_frac — NULL ulushi. muddat ning yarmi bo'sh (50 %).
• mcv va mcf — eng ko'p uchraydigan qiymatlar va ularning ulushi. bajarildi: t 59.8 %, f 40.2 %.

Qoida: n_distinct kichik va qiymatlar teng taqsimlangan ustun (bajarildi, ustuvorlik) yolg'iz indeksga yaramaydi. Lekin u tarkibiy indeksning ikkinchi ustuni sifatida foydali bo'lishi mumkin — buni hozir ko'ramiz.

Maslahat: n_distinct taxmin, aniq sanoq emas. Statistika jadvaldan tasodifiy namuna (sukut bo'yicha 30 000 qator) olib hisoblanadi. Haqiqiy sonni SELECT count(DISTINCT egasi_id) bilan bilasiz, lekin u butun jadvalni o'qiydi.

Tekshirib ko'ring: ustuvorlik ustunida 3 qiymat bor, yuqori — 25 %. WHERE ustuvorlik = 'yuqori' uchun alohida indeks qo'yish arziydimi?

3. Tarkibiy indeks

3.1 Faqat FK indeksi bilan

Tarkibiy indeks (composite index) — bir nechta ustundan tuzilgan bitta indeks. Avval u yo'q holatni o'lchaymiz. vazifalar da egasi_id uchun oddiy indeks bor (vazifalar_egasi_id_idx, tashqi kalit darsida qo'yilgan). Kanon so'rovi:

Natijada ikki narsaga qarang: qaysi qatorlarda Filter va Sort bor, va eng yuqoridagi Buffers. Kanon chiqishidagi QUERY PLAN sarlavhasi va cost=… qavslari qisqartirilgan — raqamlar o'zgarmagan.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, matn, muddat FROM vazifalar
WHERE egasi_id = 77 AND NOT bajarildi
  AND muddat >= '2025-06-01'
ORDER BY muddat LIMIT 20;

Natija (kanon, qisqartirildi):

 Limit (actual time=6.097..6.102 rows=20.00 loops=1)
   Buffers: shared hit=151 read=273
   I/O Timings: shared read=0.403
   ->  Sort (actual time=6.095..6.098 rows=20.00 loops=1)
         Sort Key: muddat
         Sort Method: top-N heapsort  Memory: 26kB
         Buffers: shared hit=151 read=273
         I/O Timings: shared read=0.403
         ->  Bitmap Heap Scan on vazifalar (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
               Buffers: shared hit=148 read=273
               I/O Timings: shared read=0.403
               ->  Bitmap Index Scan on vazifalar_egasi_id_idx (actual time=0.065..0.065 rows=416.00 loops=1)
                     Index Cond: (egasi_id = 77)
                     Index Searches: 1
                     Buffers: shared hit=5 read=1
                     I/O Timings: shared read=0.009
 Planning:
   Buffers: shared hit=152 read=7
   I/O Timings: shared read=0.214
 Planning Time: 0.614 ms
 Execution Time: 6.125 ms
(25 rows)

Rejani pastdan yuqoriga o'qiymiz (to'liq o'qish — EXPLAIN ANALYZE darsida):

1. Bitmap Index Scan — indeksdan egasi 77 ning 416 ta qatori topildi.
2. Bitmap Heap Scan — shu qatorlar jadvaldan o'qildi: 415 ta turli sahifa (Heap Blocks: exact=415). Har qator alohida sahifada!
3. Filter — 357 ta qator tashlandi (bajarilgan yoki muddati eski). 59 ta qoldi.
4. Sort — 59 ta qator muddat bo'yicha saralandi, birinchi 20 tasi olindi.

Jami 424 sahifa (151 keshdan, 273 diskdan), 6.1 ms. Indeks faqat birinchi qadamda yordam berdi. Qolgan ishni baza qo'lda qildi.

3.2 Uch ustunli indeks

Endi so'rovdagi uchala ustunni bitta indeksga qo'yamiz:

Natijada yana Filter, Sort va Buffers ni qidiring:

CREATE INDEX t57_a ON vazifalar (egasi_id, bajarildi, muddat);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, matn, muddat FROM vazifalar
WHERE egasi_id = 77 AND NOT bajarildi
  AND muddat >= '2025-06-01'
ORDER BY muddat LIMIT 20;

Natija (kanon, qisqartirildi):

CREATE INDEX
Time: 1586.603 ms (00:01.587)
 Limit (actual time=0.117..0.158 rows=20.00 loops=1)
   Buffers: shared hit=20 read=3
   I/O Timings: shared read=0.041
   ->  Index Scan using t57_a on vazifalar (actual time=0.114..0.152 rows=20.00 loops=1)
         Index Cond: ((egasi_id = 77) AND (bajarildi = false) AND (muddat >= '2025-06-01 00:00:00+00'::timestamp with time zone))
         Index Searches: 1
         Buffers: shared hit=20 read=3
         I/O Timings: shared read=0.041
 Planning:
   Buffers: shared hit=26 read=1
   I/O Timings: shared read=0.037
 Planning Time: 0.349 ms
 Execution Time: 0.182 ms
(13 rows)

Farqni ko'ring:

• Filter yo'q. Uchala shart Index Cond ga kirdi — indeksning o'zi kerakli joyni topdi.
• Sort yo'q. Indeks ichida qatorlar allaqachon muddat bo'yicha tartiblangan. Baza birinchi 20 tasini o'qidi va to'xtadi.
• 23 sahifa, 0.18 ms.

Nega shunday? Tarkibiy indeksni telefon daftari deb tasavvur qiling. Daftar avval familiya, keyin ism bo'yicha tartiblangan. "Karimov Aziz" ni topish uchun avval K harfiga, keyin "Karimov" lar orasida A ga o'tasiz. Bizning indeksda ham shunday: avval egasi_id, uning ichida bajarildi, uning ichida muddat tartibda.

flowchart LR
  A["egasi_id = 76"] --> B["egasi_id = 77"]
  B --> C["bajarildi = false"]
  B --> D["bajarildi = true"]
  C --> E["muddat o'sib boradi:<br/>06-01, 06-03, 06-09 ..."]
  E --> F["birinchi 20 tasi = javob"]
  A --> G["..."]

Diagrammada indeksning bitta "shoxi" ko'rsatilgan. So'rov aynan 77 → false → muddat ≥ 06-01 nuqtasiga tushadi va o'ngga qarab 20 qadam yuradi.

Sahifalar soni bo'yicha uch holat (ikkinchisi — noto'g'ri tartibli indeks bilan, uni keyingi bo'limda ko'ramiz):

Diqqat: Millisekundlarni emas, sahifalarni solishtiring. Birinchi o'lchov "sovuq" keshda bo'lgan (273 sahifa diskdan), ikkinchisi "issiq" keshda (hammasi xotiradan). Vaqt kesh holatiga qarab bir necha marta farq qiladi, sahifalar soni esa so'rov ishining haqiqiy o'lchovi.

Tekshirib ko'ring: Yangi rejada Sort qatori yo'q. Agar so'rovda ORDER BY matn bo'lganida, Sort qaytib kelarmidi?

4. Ustunlar tartibi: tenglik → diapazon → saralash

4.1 Qoida

Tarkibiy indeksda ustunlar quyidagi tartibda turadi:

1. Tenglik (=) bilan qidiriladigan ustunlar — egasi_id = 77, bajarildi = false.
2. Diapazon (>=, <, BETWEEN) yoki saralash (ORDER BY) ustuni — muddat.

Nega diapazon oxirida? Telefon daftariga qayting. "Familiyasi K dan boshlanadigan, ismi Aziz bo'lganlar" desangiz, daftar yordam bermaydi: K harfidagi hamma familiyalarni varaqlaysiz, chunki "Aziz" lar har familiya ichida alohida. Diapazon ustunidan keyingi ustunlar qidiruvni toraytirmaydi — ular faqat filtr bo'lib qoladi.

4.2 Noto'g'ri tartib: kanon o'lchovi

Kanon (muddat, egasi_id) indeksini yaratdi va to'g'ri indeksni o'chirdi:

Natijada (faqat reja qatorlari) qaysi indeks nomi chiqqaniga qarang:

CREATE INDEX t57_b ON vazifalar (muddat, egasi_id);
DROP INDEX t57_a;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, matn, muddat FROM vazifalar
WHERE egasi_id = 77 AND NOT bajarildi
  AND muddat >= '2025-06-01'
ORDER BY muddat LIMIT 20;

Natija (kanon, qisqartirildi):

 Limit (actual time=0.703..0.708 rows=20.00 loops=1)
   Buffers: shared hit=418
   ->  Sort (actual time=0.702..0.704 rows=20.00 loops=1)
         Sort Key: muddat
         Sort Method: top-N heapsort  Memory: 26kB
         Buffers: shared hit=418
         ->  Bitmap Heap Scan on vazifalar (actual time=0.285..0.677 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
               Buffers: shared hit=418
               ->  Bitmap Index Scan on vazifalar_egasi_id_idx (actual time=0.051..0.052 rows=416.00 loops=1)
                     Index Cond: (egasi_id = 77)

Planner yangi indeksni ishlatmadi. U eski reja — FK indeksi + Sort — arzonroq deb hisobladi. To'g'ri qildi: muddat >= 2025-06-01 jadvalning katta qismiga to'g'ri keladi, (muddat, egasi_id) indeksida esa bu qatorlar hamma egalar aralash yotadi. Indeks bor, lekin foydasi yo'q — faqat joy oladi.

Kichik jadvalda o'zimiz ham sinadik (biz ishga tushirdik, PostgreSQL 17.6; 400 000 qator, 2-mashqdagi jadval). Ikkala indeks ham Index Scan bilan ishlatildi, lekin o'qilgan sahifalar keskin farq qildi:

Noto'g'ri tartibda baza diapazon boshidan yurib, har yozuvda "egasi 77 mi?" deb tekshiradi. Reja nomi bir xil (Index Scan) bo'lsa ham, ish 6 marta ko'p.

4.3 Saralash yo'nalishi

B-tree indeksni ikki tomonga ham o'qish mumkin. (egasi_id, muddat) indeksi ORDER BY muddat ga ham, ORDER BY muddat DESC ga ham yaraydi. Lekin aralash yo'nalish (ORDER BY yaratilgan_vaqt DESC, id ASC) uchun indeksni o'sha yo'nalishda yaratish kerak.

Kanon loyihasida ro'yxat sahifasi uchun shunday indeks bor (real loyiha sxemasi darsida qo'shilgan):

CREATE INDEX vazifalar_royxat_idx ON vazifalar
  (egasi_id, yaratilgan_vaqt DESC, id DESC)
  WHERE ochirilgan_vaqt IS NULL;

WHERE qismi — partial indeks, keyingi darsda o'rganamiz. API'ning ro'yxat so'rovi aynan shu indeks bilan saralashsiz bajariladi:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, matn FROM faol_vazifalar
WHERE egasi_id = 77
ORDER BY yaratilgan_vaqt DESC, id DESC LIMIT 20;

Natija (kanon; qisqartirildi):

 Limit (actual time=0.159..0.198 rows=20.00 loops=1)
   Buffers: shared hit=22 read=1
   I/O Timings: shared read=0.064
   ->  Index Scan using vazifalar_royxat_idx on vazifalar (actual time=0.156..0.193 rows=20.00 loops=1)
         Index Cond: (egasi_id = 77)
         Index Searches: 1
         Buffers: shared hit=22 read=1
         I/O Timings: shared read=0.064
 Planning:
   Buffers: shared hit=50
 Planning Time: 0.597 ms
 Execution Time: 0.226 ms
(12 rows)

id DESC nega kerak? Bir xil yaratilgan_vaqt li ikki vazifa bo'lsa, tartib aniq bo'lishi uchun (barqaror tartib). Bu ustun keyset sahifalash darsida hal qiluvchi bo'ladi.

Tekshirib ko'ring: So'rov WHERE egasi_id = $1 AND ustuvorlik = $2 ORDER BY muddat. Qaysi indeks to'g'ri: (muddat, egasi_id, ustuvorlik) yoki (egasi_id, ustuvorlik, muddat)?

5. Chap prefiks: bitta indeks qaysi so'rovlarga yaraydi

5.1 Qoida

(a, b, c) indeksi quyidagi shartlarga samarali yaraydi:

Bu chap prefiks (leftmost prefix) qoidasi: indeks chapdan boshlab ketma-ket ustunlar bilan ishlaydi. Telefon daftari familiyasiz "faqat ismi Aziz bo'lganlar" ga yordam bermaydi.

5.2 Kanon: birinchi ustunsiz so'rov

t57_a (egasi_id, bajarildi, muddat) hali bor paytda kanon egasi_id siz so'rov yubordi:

EXPLAIN SELECT count(*) FROM vazifalar
WHERE bajarildi AND muddat >= '2026-01-01';

Natija:

                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=50758.85..50758.86 rows=1 width=8)
   ->  Gather  (cost=50758.64..50758.85 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=49758.64..49758.65 rows=1 width=8)
               ->  Parallel Index Only Scan using t57_a on vazifalar  (cost=0.43..49536.59 rows=88820 width=0)
                     Index Cond: ((bajarildi = true) AND (muddat >= '2026-01-01 00:00:00+00'::timestamp with time zone))
(6 rows)

Indeks ishlatildi, lekin narxga qarang: 49 536. Butun jadvalni o'qish (Parallel Seq Scan) shu bazada ~47 826 turadi (EXPLAIN darsida ko'ramiz). Ya'ni bu deyarli butun indeksni boshidan oxirigacha o'qish. Indeks jadvaldan ixchamroq bo'lgani uchun tanlangan, lekin "kerakli joyga sakrash" yo'q.

Maslahat: PostgreSQL 18 da B-tree uchun skip scan qo'shildi: birinchi ustunda shart bo'lmasa, baza uning har bir qiymati uchun alohida "sakrab" qidira oladi. Bu birinchi ustunda kam turli qiymat bo'lganda foydali (masalan, 3 ta). egasi_id da ~10 ming qiymat bor — 10 ming sakrash butun indeksni o'qishdan farq qilmaydi. Shuning uchun chap prefiks qoidasi amalda kuchida qoladi (hujjat: PostgreSQL 18, "Multicolumn Indexes").

5.3 Ortiqcha indekslar

Chap prefiksdan muhim xulosa: (a, b) indeksi bor bo'lsa, alohida (a) indeksi odatda ortiqcha. (a, b) ning o'zi a = ? so'rovlariga yaraydi.

Lekin ehtiyot bo'ling. Kanondagi vazifalar_royxat_idx ham egasi_id bilan boshlanadi, ammo u partial — faqat o'chirilmagan vazifalarni o'z ichiga oladi. Savatdagi vazifalarni yoki FK tekshiruvini u qoplay olmaydi. Shuning uchun vazifalar_egasi_id_idx (22 MB) o'z o'rnida qoladi.

Tekshirib ko'ring: Jadvalda (egasi_id, muddat) indeksi bor. Quyidagi so'rovlardan qaysi biri undan samarali foydalanadi? (a) WHERE egasi_id = 5; (b) WHERE muddat < now(); (c) WHERE egasi_id = 5 ORDER BY muddat DESC.

6. Tashqi kalitlarga indeks

6.1 Postgres buni o'zi qilmaydi

PRIMARY KEY va UNIQUE uchun Postgres indeksni avtomatik yaratadi. Tashqi kalit (foreign key) uchun — yo'q. Bu tashqi kalit darsida aytilgan edi. Endi nega muhimligini aniq ko'ramiz.

FK ustunida indeks bo'lmasa, ikki joy sekinlashadi:

1. Ota qatorni o'chirish. Foydalanuvchi o'chirilsa, Postgres ON DELETE CASCADE uchun uning hamma vazifalarini topishi kerak. Indekssiz — butun vazifalar jadvalini o'qiydi. Har bitta o'chirishda.
2. JOIN. foydalanuvchilar JOIN vazifalar ON v.egasi_id = f.id — "shu foydalanuvchining vazifalari" ni topish indekssiz qimmat.

6.2 Katalogdan tekshirish

Qaysi FK ustunlarida indeks borligini tizim katalogidan so'rash mumkin. Indeks FK ustuni bilan boshlanishi kerak (chap prefiks!). Biz ishga tushirdik (PostgreSQL 17.6, holat 40 sxemasi):

SELECT c.conrelid::regclass AS jadval,
       a.attname AS fk_ustun,
       EXISTS (SELECT 1 FROM pg_index i
               WHERE i.indrelid = c.conrelid
                 AND i.indkey[0] = c.conkey[1]) AS indeks_bormi
FROM pg_constraint c
JOIN pg_attribute a
  ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
ORDER BY 1, 2;

Natija:

     jadval     |  fk_ustun  | indeks_bormi 
----------------+------------+--------------
 vazifalar      | egasi_id   | t
 vazifalar      | ota_id     | t
 teglar         | egasi_id   | t
 vazifa_teglari | teg_id     | t
 vazifa_teglari | vazifa_id  | t
 izohlar        | muallif_id | t
 izohlar        | vazifa_id  | t
(7 rows)

Yettala FK ustunining indeksi bor. Kanon review'si ham shunday chiqqan edi. Qisqacha nima bo'ldi:

• pg_constraint — hamma cheklovlar; contype = 'f' — faqat tashqi kalitlar.
• conkey[1] — FK ning birinchi ustuni raqami, pg_attribute dan uning nomi olinadi.
• pg_index.indkey[0] — indeksning birinchi ustuni. Ikkalasi teng bo'lsa — indeks FK bilan boshlanadi.

Diqqat: Bu so'rov soddalashtirilgan: faqat FK ning birinchi ustunini tekshiradi. Tarkibiy FK ((ota_id, egasi_id)) uchun ikkinchi ustun ham kerak bo'lishi mumkin. Jadval tartibi esa nom bo'yicha emas, regclass ichki raqami bo'yicha — shuning uchun vazifalar birinchi.

7. Indeks bepul emas

7.1 Joy

Kanon bazasida vazifalar jadvali 309 MB. Uning indekslari:

SELECT indexrelid::regclass AS indeks,
       pg_size_pretty(pg_relation_size(indexrelid)) AS hajm,
       idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'vazifalar'
ORDER BY 1;

Natija:

           indeks            |    hajm    | idx_scan 
-----------------------------+------------+----------
 vazifalar_pkey              | 86 MB      |   442356
 vazifalar_egasi_id_idx      | 22 MB      |        1
 vazifalar_id_egasi_id_key   | 111 MB     |  1600377
 vazifalar_ota_id_idx        | 8192 bytes |        0
 vazifalar_egasi_id_matn_key | 156 MB     |        0
 vazifalar_royxat_idx        | 123 MB     |        0
(6 rows)

Indekslar jami ~498 MB — jadvaldan 1.6 marta katta. Ayniqsa vazifalar_id_egasi_id_key ga qarang: 111 MB. U faqat bitta narsa uchun bor — kichik vazifa faqat o'sha egasining vazifasiga bog'lanishi uchun (tarkibiy FK, ALTER TABLE darsida qo'shilgan). Bu ongli qaror: kafolat uchun 111 MB to'laymiz.

Matn ustunlari (matn_key) eng qimmat: indeks yozuvida satrning o'zi saqlanadi.

idx_scan — indeks necha marta ishlatilgani. royxat_idx da 0 — bu bazada hali API so'rovlari yuborilmagan. Ishlatilmaydigan indekslarni topish — sekin so'rovlarni topish darsida.

7.2 Yozish

Har INSERT'da baza jadvalga va har bir indeksga yozuv qo'shadi. Indeks qancha ko'p bo'lsa, yozish shuncha sekin. Biz o'lchadik (PostgreSQL 17.6, Windows 11, i5-12500H): 200 000 qatorni bitta INSERT … SELECT bilan yozish, har holat 3 marta. Har o'lchov oldidan TRUNCATE t_yoz. Natijada birinchi uchta vaqt — indekssiz, keyingi uchtasi — (a) indeksi bilan, oxirgi uchtasi — (a), (b, a) va (c) bilan:

CREATE TABLE t_yoz (
  id bigint GENERATED ALWAYS AS IDENTITY,
  a int, b int, c text
);
\timing on
INSERT INTO t_yoz (a, b, c)
SELECT g % 1000, g % 7, 'matn ' || g
FROM generate_series(1, 200000) g;

Natija:

Time: 522.405 ms
Time: 443.599 ms
Time: 430.553 ms
Time: 821.241 ms
Time: 829.048 ms
Time: 846.651 ms
Time: 2233.255 ms (00:02.233)
Time: 2512.002 ms (00:02.512)
Time: 2650.558 ms (00:02.651)

Bitta indeks yozishni ~1.9 marta, uchtasi ~5.7 marta sekinlashtirdi. Jadval 11 MB, indekslar 14 MB bo'ldi.

Halol eslatma: kanon vazifalar jadvalida xuddi shu tajribani qilganda (10 000 qator, matn indeksi bilan va usiz) farq ko'rinmadi — ikkalasi ~1.26 s. Sabab: u jadvalda har INSERT'da trigger'lar (teglar, tarix) ishlaydi va ular indeksdan ancha qimmat. Indeks narxi doim bor, lekin u har doim eng katta narx bo'lmaydi.

Tekshirib ko'ring: Jurnal jadvaliga sekundiga 5 000 qator yoziladi, o'qish esa kuniga bir marta hisobot uchun. Bu jadvalga 6 ta indeks qo'yish to'g'rimi?

8. Node qadami: indeks so'rovdan keyin keladi

Bu darsda loyihaga yangi indeks qo'shilmadi. Kanon t57_a va t57_b ni o'lchadi va o'chirdi. Sabablari:

1. "Muddati yaqin" so'rovi API'da yo'q. Indeks so'rov uchun quriladi. So'rov yo'q joyga indeks — faqat joy va yozish narxi.
2. API ro'yxat so'rovi allaqachon qoplangan. vazifalar-api ni Postgres'ga ulaganda (node-postgres asoslari) 013-api-tartib.sql sukut ro'yxat uchun vazifalar_tartib_idx (egasi_id, tartib, id) ni qo'shgan. Bu aynan shu darsning qoidasi: egasi_id — tenglik, tartib, id — saralash (ORDER BY v.tartib, v.id).
3. Indeks qo'shish arzon. CREATE INDEX 2 mln qatorda 1.6 s oldi. Kerak bo'lganda — alohida SQL fayl bilan qo'shiladi.

Saralash yo'nalishi bo'limidagi qoida API'da ham ko'rindi. GET /v1/vazifalar?sort=-yaratildi — ORDER BY yaratilgan_vaqt DESC, id (ikkinchi kalit 25-qism API'sida doim o'sish tartibida). vazifalar_royxat_idx esa id DESC bilan. Kanon ombor yuborgan haqiqiy SQL'ning rejasini katta bazada ko'rdi (reja-64.txt, 013/014 bilan qayta qurilgan baza; COSTS OFF):

=== sort=-yaratildi: royxat() 3.2 ms, so'rovlar 2
Limit (actual time=0.048..0.051 rows=21.00 loops=1)
  Buffers: shared hit=25
  ->  Incremental Sort (actual time=0.047..0.048 rows=21.00 loops=1)
        Sort Key: yaratilgan_vaqt DESC, id
        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
        Buffers: shared hit=25
        ->  Index Scan using vazifalar_royxat_idx on vazifalar v (actual time=0.011..0.029 rows=22.00 loops=1)
              Buffers: shared hit=25
  Buffers: shared hit=1
Execution Time: 0.064 ms

Nima bo'ldi? Indeks birinchi kalitni (yaratilgan_vaqt DESC) tartibda berdi, lekin ikkinchi kalit yo'nalishi mos emas. Shuning uchun Postgres Incremental Sort qo'shdi: bir xil vaqtli kichik guruhlarnigina qayta saraladi (bu yerda 22 qator). Narxi kichik — 25 sahifa, 0.064 ms. Kanon buni ongli qoldirdi: narx kichik, mos yo'nalishli yangi indeks esa yana joy va yozish narxi (vazifalar_royxat_idx ning o'zi 123 MB).

Sizning qadamingiz — o'z bazangizda (holat 40) ro'yxat so'rovining rejasini ko'ring. Biz ishga tushirdik (PostgreSQL 17.6, 48 vazifa):

EXPLAIN (COSTS OFF)
SELECT id, matn FROM faol_vazifalar
WHERE egasi_id = 1
ORDER BY yaratilgan_vaqt DESC, id DESC LIMIT 20;

Natija:

                              QUERY PLAN                              
----------------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: vazifalar.yaratilgan_vaqt DESC, vazifalar.id DESC
         ->  Seq Scan on vazifalar
               Filter: ((ochirilgan_vaqt IS NULL) AND (egasi_id = 1))
(5 rows)

Indeks bor, lekin Seq Scan! Xato emas. 48 qatorli jadval bitta sahifaga sig'adi. Bitta sahifani o'qish indeks orqali sakrashdan arzon. Indeks foydasini ko'rish uchun katta ma'lumot kerak — shuning uchun kanon o'lchovlari 2 mln qatorli bazada.

9. Ko'p uchraydigan xatolar

Yana bitta xato — vaqtni solishtirib xulosa chiqarish. "Indeks bilan 0.7 ms, usiz 6.1 ms" — ehtimol farqning katta qismi kesh. Sahifalar sonini (Buffers) solishtiring.

10. Mashqlar

Mashqlar kurs/mashqlar/26/57-tarkibiy/ da. SQL yechimlar holat 40 bazasida tekshirilgan (biz ishga tushirdik, PostgreSQL 17.6).

1-mashq (oson): to'ldiring

• Shart jadvaldagi qatorlarning qancha qismini qoldirishi —
• pg_stats da turli qiymatlar soni ustuni —
• Tarkibiy indeksda tenglik ustunlaridan keyin keladigan ustun turi —
• (a, b, c) indeksi b = ? shartiga deyarli yaramasligining qoidasi — chap
• Postgres avtomatik indeks yaratmaydigan cheklov turi —

2-mashq (o'rta): ustunlar tartibini tanlang

Jasur "yuqori ustuvorlikli, 1-iyundan keyin yaratilgan vazifalarim, eskisi birinchi" sahifasini so'radi:

SELECT id FROM t57_mashq
WHERE egasi_id = 77 AND ustuvorlik = 'yuqori'
  AND yaratilgan_vaqt >= '2026-06-01'
ORDER BY yaratilgan_vaqt
LIMIT 20;

400 000 qatorli sinov jadvalini yarating (generate_series — tahliliy SQL darsida ishlatilgan), unga mos indeks qo'ying va EXPLAIN (COSTS OFF) bilan rejada Sort yo'qligini tekshiring. COSTS OFF — narx raqamlarisiz, faqat reja shakli.

CREATE TABLE t57_mashq AS
SELECT g AS id,
       1 + g % 400 AS egasi_id,
       (ARRAY['past', 'orta', 'orta', 'yuqori'])[1 + g / 400 % 4]
         ::ustuvorlik AS ustuvorlik,
       timestamptz '2026-01-01 00:00+05'
         + g * interval '1 minute' AS yaratilgan_vaqt
FROM generate_series(1, 400000) g;

-- tenglik, tenglik, keyin diapazon + saralash
CREATE INDEX t57_mashq_togri
  ON t57_mashq (egasi_id, ustuvorlik, yaratilgan_vaqt);
ANALYZE t57_mashq;

EXPLAIN (COSTS OFF)
SELECT id FROM t57_mashq
WHERE egasi_id = 77 AND ustuvorlik = 'yuqori'
  AND yaratilgan_vaqt >= '2026-06-01'
ORDER BY yaratilgan_vaqt
LIMIT 20;

                                                                        QUERY PLAN                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit
   ->  Index Scan using t57_mashq_togri on t57_mashq
         Index Cond: ((egasi_id = 77) AND (ustuvorlik = 'yuqori'::ustuvorlik) AND (yaratilgan_vaqt >= '2026-06-01 00:00:00+00'::timestamp with time zone))
(3 rows)

3-mashq (qiyin): indeksi yo'q tashqi kalitni toping

Loyihaga "vazifa kuzatuvchilari" jadvali kerak: bitta vazifani bir nechta foydalanuvchi kuzatadi. Jadvalni ikkala FK va tarkibiy PK bilan yarating. Keyin katalog so'rovi bilan indeksi yo'q FK ustunini toping, indeks qo'ying va qayta tekshiring. Hammasini tranzaksiyada qiling va oxirida ROLLBACK — kanon sxemasi o'zgarmasin.

Ishora: PK (vazifa_id, foydalanuvchi_id) o'zi indeks yaratadi. Chap prefiks qoidasini eslang.

BEGIN;
CREATE TABLE vazifa_kuzatuvchilar (
  vazifa_id bigint NOT NULL
    REFERENCES vazifalar (id) ON DELETE CASCADE,
  foydalanuvchi_id bigint NOT NULL
    REFERENCES foydalanuvchilar (id) ON DELETE CASCADE,
  PRIMARY KEY (vazifa_id, foydalanuvchi_id)
);

SELECT c.conrelid::regclass AS jadval,
       a.attname AS fk_ustun,
       EXISTS (SELECT 1 FROM pg_index i
               WHERE i.indrelid = c.conrelid
                 AND i.indkey[0] = c.conkey[1]) AS indeks_bormi
FROM pg_constraint c
JOIN pg_attribute a
  ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND c.conrelid = 'vazifa_kuzatuvchilar'::regclass
ORDER BY 2;

CREATE INDEX vazifa_kuzatuvchilar_foydalanuvchi_id_idx
  ON vazifa_kuzatuvchilar (foydalanuvchi_id);
-- ... shu katalog so'rovi qayta
ROLLBACK;

        jadval        |     fk_ustun     | indeks_bormi 
----------------------+------------------+--------------
 vazifa_kuzatuvchilar | foydalanuvchi_id | f
 vazifa_kuzatuvchilar | vazifa_id        | t
(2 rows)

     fk_ustun     | indeks_bormi 
------------------+--------------
 foydalanuvchi_id | t
 vazifa_id        | t
(2 rows)

11. Real ishda

• Kod review'da "bu so'rovga indeks bormi?" — eng ko'p beriladigan savollardan. Javob — EXPLAIN chiqishi bilan, taxmin bilan emas.
• Migratsiyada katta jadvalga CREATE INDEX jadvalni yozishdan qulflaydi. Production'da CREATE INDEX CONCURRENTLY ishlatiladi (qulfsiz, lekin sekinroq; tranzaksiya ichida ishlamaydi) — xavfli migratsiyalar darsida (27-qism) batafsil.
• ORM'lar (Prisma, Drizzle — 27-qism) sxemada @@index([egasiId, muddat]) ko'rinishida tarkibiy indeks yozadi. Ustunlar tartibi qoidasi o'sha yerda ham aynan shu.
• Intervyuda: "Composite index'da ustunlar tartibi qanday tanlanadi?", "Nega FK ga indeks kerak?", "Indeks qachon zarar qiladi?"

Xulosa

• Selektiv ustun (kam qator qoldiradigan) indeksdan foyda oladi; bajarildi kabi 2 qiymatli ustun yolg'iz — yo'q.
• Ko'p shartli so'rovga bitta tarkibiy indeks: tenglik ustunlari → diapazon yoki saralash ustuni.
• To'g'ri tartib Filter va Sort ni yo'qotadi: kanonda 424 → 23 sahifa.
• Chap prefiks: (a, b, c) — a, a, b, a, b, c uchun; b yolg'iz uchun deyarli yo'q.
• FK ustunlariga indeksni o'zingiz qo'yasiz; katalog so'rovi bilan tekshiriladi.
• Indeks joy (kanonda indekslar jadvaldan katta) va yozish vaqti (bizda 3 indeks — ~5.7 marta) bilan to'lanadi.

Keyingi dars: Partial, expression va covering indekslar — indeksni kichraytirish (faqat kerakli qatorlar), ifodaga indeks (lower(email)) va jadvalga umuman tegmaydigan Index Only Scan.

Manbalar

• PostgreSQL 18 hujjati — Multicolumn Indexes (skip scan bilan): https://www.postgresql.org/docs/18/indexes-multicolumn.html
• PostgreSQL 18 hujjati — Indexes and ORDER BY: https://www.postgresql.org/docs/18/indexes-ordering.html
• PostgreSQL 18 hujjati — pg_stats ko'rinishi: https://www.postgresql.org/docs/18/view-pg-stats.html
• PostgreSQL 18 hujjati — Foreign Keys (indeks avtomatik emas): https://www.postgresql.org/docs/18/ddl-constraints.html#DDL-CONSTRAINTS-FK
• IlmHamroh kanon o'lchovi (PostgreSQL 18.6, 2 mln vazifa, 2026-10-24); yozish o'lchovi, katalog so'rovi va mashqlar — biz ishga tushirdik (PostgreSQL 17.6, Windows 11, i5-12500H).