PostgreSQL indekslari amaliyotda: qachon ishlaydi, qachon yo'q

Men maktab boshqaruv tizimi ustida ishlayman: o'quvchilar, davomat, baholar — jadvallar tez to'ladi, davomat jadvali bir necha oyda millionlab qatorga yetadi. Bir kuni davomat hisoboti sahifasi 3-4 soniyada ochiladigan bo'lib qoldi. "Indeks qo'shamiz, tamom" dedim. Qo'shdim — hech narsa o'zgarmadi. O'shanda yaxshilab tushundim: indeks bor bo'lishi bilan indeks ishlashi ikki xil narsa. Bu maqolada PostgreSQL indekslari qachon ishlashini, qachon esa shunchaki joy egallab yotishini real misollarda ko'ramiz.

Misollar soddalashtirilgan shu sxemada bo'ladi:

CREATE TABLE students (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    class_id  int  NOT NULL,
    full_name text NOT NULL,
    email     text,
    status    text NOT NULL DEFAULT 'active'  -- 'active' | 'archived'
);

CREATE TABLE attendance (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id  bigint NOT NULL REFERENCES students (id),
    lesson_date date   NOT NULL,
    status      text   NOT NULL  -- 'present' | 'absent' | 'late'
);

B-tree qanday ishlaydi — bir daqiqalik intuitsiya

CREATE INDEX deganingizda PostgreSQL sukut bo'yicha B-tree quradi. Uni kitob oxiridagi alifbo ko'rsatkichi deb tasavvur qiling: qiymatlar saralangan holda daraxtda turadi, har bir bargda qiymat va qatorning jadvaldagi manzili bor. Qidiruv ildizdan bargacha bir necha sakrashda tushadi — million qatorli jadvalda ham 3-4 sahifa o'qish kifoya.

Bundan bitta muhim xulosa kelib chiqadi: B-tree saralangan tuzilma, shuning uchun u =, <, >, BETWEEN, ORDER BY va prefiks bo'yicha qidiruvda kuchli. Saralashga tayanmaydigan har qanday shartda esa ojiz. Quyidagi barcha "ishlamaydigan" holatlar aynan shu bitta sababdan kelib chiqadi.

Indeks bor, lekin ishlamaydi: to'rt klassik holat

1. Ustunga funksiya qo'llash. Eng ko'p uchraydigan xato:

-- email ustunida oddiy indeks bor:
CREATE INDEX idx_students_email ON students (email);

-- Bu so'rov indeksni chetlab o'tadi:
SELECT * FROM students WHERE lower(email) = 'aziz@example.com';

Indeks email qiymatlari bo'yicha saralangan, lower(email) bo'yicha emas — PostgreSQL uchun bu butunlay boshqa qiymat. Yechim — expression index:

CREATE INDEX idx_students_email_lower ON students (lower(email));

Endi so'rovdagi ifoda indeksdagi ifodaga aynan mos keladi va Index Scan ishlaydi.

2. Boshida `%` bilan LIKE. full_name LIKE 'Aziz%' — prefiks qidiruv, saralangan daraxt buni uddalaydi: "A-z-i-z bilan boshlanadiganlar" daraxtda yonma-yon turadi. Lekin LIKE '%aziz%' — "istalgan joyda uchrasin" degani, bunday qidiruvning daraxtda boshlanish nuqtasi yo'q, PostgreSQL to'liq skanerga o'tadi. O'quvchini ism bo'lagi bo'yicha qidirish kerak bo'lsa, yechim pg_trgm kengaytmasi va GIN indeksi.

3. Past selektivlik. students.status bo'yicha indeks qo'ydik deylik, lekin o'quvchilarning 95 foizi active. WHERE status = 'active' so'rovida PostgreSQL statistikaga qarab hisoblaydi: indeks orqali qatorlarning 95 foiziga birma-bir murojaat qilishdan ko'ra jadvalni boshidan oxirigacha ketma-ket o'qish arzonroq. Va Seq Scan tanlaydi. Bu xato emas — to'g'ri qaror; bunday ustunga alohida indeks umuman kerak emas edi.

4. Implicit type cast. ORM yoki driver parametr turini noto'g'ri yuborsa, indeks jimgina o'chadi:

-- student_id — bigint, indekslangan. Parametr numeric bo'lib keldi:
EXPLAIN SELECT * FROM attendance WHERE student_id = 100234::numeric;
-- Filter: ((student_id)::numeric = '100234'::numeric)  →  Seq Scan

PostgreSQL ustunni numeric ga cast qiladi — bu yana o'sha "ustunga funksiya" holati. Belgisi: EXPLAIN chiqishida ustun yonidagi kutilmagan ::. Yechim — parametr turini ustun turiga moslash.

Kompozit indeksda ustun tartibi hal qiluvchi

Kompozit indeks — telefon kitobiga o'xshaydi: yozuvlar (familiya, ism) tartibida saralangan. Familiyani bilsangiz — bir zumda topasiz, faqat ismni bilsangiz — butun kitobni varaqlaysiz.

CREATE INDEX idx_att_student_date ON attendance (student_id, lesson_date);

