PostgreSQL indeks turlari: B-tree, GIN, GiST, BRIN va qo‘llanishi

Qisqa javob

Indeksni ustun turiga qarab emas, WHERE dagi operatorga qarab tanlang:

Yana Hash (faqat tenglik) va SP-GiST (muvozanatsiz bo‘lish, masalan IP-manzillar yoki nuqtalar uchun) bor, lekin yuqoridagi to‘rt tur real vazifalarning aksariyatini qamrab oladi.

B-tree: bejiz standart indeks emas

B-tree kalitlarni saralangan holda saqlaydi, shuning uchun “aniq topish”ga ham, “oraliqni topish”ga ham javob beradi, qatorlarni allaqachon tartiblangan holda qaytarib, saralashdan xalos qilishi ham mumkin.

• Ko‘p ustunli indeksda ustunlar tartibi muhim: (customer_id, created_at) WHERE customer_id = ? ORDER BY created_at ga xizmat qiladi, lekin faqat created_at bo‘yicha filtrga emas.
• LIKE 'abc%' B-tree’dan faqat C collation yoki text_pattern_ops operator klassi bilan foydalanadi. LIKE '%abc%' — hech qachon.
• Selektivligi past ustunlar (mantiqiy bayroq) yolg‘iz o‘zi B-tree’dan kamdan-kam foyda ko‘radi.

GIN: “ichida nima bor” uchun

GIN har bir qiymatni elementlarga ajratadi — jsonb kalit va qiymatlari, massiv elementlari, hujjat so‘zlari — va har bir element uchun qatorlar ro‘yxatini saqlaydi.

CREATE INDEX ON orders USING gin (attributes jsonb_path_ops);
-- xizmat qiladi: WHERE attributes @> '{"delivery": "express"}'

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON products USING gin (name gin_trgm_ops);
-- xizmat qiladi: WHERE name ILIKE '%telefon%'

• jsonb_path_ops ixchamroq va @> uchun tezroq, lekin ? kabi kalit mavjudligini tekshiruvchi operatorlarni qo‘llab-quvvatlamaydi. Standart jsonb_ops ko‘proq operatorlarni qo‘llaydi.
• GIN’dan o‘qish tez, yozish qimmatroq: bitta qator indeksdagi ko‘p yozuvlarni yangilaydi. Pending list (fastupdate) buni yumshatadi, lekin ro‘yxat birlashtirilguncha ba’zi qidiruvlarni sekinlashtiradi.

GiST: kesishmalar, geometriya va “eng yaqin”

GiST — bu karkas: har bir tugun o‘z avlodlarini qoplovchi predikatni saqlaydi (masalan, chegaralovchi to‘rtburchak). U “nima kesishadi” va “nima eng yaqin” degan savollarga javob beradi.

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
  room_id int,
  during  tstzrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

Bunday istisno cheklovi xonani ikki marta band qilishni ma’lumotlar bazasi darajasida imkonsiz qiladi. GiST’da PostGIS geoma’lumotlari va ORDER BY location <-> point LIMIT 10 orqali eng yaqin qo‘shnilarni qidirish ishlaydi. To‘liq matnli qidiruv uchun GiST GIN’dan kichikroq va yangilashda arzonroq, lekin qidiruvda odatda sekinroq.

BRIN: ulkan jadvallar uchun mitti indekslar

BRIN jadval sahifalarining har bir oralig‘i uchun faqat minimum va maksimumni saqlaydi. Agar qatorlar jismonan ustun tartibida yozilsa — append-only hodisalar jadvalidagi created_at kabi — PostgreSQL mos qatorlar bo‘lishi mumkin bo‘lmagan barcha oraliqlarni o‘tkazib yuboradi.

CREATE INDEX ON events USING brin (created_at);

BRIN indeksi xuddi shu ustundagi B-tree’dan bir necha tartib kichik bo‘lishi mumkin. Agar ustun qatorlarning jismoniy tartibi bilan bog‘liq bo‘lmasa — masalan, ko‘p yangilanishlardan keyin yoki tasodifiy UUID’larda — u foydasiz.

Qisman, ifoda va qoplovchi indekslar

• Qisman: faqat so‘raladigan qatorlarni indekslang. CREATE INDEX ON orders (created_at) WHERE status = 'pending'; buyurtmalarning ko‘pchiligi yakunlangan ekan, kichik bo‘lib qoladi.
• Ifoda bo‘yicha: ifoda natijasiga indeks. CREATE INDEX ON users (lower(email)); faqat so‘rov aynan lower(email) ni ishlatsa ishlaydi.
• Qoplovchi: CREATE INDEX ON orders (customer_id) INCLUDE (total); faqat indeksning o‘zidan javob berishga imkon beradi (index-only scan), agar visibility map dolzarb bo‘lsa — bu esa vacuum’ga bog‘liq.

Ishlatilmayotgan indekslarni qanday topish mumkin

Har bir indeks yozishni sekinlashtiradi va joy egallaydi. Statistikani tekshiring:

SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

idx_scan = 0 bo‘lgan indeksni o‘chirishdan oldin:

• barcha replikalarni tekshiring — statistika har bir serverda alohida yuritiladi;
• statistika oxirgi marta qachon tozalanganini aniqlang;
• birlamchi kalitlar va unikal cheklovlar indekslarini o‘chirmang;
• dublikatlarni qidiring: (a, b) yonida (a) bo‘yicha indeks odatda ortiqcha.

Rejalashtiruvchi yangi indeksdan haqiqatan foydalanayotganini doim EXPLAIN (ANALYZE, BUFFERS) orqali tasdiqlang.