PostgreSQL indekslari: qachon tezlik beradi, qachon zarar qiladi

Indeksni kitobning oxiridagi alifbo mundarijaga o‘xshatish mumkin. Kerakli so‘zni butun kitobni varaqlab emas, mundarijadan topib, to‘g‘ri sahifaga o‘tasiz. Baza indeksi ham xuddi shunday ishlaydi: kerakli yozuvni butun jadvalni o‘qimasdan topadi. Lekin mundarijaning ham narxi bor — u joy egallaydi va kitobga har yangi sahifa qo‘shilganda uni yangilash kerak. Indeks ham aynan shunday: qidiruvni tezlashtiradi, lekin har yangi yozuvda o‘zini yangilab turishi shart. Ya'ni indeksning o‘z narxi bor.

Bu narxni his qilishning eng yaxshi yo‘li — uni o‘z ko‘zingiz bilan ko‘rish. Buni PostgreSQL'da bir necha buyruq bilan qilamiz.

Sekin so‘rovni yasab olamiz#

Avval katta jadval kerak, chunki kichik jadvalda indeksning farqi bilinmaydi. psql ochib, million qatorli jadval yasaymiz:

CREATE TABLE orders (id int, user_id int, created_at timestamptz);

-- million qator: user_id 1 dan 10000 gacha tarqaladi
INSERT INTO orders
SELECT g, (random() * 10000)::int, now() - (random() * 365) * interval '1 day'
FROM generate_series(1, 1000000) g;

Endi bitta foydalanuvchining buyurtmalarini so‘raymiz va baza bu ishni qanday bajarayotganini ko‘ramiz. EXPLAIN ANALYZE — aynan shuning uchun: u so‘rovni haqiqatan bajaradi va qanday yo‘l tanlaganini aytadi.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;

Natijada shunga o‘xshash qatorni ko‘rasiz:

->  Parallel Seq Scan on orders  (cost=0.00..10614.33 rows=42 width=16)
                                 (actual time=0.371..11.374 rows=38 loops=3)
Execution Time: 13.079 ms

Seq Scan degani — baza javob topish uchun butun million qatorni birma-bir o‘qib chiqdi. Bitta foydalanuvchi uchun million qatorni o‘qish — ochiq isrofgarchilik. (Parallel so‘zi chiqsa ajablanmang: PostgreSQL bu ishni bir necha jarayonga bo‘lib bergan, lekin baribir hamma qatorni o‘qiydi. Vaqt esa kompyuteringizga qarab farq qiladi — muhimi, keyingi o‘lchov bilan nisbati.)

Indeks qo‘shib, farqni o‘lchaymiz#

Endi aynan shu so‘rovga mos indeks yasaymiz:

CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC);

O‘sha EXPLAIN ANALYZE so‘rovini yana bir bor ishga tushiring:

->  Index Scan using orders_user_created_idx on orders  (cost=0.42..406.17 rows=100 width=16)
                                                        (actual time=0.018..0.076 rows=20 loops=1)
Execution Time: 0.083 ms

Seq Scan o‘rniga endi Index Scan turibdi va vaqt 13 millisekunddan millisekundning o‘ndan biriga tushdi — bir necha yuz barobar. Baza endi butun jadvalni o‘qimadi, indeks orqali to‘g‘ri o‘sha yigirmata qatorga bordi. Mana shu — indeksning kuchi, va uni taxmin qilib emas, o‘z ko‘zingiz bilan ko‘rdingiz.

Indeksdagi ustunlar tartibiga e'tibor bering: avval tenglik bilan filtrlanadigani (user_id), keyin saralanadigani (created_at). Bu tartib muhim. Teskarisini ham sinab ko‘rdim — (created_at DESC, user_id) indeksi bilan baza indeksdan foydalandi, lekin kerakli 20 qatorni topguncha sanalar bo‘yicha ancha yurishiga to‘g‘ri keldi: 0,08 ms o‘rniga 1,87 ms, ya'ni yigirma barobar sekin.

Unda nega har ustunga indeks qo‘ymaymiz#

Mana shu yerda o‘sha narx — indeksning ikkinchi tomoni — ko‘rinadi. Indeks o‘qishni tezlashtiradi, lekin yozishni sekinlashtiradi: har INSERT va UPDATE da baza jadval bilan birga uning barcha indekslarini ham yangilashi kerak. Jadvalga o‘nta indeks qo‘ysangiz, har yangi qator o‘nta joyga yoziladi. Buni ham o‘lchadim: 200 000 qatorni indekssiz jadvalga yozish 69 millisekund oldi, o‘nta indeksli jadvalga esa 1,65 soniya — 24 barobar ko‘p. Ustiga o‘sha indekslar 43 MB joy egalladi. Shuning uchun "har ehtimolga qarshi hamma ustunga indeks qo‘yay" degan yondashuv aslida bazani sekinlashtiradi.

Indeks o‘zini oqlamaydigan bir necha holat bor. Kichik jadvalda u befoyda — baza baribir to‘liq o‘qishni tanlaydi, chunki bu tezroq. Kam qidirilib, ko‘p yoziladigan ustunda indeks faqat zarar qiladi. Va selektivligi past ustunda — masalan faqat "faol" va "nofaol" degan ikki qiymatli status ustunida — indeks kam yordam beradi, chunki u yozuvlarning yarmini baribir qamrab oladi. Sinovda shunday indeks qurib, WHERE status = 'active' so‘rovini so‘radim: PostgreSQL indeksni umuman ishlatmadi, Seq Scan ni tanladi — chunki yarim jadvalni indeks orqali o‘qish to‘g‘ridan-to‘g‘ri o‘qishdan qimmatroq.

Indeks turlari haqida qisqacha#

Ko‘pincha oddiy B-tree indeksi yetadi, u standart va tenglik hamda diapazon so‘rovlari uchun ishlaydi. Lekin ba'zi holatlar uchun maxsus turlar bor:

Partial indeks ayniqsa qiziq: agar so‘rovlaringiz doim faqat faol yozuvlarni qidirsa, indeksni ham faqat o‘shalarga qurish mumkin — u kichikroq va tezroq bo‘ladi. O‘sha million qatorli jadvalda user_id bo‘yicha to‘liq indeks 6968 kB joy egalladi, WHERE status = 'active' sharti bilan qurilgani esa 3488 kB — ikki barobar kichik.

Amaliy qoida#

Indeksni his-tuyg‘u bilan emas, o‘lchov bilan qo‘shing. Avval haqiqatan sekin so‘rovni toping — buning uchun pg_stat_statements kengaytmasi qaysi so‘rovlar ko‘p vaqt olayotganini ko‘rsatadi. Keyin EXPLAIN ANALYZE bilan sababni tasdiqlang, indeks qo‘shing va farqni qayta o‘lchang. Va vaqti-vaqti bilan umuman ishlatilmayotgan indekslarni topib o‘chiring — ular hech kimga foyda bermay, faqat joy va yozish tezligini yeb yotadi:

SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS hajm
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 — "statistika yig‘ilgandan beri bu indeksga bir marta ham murojaat bo‘lmagan" degani. Shoshilmang: statistika qachondan beri yig‘ilayotganini bilib oling va mavsumiy so‘rovlarni ham hisobga oling.