Bu indeks bilan:

• WHERE student_id = 42 — ishlaydi (leftmost prefix);
• WHERE student_id = 42 AND lesson_date >= '2026-05-01' — to'liq ishlaydi;
• WHERE lesson_date = '2026-05-10' — ishlamaydi: birinchi ustunsiz daraxtga kirish nuqtasi yo'q.

"Ma'lum sanada butun maktab davomati" so'rovi tez-tez kerak bo'lsa, unga alohida (lesson_date) indeksi ochiladi. Amaliy qoida: tenglik (=) bilan filtrlanadigan ustunlarni indeks boshiga, diapazon (>=, BETWEEN) ustunini oxiriga qo'ying.

EXPLAIN ANALYZE o'qishni o'rganamiz

Indeks ishlayaptimi-yo'qmi — taxmin qilmang, so'rang:

EXPLAIN ANALYZE
SELECT * FROM attendance
WHERE student_id = 42 AND lesson_date >= date '2026-05-01';

Index Scan using idx_att_student_date on attendance
  (cost=0.43..12.10 rows=38 width=29)
  (actual time=0.031..0.058 rows=41 loops=1)
  Index Cond: ((student_id = 42) AND (lesson_date >= '2026-05-01'::date))
Planning Time: 0.210 ms
Execution Time: 0.089 ms

Men birinchi navbatda nimalarga qarayman:

• Seq Scan — jadval to'liq o'qilyapti. Kichik jadvalda normal, katta jadvalda filtrlangan so'rov uchun signal.
• Index Scan — daraxt orqali kerakli qatorlargagina borilyapti.
• Bitmap Heap Scan — oraliq variant: indeksdan mos sahifalar "xaritasi" yig'iladi, keyin sahifalar bir yo'la o'qiladi. O'rtacha selektivlikda normal holat.
• rows (baholangan) vs actual rows — planner bashorati va real natija. Farq 10 barobardan oshsa, statistika eskirgan — ANALYZE attendance; yurgizing, aks holda planner noto'g'ri reja tanlashda davom etadi.

Index Cond qatoriga ham e'tibor bering: shartingiz shu yerda turibdimi yoki pastdagi Filter ga tushib qoldimi — bu indeks shartni haqiqatan qamraganini ko'rsatadi.

Partial va covering indekslar

Partial indeks — jadvalning faqat kerakli qismini indekslaydi. Davomatda qatorlarning ~95 foizi present, lekin hisobotlarda bizni deyarli har doim kelmaganlar qiziqtiradi:

CREATE INDEX idx_att_absent ON attendance (lesson_date)
WHERE status = 'absent';

Bu indeks faqat absent qatorlarni saqlaydi — to'liq indeksdan yigirma barobar kichik, yozuvda arzon, "shu kuni kim kelmadi" so'rovlariga ideal. Shart so'rovda aynan takrorlanishi kerak: WHERE status = 'absent' AND lesson_date = ....

Covering indeks (INCLUDE) — so'rovga kerak qo'shimcha ustunlarni indeks bargiga qo'shib qo'yadi:

CREATE INDEX idx_att_report ON attendance (student_id, lesson_date)
INCLUDE (status);

Hisobot so'rovi faqat shu uch ustunni so'rasa, PostgreSQL Index Only Scan qiladi — jadvalga umuman tegmaydi. Bizda oylik davomat hisoboti aynan shu indeks bilan sezilarli tezlashgan. Bitta eslatma: Heap Fetches: 0 bo'lishi uchun jadvalda VACUUM muntazam yurib turishi kerak.

Ortiqcha indeks ham zarar

Indeks bepul emas. Har bir INSERT va UPDATE jadvalning har bir indeksini yangilaydi. attendance ga har darsda yuzlab yozuv kiradi — undagi har bir keraksiz indeks har bir yozuvni sekinlashtiradi va diskda joy yeydi. Qaysi indekslar bekor yotganini statistika aytib beradi:

SELECT indexrelid::regclass AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 bo'lgan, unique bo'lmagan indekslar — o'chirish nomzodlari. Men chorakda bir marta shu so'rovni yurgizib turaman.

Xulosa

• Indeks — saralangan tuzilma: ustunga funksiya, boshida %, noto'g'ri type cast uni o'chiradi.
• Past selektivlikda Seq Scan — xato emas, planner'ning to'g'ri qarori.
• Kompozit indeksda tartib: tenglik ustunlari oldin, diapazon oxirida; leftmost prefix'siz indeks ishlamaydi.
• Taxmin qilmang — EXPLAIN ANALYZE yurgizing va rows estimate'ni actual bilan solishtiring.
• Partial va INCLUDE indekslar to'g'ri joyda oddiy indeksdan ancha samarali.
• Har chorakda pg_stat_user_indexes ni tekshirib, o'lik indekslarni o'chiring.

Indeks — "qo'shdim va unutdim" narsa emas, so'rovlaringiz bilan birga yashaydigan vosita. EXPLAIN ANALYZE bilan do'stlashsangiz, u sizga hamma sirini o'zi aytib beradi.