PostgreSQL indeks turlari: GIN, GiST, BRIN, Hash va pg_trgm

Qisqacha: B-tree tartiblash mumkin bo'lgan qiymatlar uchun: =, <, >, BETWEEN, LIKE 'boshi%'. Matn ichidan qidiruv (ILIKE '%review%'), massiv va JSONB ichidagi element, kesishadigan vaqt oraliqlari uchun boshqa indekslar kerak. Kanonda 2 mln vazifada ILIKE '%review 1234%' Seq Scan bilan 529 ms, pg_trgm GIN indeksi bilan 13 ms. BRIN esa vaqt tartibida yozilgan katta jadval uchun: B-tree 43 MB, BRIN — 32 kB.

Bu darsda

• B-tree nimaga yaramasligini va LIKE 'boshi%' uchun nima kerakligini bilasiz.
• pg_trgm va GIN bilan ILIKE '%…%' qidiruvni tezlashtirasiz.
• Massiv (@>), JSONB (@>) va to'liq matnli qidiruv uchun GIN indeks yaratasiz.
• GiST bilan kesishmaydigan bronlarni bazada kafolatlaysiz.
• BRIN va Hash qachon foydali, qachon foydasizligini o'lchov bilan aytasiz.

Oldin bilishingiz kerak: Partial, expression va covering indekslar, Indeks nima: B-tree ichidan, PostgreSQL ma'lumot turlari, DB turlari xaritasi va SQL vs NoSQL.

1. Nega bu kerak?

vazifalar ilovasida qidiruv maydoni bor. Aziz "review" deb yozadi va "Kod review 1234 #uy" kabi vazifalarni kutadi. 25-qism API'si buni matn ichidan qidiradi — so'z boshida emas, istalgan joyida. SQL'da eng sodda yozilishi:

SELECT … FROM vazifalar WHERE matn ILIKE '%review 1234%';

B-tree bunga yordam bera olmaydi. B-tree — alifbo tartibidagi lug'at. "R harfidan boshlanadigan so'zlar" ni tez topadi. "Ichida «view» bor so'zlar" ni esa topa olmaydi — butun lug'atni varaqlash kerak.

Kanon bazasida (PostgreSQL 18.6, 2 mln vazifa; sxemaning 012-fayldagi holati, 59-indeks-turlari.txt) bu so'rovni butun jadval bo'yicha o'lchadi. Kanon EXPLAIN chiqishlarida bu darsda ham cost=… qavslari qisqartirilgan — raqamlar o'zgarmagan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM vazifalar WHERE matn ILIKE '%review 1234%';

Natija (reja qatorlari, qisqartirildi):

 Finalize Aggregate (actual time=525.088..529.300 rows=1.00 loops=1)
   ->  Gather (actual time=524.801..529.284 rows=3.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual time=521.368..521.370 rows=1.00 loops=3)
               ->  Parallel Seq Scan on vazifalar (actual time=236.727..521.344 rows=36.67 loops=3)
                     Filter: (matn ~~* '%review 1234%'::text)
                     Rows Removed by Filter: 666630
 Execution Time: 529.407 ms

Uch jarayon (loops=3 — asosiy jarayon va 2 yordamchi) jadvalni bo'lib o'qidi va 2 mln qatordan 110 tasini topdi. Yarim soniya — bitta qidiruv uchun. 10 kishi bir vaqtda qidirsa, server faqat shu bilan band.

Postgres'da B-tree'dan boshqa 5 xil indeks turi bor. Kanon pg_am katalogidan so'radi:

SELECT amname FROM pg_am WHERE amtype = 'i' ORDER BY 1;

Natija:

 amname 
--------
 brin
 btree
 gin
 gist
 hash
 spgist
(6 rows)

Har biri o'z turdagi savol uchun. Bu darsda ularni bittalab ko'ramiz.

2. B-tree chegarasi: LIKE 'boshi%'

Avval B-tree nima qila olishini aniqlaymiz. LIKE 'Vazifa 1234%' — boshi ma'lum. Lug'atda "Vazifa 1234" dan "Vazifa 1235" gacha bo'lgan bo'lakni o'qish yetadi. Lekin bitta shart bor.

Biz ishga tushirdik (PostgreSQL 17.6, Windows 11). 200 000 qatorli sinov jadvali, matn ustuni en-x-icu collation bilan — kanon bazasidagidek tilga bog'liq tartib:

CREATE TABLE t_tur (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  matn text COLLATE "en-x-icu",
  teglar text[],
  qoshimcha jsonb
);
INSERT INTO t_tur (matn, teglar, qoshimcha)
SELECT 'Vazifa ' || g,
       CASE g % 4 WHEN 0 THEN ARRAY['ish', 'uy']
                  WHEN 1 THEN ARRAY['bozor']
                  WHEN 2 THEN ARRAY['oqish', 'ish']
                  ELSE ARRAY['sport'] END,
       jsonb_build_object(
         'manba', (ARRAY['telegram', 'veb', 'mobil'])[1 + g % 3],
         'baho', g % 5)
FROM generate_series(1, 200000) g;
CREATE INDEX t_tur_matn ON t_tur (matn);
VACUUM ANALYZE t_tur;

Natija:

CREATE TABLE
INSERT 0 200000
CREATE INDEX
VACUUM

Endi prefiks bilan qidiramiz — avval oddiy indeks bilan, keyin text_pattern_ops bilan:

EXPLAIN (COSTS OFF)
SELECT count(*) FROM t_tur WHERE matn LIKE 'Vazifa 1234%';

CREATE INDEX t_tur_matn_pat ON t_tur (matn text_pattern_ops);
EXPLAIN (COSTS OFF)
SELECT count(*) FROM t_tur WHERE matn LIKE 'Vazifa 1234%';

Natija:

                         QUERY PLAN                         
------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Seq Scan on t_tur
                     Filter: (matn ~~ 'Vazifa 1234%'::text)
(6 rows)

CREATE INDEX
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Aggregate
   ->  Index Only Scan using t_tur_matn_pat on t_tur
         Index Cond: ((matn ~>=~ 'Vazifa 1234'::text) AND (matn ~<~ 'Vazifa 1235'::text))
         Filter: (matn ~~ 'Vazifa 1234%'::text)
(4 rows)

Oddiy indeks ishlatilmadi. Sabab — collation (taqqoslash qoidasi). ORDER BY darsida ko'rgandik: til qoidalari bo'yicha tartiblashda katta-kichik harf, belgilar murakkab qoida bilan taqqoslanadi. Bunday tartibda "Vazifa 1234" bilan boshlanadiganlar bitta bo'lakda yotishiga kafolat yo'q. text_pattern_ops — belgilarni bayt-bayt taqqoslaydigan operator sinfi. Shunday indeksda prefiks bo'lagi aniq: Index Cond da ~>=~ 'Vazifa 1234' va ~<~ 'Vazifa 1235' — "1234 dan 1235 gacha".

Maslahat: Baza C collation bilan yaratilgan bo'lsa, oddiy B-tree ham LIKE 'boshi%' ga yaraydi. Kanon bazasi en_US.utf8 da — u yerda text_pattern_ops kerak. O'z bazangiz collation'ini \l bilan ko'ring.

Lekin ILIKE '%…%' — boshi noma'lum. Unga hech qanday B-tree yordam bermaydi.

3. pg_trgm va GIN: matn ichidan qidiruv

3.1 Trigramma nima

Trigramma (trigram) — matndagi ketma-ket 3 belgilik bo'laklar. pg_trgm kengaytmasi matnni so'zlarga ajratadi, har so'zning boshiga ikki, oxiriga bitta bo'sh joy qo'shadi va bo'laklarni oladi. Biz ishga tushirdik:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT show_trgm('osh');
SELECT similarity('Toshkent', 'Toshknet') AS oxshashlik,
       'Toshkent' % 'Toshknet' AS yaqinmi;

Natija:

CREATE EXTENSION
        show_trgm        
-------------------------
 {"  o"," os",osh,"sh "}
(1 row)

 oxshashlik | yaqinmi 
------------+---------
  0.3846154 | t
(1 row)

"osh" so'zi 4 ta trigrammaga bo'lindi. Kanon show_trgm('Kod') → {" k"," ko",kod,"od "} oldi (kichik harfga o'tkaziladi).

similarity — ikki matnning umumiy trigrammalar ulushi (0 dan 1 gacha). % operatori — o'xshashlik chegaradan (sukut 0.3) yuqorimi. "Toshknet" — xato terilgan "Toshkent", lekin 0.38 o'xshash. Shu bilan "Siz buni nazarda tutdingizmi?" xizmati quriladi.

Qidiruvda g'oya bunday: '%review 1234%' ham trigrammalarga bo'linadi. Indeks har trigramma qaysi qatorlarda borligini biladi. Hammasi bor qatorlar — nomzodlar. Keyin baza ularni haqiqiy ILIKE bilan qayta tekshiradi.

3.2 GIN indeksi

GIN (Generalized Inverted Index — umumlashgan teskari indeks) — "qiymat ichidagi har bir element → u bor qatorlar ro'yxati" xaritasi. Kitob oxiridagi alifboli ko'rsatkich kabi: "Toshkent — 12, 45, 78-betlar". Bitta qator ko'p elementga ega (matnda ko'p trigramma, massivda ko'p teg) — GIN aynan shunday ma'lumot uchun.

Kanon trigramma GIN indeksini qurdi:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX t59_trgm ON vazifalar USING gin (matn gin_trgm_ops);

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM vazifalar WHERE matn ILIKE '%review 1234%';

Natija:

CREATE EXTENSION
Time: 19.803 ms
CREATE INDEX
Time: 10488.098 ms (00:10.488)
 Aggregate (actual time=13.243..13.244 rows=1.00 loops=1)
   Buffers: shared hit=672 read=37
   I/O Timings: shared read=0.428
   ->  Bitmap Heap Scan on vazifalar (actual time=12.619..13.226 rows=110.00 loops=1)
         Recheck Cond: (matn ~~* '%review 1234%'::text)
         Rows Removed by Index Recheck: 2
         Heap Blocks: exact=43
         Buffers: shared hit=672 read=37
         I/O Timings: shared read=0.428
         ->  Bitmap Index Scan on t59_trgm (actual time=12.376..12.376 rows=112.00 loops=1)
               Index Cond: (matn ~~* '%review 1234%'::text)
               Index Searches: 1
               Buffers: shared hit=666
 Planning:
   Buffers: shared hit=28
 Planning Time: 0.293 ms
 Execution Time: 13.305 ms
(17 rows)

Rejani o'qiymiz:

• Bitmap Index Scan on t59_trgm — indeks 112 ta nomzod qator berdi.
• Rows Removed by Index Recheck: 2 — ikkitasi tekshiruvdan o'tmadi. Trigrammalar hammasi bor, lekin boshqa tartibda (masalan, "1234" va "review" alohida joyda). Shuning uchun GIN "taxminiy" — Recheck majburiy.
• 110 qator, 43 jadval sahifasi, 13.3 ms.

40 marta tez. Narxi: indeks 79 MB (jadval 309 MB) va 10.5 soniya qurildi. GIN yozishni ham sekinlashtiradi: har yangi matnning hamma trigrammalari indeksga tushadi. Postgres buni yumshatish uchun yangi yozuvlarni avval "kutish ro'yxati" ga (pending list) yig'adi va keyin to'plab qo'shadi.

Diqqat: Trigramma 3 belgidan iborat. 1–2 harfli qidiruv ('%ab%') uchun indeks deyarli yordam bermaydi — hamma qatorda bunday bo'lak bo'lishi mumkin. Ilovada qidiruvni kamida 3 belgidan boshlash odatiy qoida.

Tekshirib ko'ring: Nega pg_trgm ILIKE (katta-kichik harfga befarq) ga ham, LIKE ga ham yaraydi?

4. GIN: massiv, JSONB va to'liq matnli qidiruv

4.1 Massiv va JSONB

Ma'lumot turlari darsida massiv va jsonb ni ko'rgandik. Ularning "ichida X bormi?" savoli — @> (o'z ichiga oladi) operatori. Bu ham GIN ishi. Biz ishga tushirdik (o'sha t_tur jadvali):

CREATE INDEX t_tur_teglar ON t_tur USING gin (teglar);
CREATE INDEX t_tur_qosh ON t_tur
  USING gin (qoshimcha jsonb_path_ops);
ANALYZE t_tur;

EXPLAIN (COSTS OFF)
SELECT count(*) FROM t_tur WHERE teglar @> ARRAY['sport'];
EXPLAIN (COSTS OFF)
SELECT count(*) FROM t_tur
WHERE qoshimcha @> '{"manba": "telegram", "baho": 4}';
EXPLAIN (COSTS OFF)
SELECT count(*) FROM t_tur
WHERE qoshimcha->>'manba' = 'telegram';

Natija:

CREATE INDEX
CREATE INDEX
ANALYZE
                       QUERY PLAN                        
---------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on t_tur
         Recheck Cond: (teglar @> '{sport}'::text[])
         ->  Bitmap Index Scan on t_tur_teglar
               Index Cond: (teglar @> '{sport}'::text[])
(5 rows)

                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on t_tur
         Recheck Cond: (qoshimcha @> '{"baho": 4, "manba": "telegram"}'::jsonb)
         ->  Bitmap Index Scan on t_tur_qosh
               Index Cond: (qoshimcha @> '{"baho": 4, "manba": "telegram"}'::jsonb)
(5 rows)

                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Seq Scan on t_tur
                     Filter: ((qoshimcha ->> 'manba'::text) = 'telegram'::text)
(6 rows)

Uch xulosa:

1. Massiv ichidan teg qidirish (@>) — GIN orqali.
2. JSONB ichidan "shunday juftliklar bor hujjat" (@>) — GIN orqali. jsonb_path_ops — faqat @> ni qo'llaydigan, lekin ixchamroq variant. Sukut (jsonb_ops) kalit bormi (?) savollarini ham qo'llaydi.
3. ->>'manba' = … — GIN ishlatilmadi! GIN @> operatorini biladi, ->> + = ni emas. Bunday so'rov uchun ifoda indeksi kerak: CREATE INDEX … ((qoshimcha->>'manba')). Yoki so'rovni @> ga qayta yozing.

Hajmi (biz ishga tushirdik):

SELECT pg_size_pretty(pg_relation_size('t_tur_teglar')) AS gin_massiv,
       pg_size_pretty(pg_relation_size('t_tur_qosh')) AS gin_jsonb,
       pg_size_pretty(pg_relation_size('t_tur_matn')) AS btree_matn;

Natija:

 gin_massiv | gin_jsonb | btree_matn 
------------+-----------+------------
 392 kB     | 536 kB    | 6184 kB
(1 row)

Massiv GIN — 392 kB, B-tree esa 6 MB. Sababi: bu yerda turli qiymatlar kam (5 ta teg, 8 ta JSON juftligi), GIN har biri uchun bitta ixcham ro'yxat saqlaydi. Har qatorda turli qiymat bo'lsa (trigrammalar), GIN katta bo'ladi.

JSONB'ning to'liq imkoniyatlari — 27-qismdagi JSONB darsida.

4.2 To'liq matnli qidiruv (FTS)

To'liq matnli qidiruv (full-text search, FTS) — matnni so'zlarga ajratib, "shu so'zlar bor hujjatlar" ni topish. DB turlari darsida qisqa ko'rgandik. Kanon GIN bilan o'lchadi:

CREATE INDEX t59_fts ON vazifalar
  USING gin (to_tsvector('simple', matn));

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM vazifalar
WHERE to_tsvector('simple', matn)
      @@ to_tsquery('simple', 'hisobot & 1234');

Natija (qisqartirildi):

CREATE INDEX
Time: 11172.886 ms (00:11.173)
 Aggregate (actual time=0.465..0.467 rows=1.00 loops=1)
   ->  Bitmap Heap Scan on vazifalar (actual time=0.458..0.460 rows=1.00 loops=1)
         Recheck Cond: (to_tsvector('simple'::regconfig, matn) @@ '''hisobot'' & ''1234'''::tsquery)
         ->  Bitmap Index Scan on t59_fts (actual time=0.184..0.185 rows=1.00 loops=1)
               Index Cond: (to_tsvector('simple'::regconfig, matn) @@ '''hisobot'' & ''1234'''::tsquery)
 Execution Time: 0.538 ms

0.54 ms. Bu ham ifoda indeksi: to_tsvector('simple', matn). 'simple' — lug'atsiz konfiguratsiya: so'zlarni faqat kichik harfga o'tkazadi. O'zbek tili uchun Postgres'da tayyor lug'at yo'q — "kitoblar" va "kitob" boshqa so'z hisoblanadi. pg_trgm esa so'z qismini ham topadi. Shuning uchun o'zbekcha ilovalarda ko'pincha pg_trgm tanlanadi.

5. GiST: oraliqlar va "kesishmasin" qoidasi

GiST (Generalized Search Tree — umumlashgan qidiruv daraxti) — "bir-birini qoplaydimi, kesishadimi, yaqinmi" savollari uchun. Qiymatlar to'g'ri chiziqdagi nuqtalar emas, sohalar: vaqt oraliqlari, xaritadagi joylar, geometrik shakllar.

Race condition darsida xonani bron qilish masalasi bor edi: bir xonaga kesishadigan vaqtda ikki bron bo'lmasin. Buni bazada exclusion constraint (istisno cheklovi) bilan, GiST ustida kafolatlash mumkin. Biz ishga tushirdik (PostgreSQL 17.6, sessiya zonasi UTC — DETAIL dagi vaqtlar UTC'da: 11:30+05 = 06:30+00):

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE xona_bron (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  xona int NOT NULL,
  vaqt tstzrange NOT NULL,
  EXCLUDE USING gist (xona WITH =, vaqt WITH &&)
);
INSERT INTO xona_bron (xona, vaqt) VALUES
  (1, '[2026-10-25 10:00+05, 2026-10-25 12:00+05)'),
  (1, '[2026-10-25 12:00+05, 2026-10-25 14:00+05)');
INSERT INTO xona_bron (xona, vaqt) VALUES
  (1, '[2026-10-25 11:30+05, 2026-10-25 12:30+05)');
DROP TABLE xona_bron;

Natija:

CREATE EXTENSION
CREATE TABLE
INSERT 0 2
ERROR:  conflicting key value violates exclusion constraint "xona_bron_xona_vaqt_excl"
DETAIL:  Key (xona, vaqt)=(1, ["2026-10-25 06:30:00+00","2026-10-25 07:30:00+00")) conflicts with existing key (xona, vaqt)=(1, ["2026-10-25 05:00:00+00","2026-10-25 07:00:00+00")).
DROP TABLE

Qatorma-qator:

• tstzrange — vaqt oralig'i turi. [ — boshi kiradi, ) — oxiri kirmaydi. Shuning uchun 10:00–12:00 va 12:00–14:00 kesishmaydi: ikkalasi qabul qilindi.
• EXCLUDE USING gist (xona WITH =, vaqt WITH &&) — "bir xil xona (=) va kesishadigan vaqt (&&) bo'lgan ikki qator bo'lmasin".
• btree_gist — oddiy = ni GiST ichida ishlatish uchun kengaytma.
• 11:30–12:30 ikkalasi bilan kesishadi — rad etildi. Xato: "conflicting key value violates exclusion constraint" — "to'qnashuvchi kalit qiymati istisno cheklovini buzadi". DETAIL qaysi bron bilan to'qnashganini aytadi.

Kanon race condition tajribasida parallel bronlarda ham ikkinchisi rad etilgan: bir seansda 23P01 (exclusion), boshqasida 40P01 (deadlock) — ilova ikkalasini ham "band" deb tushunishi kerak.

GiST yana: geografik nuqtalar (PostGIS — kanon image'ida yo'q, sinalmagan), pg_trgm uchun ham GiST varianti bor (gist_trgm_ops — o'xshashlik bo'yicha "eng yaqin 10 ta" uchun). SP-GiST — teng bo'lmagan bo'laklarga ajraladigan ma'lumot uchun (IP manzillar, telefon prefikslari); bu darsda sinalmagan, hujjat bo'yicha.

6. BRIN: katta jurnal uchun mitti indeks

6.1 G'oya

BRIN (Block Range Index — bloklar oralig'i indeksi) har qatorni emas, sahifalar guruhini eslaydi: "128 ta sahifadan iborat shu bo'lakda yaratilgan_vaqt 1-mart 08:00 dan 1-mart 19:00 gacha". Qidiruvda baza mos kelmaydigan bo'laklarni butunlay o'tkazib yuboradi.

O'xshatish: arxivdagi papkalar. Har papka ustida "2025-yil mart" deb yozilgan. Ichidagi har hujjatning ro'yxati yo'q, lekin aprel hujjatini mart papkasidan qidirmaysiz. Bu faqat papkalar tartib bilan terilgan bo'lsa ishlaydi.

6.2 Kanon o'lchovi

CREATE INDEX t59_brin ON vazifalar USING brin (yaratilgan_vaqt);
CREATE INDEX t59_btree_vaqt ON vazifalar (yaratilgan_vaqt);
SELECT indexrelid::regclass AS indeks,
       pg_size_pretty(pg_relation_size(indexrelid)) AS hajm
FROM pg_index
WHERE indexrelid IN ('t59_brin'::regclass,
                     't59_btree_vaqt'::regclass,
                     't59_trgm'::regclass);

Natija:

CREATE INDEX
Time: 225.234 ms
CREATE INDEX
Time: 897.928 ms
     indeks     | hajm  
----------------+-------
 t59_trgm       | 79 MB
 t59_brin       | 32 kB
 t59_btree_vaqt | 43 MB
(3 rows)

BRIN 32 kB — B-tree'dan ~1 400 marta kichik. Endi bir kunlik oraliq: B-tree bilan 1.76 ms. B-tree o'chirilgandan keyin, faqat BRIN bilan:

DROP INDEX t59_btree_vaqt;
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM vazifalar
WHERE yaratilgan_vaqt >= '2025-03-01'
  AND yaratilgan_vaqt < '2025-03-02';

Natija (reja qatorlari):

 Aggregate (actual time=85.926..85.928 rows=1.00 loops=1)
   ->  Bitmap Heap Scan on vazifalar (actual time=2.483..85.613 rows=2879.00 loops=1)
         Recheck Cond: ((yaratilgan_vaqt >= '2025-03-01 00:00:00+00'::timestamp with time zone) AND (yaratilgan_vaqt < '2025-03-02 00:00:00+00'::timestamp with time zone))
         Rows Removed by Index Recheck: 454232
         Heap Blocks: lossy=7493
         ->  Bitmap Index Scan on t59_brin (actual time=0.173..0.173 rows=74930.00 loops=1)
 Execution Time: 85.961 ms

86 ms — B-tree'dan 49 marta sekin. Heap Blocks: lossy=7493 — BRIN 7 493 sahifani "bu yerda bo'lishi mumkin" dedi. Baza ularning hammasini o'qib, 454 232 qatorni tashladi. Kerakli 2 879 qator 7 493 sahifaga sochilgan edi.

6.3 Topilma: tartib buzilgan

Nega shunday? Kanon pg_stats dan correlation ni so'radi — ustun qiymatlari tartibi jadvaldagi jismoniy tartibga qanchalik mos (1 — to'liq mos, 0 — tartibsiz):

SELECT correlation FROM pg_stats
WHERE tablename = 'vazifalar'
  AND attname IN ('yaratilgan_vaqt', 'muddat', 'egasi_id')
ORDER BY attname;

Natija:

  correlation  
---------------
 -0.0039972365
     0.6196889
     0.6201849
(3 rows)

Qatorlar attname bo'yicha: egasi_id (−0.004), muddat 0.62-bob, yaratilgan_vaqt 0.62-bob. Vazifalar vaqt tartibida yozilgan, lekin correlation atigi 0.62. Sabab — katta to'plam yasalgach, izohlar_soni ustuni 442 356 qatorda UPDATE qilindi. MVCC da UPDATE yangi versiyani boshqa joyga yozadi — qatorlar jadval bo'ylab ko'chib ketdi. BRIN'ning har bo'lagi endi keng vaqt oralig'ini qoplaydi.

Xulosa: BRIN faqat qo'shiladigan, o'zgarmaydigan jadvallar uchun — jurnal, hodisalar, sensor o'lchovlari, to'lov tarixi. Tez-tez UPDATE bo'ladigan vazifalar uchun — yo'q. 3-mashqda buni o'zingiz ko'rasiz.

7. Hash: faqat tenglik

Hash indeks — qiymatning hash'ini saqlaydi (hash jadval g'oyasi). Faqat = ga yaraydi: diapazon, saralash, prefiks — yo'q. Kanon matn ustunida:

CREATE INDEX t59_hash ON vazifalar USING hash (matn);
SELECT pg_size_pretty(pg_relation_size('t59_hash')) AS hash_hajmi;
EXPLAIN (ANALYZE)
SELECT id FROM vazifalar WHERE matn = 'Kod review 1234 #uy';

Natija (qisqartirildi):

CREATE INDEX
Time: 4409.618 ms (00:04.410)
 hash_hajmi 
------------
 64 MB
(1 row)

 Index Scan using t59_hash on vazifalar (actual time=0.010..0.010 rows=0.00 loops=1)
   Index Cond: (matn = 'Kod review 1234 #uy'::text)
 Execution Time: 0.023 ms

Uzun matn uchun hash ixchamroq: B-tree darsida xuddi shu matn ustunining B-tree indeksi 94 MB edi, hash — 64 MB. Chunki hash satrning o'zini emas, 4 baytli hash qiymatini saqlaydi.

Kamchiliklari: UNIQUE bo'la olmaydi, tarkibiy bo'la olmaydi, ORDER BY ga yordam bermaydi. Shuning uchun amalda kam ishlatiladi: B-tree deyarli hamma joyda yetarli. PostgreSQL 10 gacha hash indeks qulashdan keyin tiklanmasdi (WAL'ga yozilmasdi) — eski maqolalarda "ishlatmang" degan maslahat shundan.

8. Qaysi birini tanlash

flowchart TD
  A["Savol qanday?"] --> B["=, <, >, BETWEEN,<br/>ORDER BY, LIKE 'boshi%'"]
  A --> C["Matn ichidan:<br/>ILIKE '%...%'"]
  A --> D["Ichida bormi:<br/>massiv, jsonb @>, FTS"]
  A --> E["Kesishadimi, yaqinmi:<br/>oraliq, joy"]
  A --> F["Juda katta jadval,<br/>vaqt tartibida yoziladi"]
  B --> B1["B-tree"]
  C --> C1["GIN + pg_trgm"]
  D --> D1["GIN"]
  E --> E1["GiST"]
  F --> F1["BRIN"]

Diagrammaga savol bilan kiring, indeks turi bilan chiqing. Hash'ni chizmadik — u B-tree'ning tor varianti, kamdan-kam kerak.

Tekshirib ko'ring: "Oxirgi 1 soatda to'lov tizimidan kelgan hodisalar" — jadvalga sekundiga 2 000 qator qo'shiladi, hech qachon o'zgartirilmaydi, 3 oyda 15 mlrd qator. Qaysi indeks?

9. Node qadami: kanon tajribasi, loyihaga kirmaydi

25-qism API'sining ro'yxat endpoint'ida qidiruv parametri bor (?qidiruv=…). PgOmbori uni ILIKE bilan emas, strpos bilan yozadi (Parametrli so'rovlar va SQL injection darsida qabul qilingan; kanondan, qatorlarga bo'lingan):

// LIKE emas: % va _ joker bo'lib qolmasin (#42).
// strpos — oddiy "ichida bormi"
if (filtr.qidiruv !== undefined)
  s.push(`strpos(lower(v.matn), lower(${p.qosh(filtr.qidiruv)})) > 0`);

Sabab: foydalanuvchi 50% deb qidirsa, ILIKE uchun % — "istalgan matn" belgisi bo'lib qolardi. strpos(a, b) esa b ning a ichidagi o'rnini qaytaradi (topilmasa 0) — hech qanday maxsus belgi yo'q.

Trigramma indeksi loyihaga qo'shilmadi. Uch sabab:

1. So'rov allaqachon tor. API har doim bitta egasining vazifalarini qidiradi (v.egasi_id = $1). Kanon ombor yuborgan haqiqiy SQL'ni katta bazada (013/014 bilan qayta qurilgan; eng ko'p vazifali egasi — 479 ta) ko'rdi:

=== qidiruv='review 1': royxat() 3.4 ms, so'rovlar 2
Limit (actual time=0.042..0.370 rows=21.00 loops=1)
  Buffers: shared hit=347
  ->  Index Scan using vazifalar_tartib_idx on vazifalar v (actual time=0.041..0.367 rows=21.00 loops=1)
        Rows Removed by Filter: 323
        Buffers: shared hit=347
  Buffers: shared hit=1
Execution Time: 0.382 ms

Nima bo'ldi? Postgres egasining vazifalarini vazifalar_tartib_idx bo'yicha tartib bilan yurdi va strpos ni oddiy filtr sifatida tekshirdi: 323 tasi mos kelmadi, 21 tasi mos keldi — 0.38 ms. 529 ms emas, chunki 2 mln emas, ~500 qator ko'rildi.

2. strpos ga GIN yordam bermaydi. gin_trgm_ops LIKE/ILIKE va o'xshashlik operatorlarini taniydi, strpos funksiyasini emas (pg_trgm hujjatiga ko'ra). Indeks kerak bo'lsa, so'rovni ILIKE ga qaytarib, % va _ ni ekranlash kerak bo'lardi.
3. Narxi. 79 MB va har yozuvda trigrammalarni yangilash — kichik hajm uchun ortiqcha.

Qachon kerak bo'ladi: qidiruv hamma foydalanuvchilar bo'yicha (admin panel) yoki bitta egasida o'n minglab vazifa bo'lsa. Bu holatni 2-mashqda o'zingiz sinab ko'rasiz.

10. Ko'p uchraydigan xatolar

gin_trgm_ops xatosini biz ishga tushirdik (PostgreSQL 17.6, pg_trgm o'rnatilmagan baza): "gin uchun «gin_trgm_ops» operator sinfi mavjud emas". Operator sinfi kengaytma bilan birga keladi — har bazada CREATE EXTENSION alohida bajariladi.

11. Mashqlar

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

1-mashq (oson): moslang

Har so'rovga indeks turini yozing: WHERE id = 5 — [:B-tree]; WHERE matn ILIKE '%non%' — [:GIN]; WHERE teglar @> ARRAY['uy'] — [:GIN]; EXCLUDE (vaqt WITH &&) — [:GiST]; o'zgarmaydigan 1 mlrd qatorli jurnalda WHERE vaqt > now() - interval '1 hour' — .

2-mashq (o'rta): qidiruvni tezlashtiring

200 000 qatorli jadval yarating (to'rt xil matn + raqam), ILIKE '%review 1234%' rejasini oling, pg_trgm GIN indeksini qo'ying va rejani qayta oling. Oxirida '%REVIEW 1234%' (katta harflar) bilan sanang — natija o'zgarmasligini tekshiring.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE t59_qidiruv AS
SELECT g AS id,
       (ARRAY['Bozordan non olish', 'Hisobot yozish',
              'Kod review', 'Darsga tayyorlanish'])[1 + g % 4]
         || ' ' || g AS matn
FROM generate_series(1, 200000) g;
ANALYZE t59_qidiruv;

EXPLAIN (COSTS OFF)
SELECT count(*) FROM t59_qidiruv WHERE matn ILIKE '%review 1234%';

CREATE INDEX t59_qidiruv_trgm
  ON t59_qidiruv USING gin (matn gin_trgm_ops);

EXPLAIN (COSTS OFF)
SELECT count(*) FROM t59_qidiruv WHERE matn ILIKE '%review 1234%';

SELECT count(*) FROM t59_qidiruv WHERE matn ILIKE '%REVIEW 1234%';
DROP TABLE t59_qidiruv;

                    QUERY PLAN                    
--------------------------------------------------
 Aggregate
   ->  Seq Scan on t59_qidiruv
         Filter: (matn ~~* '%review 1234%'::text)
(3 rows)

                         QUERY PLAN                         
------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on t59_qidiruv
         Recheck Cond: (matn ~~* '%review 1234%'::text)
         ->  Bitmap Index Scan on t59_qidiruv_trgm
               Index Cond: (matn ~~* '%review 1234%'::text)
(5 rows)

 count 
-------
    28
(1 row)

3-mashq (qiyin): BRIN qachon yaraydi

500 000 qatorli "jurnal" yarating: vaqt har 10 soniyada oshadi. BRIN va B-tree indekslarini qurib, BRIN kamida 100 marta kichikligini va correlation 0.99 dan yuqoriligini mantiqiy qiymat bilan chiqaring. B-tree'ni o'chirib, bir soatlik oraliq rejasida BRIN ishlatilishini ko'rsating. Keyin xuddi shu qatorlarni aralash tartibda (ORDER BY md5(id::text)) yangi jadvalga yozib, correlation 0 ga yaqinligini ko'rsating.

CREATE TABLE t59_jurnal AS
SELECT g AS id,
       timestamptz '2026-01-01 00:00+00' + g * interval '10 seconds'
         AS vaqt,
       'hodisa ' || g AS matn
FROM generate_series(1, 500000) g;

CREATE INDEX t59_jurnal_brin ON t59_jurnal USING brin (vaqt);
CREATE INDEX t59_jurnal_btree ON t59_jurnal (vaqt);
ANALYZE t59_jurnal;

SELECT pg_relation_size('t59_jurnal_brin') * 100
         < pg_relation_size('t59_jurnal_btree')
         AS brin_100_marta_kichik,
       (SELECT correlation > 0.99 FROM pg_stats
        WHERE tablename = 't59_jurnal' AND attname = 'vaqt')
         AS tartibli;

DROP INDEX t59_jurnal_btree;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM t59_jurnal
WHERE vaqt >= '2026-01-10' AND vaqt < '2026-01-10 01:00';

CREATE TABLE t59_aralash AS
SELECT * FROM t59_jurnal ORDER BY md5(id::text);
ANALYZE t59_aralash;
SELECT abs(correlation) < 0.1 AS tartibsiz
FROM pg_stats WHERE tablename = 't59_aralash' AND attname = 'vaqt';
DROP TABLE t59_jurnal, t59_aralash;

 brin_100_marta_kichik | tartibli 
-----------------------+----------
 t                     | t
(1 row)

                                                                        QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on t59_jurnal
         Recheck Cond: ((vaqt >= '2026-01-10 00:00:00+00'::timestamp with time zone) AND (vaqt < '2026-01-10 01:00:00+00'::timestamp with time zone))
         ->  Bitmap Index Scan on t59_jurnal_brin
               Index Cond: ((vaqt >= '2026-01-10 00:00:00+00'::timestamp with time zone) AND (vaqt < '2026-01-10 01:00:00+00'::timestamp with time zone))
(5 rows)

 tartibsiz 
-----------
 t
(1 row)

12. Real ishda

• Qidiruv maydoni — o'rta hajmdagi loyihalarda alohida qidiruv serveri (Elasticsearch, Meilisearch) o'rniga pg_trgm + GIN yetarli. Million qatorgacha bu ko'p kompaniyalarning birinchi tanlovi.
• Bron tizimlari (mehmonxona, shifokor navbati, taksi) — EXCLUDE USING gist bilan "ikki marta sotish" bazada imkonsiz.
• Jurnal va telemetriya jadvallari — BRIN yoki vaqt bo'yicha bo'linish (partitioning, 27-qism).
• JSONB — "moslashuvchan maydonlar" uchun GIN (@>); tez-tez so'raladigan kalit uchun ifoda indeksi.
• Intervyuda: "LIKE '%x%' ni qanday tezlashtirasiz?", "GIN va GiST farqi?", "BRIN qachon ishlamaydi?"

Xulosa

• B-tree: =, <, >, saralash va LIKE 'boshi%' (en_US'da text_pattern_ops bilan).
• GIN — "ichida bormi" savollari: pg_trgm (ILIKE '%…%', kanonda 529 → 13 ms), massiv va JSONB @>, FTS.
• GiST — kesishish va yaqinlik: EXCLUDE USING gist bilan kesishmaydigan bronlar.
• BRIN — tartibli, o'zgarmaydigan katta jadval: 32 kB vs 43 MB; tartib buzilsa (kanonda correlation 0.62) foydasi yo'qoladi.
• Hash — faqat =, uzun matnda ixcham (64 vs 94 MB), lekin kamdan-kam kerak.

Keyingi dars: EXPLAIN ANALYZE'ni o'qish — shu paytgacha ko'rgan rejalarimizni oxirigacha o'qiymiz: cost, rows, loops, Buffers, uch xil JOIN algoritmi va diskka tushgan saralash.

Manbalar

• PostgreSQL 18 hujjati — Index Types: https://www.postgresql.org/docs/18/indexes-types.html
• PostgreSQL 18 hujjati — pg_trgm: https://www.postgresql.org/docs/18/pgtrgm.html
• PostgreSQL 18 hujjati — GIN Indexes (pending list), BRIN Indexes: https://www.postgresql.org/docs/18/gin.html, https://www.postgresql.org/docs/18/brin.html
• PostgreSQL 18 hujjati — Exclusion Constraints va btree_gist: https://www.postgresql.org/docs/18/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION, https://www.postgresql.org/docs/18/btree-gist.html
• PostgreSQL 18 hujjati — Operator Classes (text_pattern_ops): https://www.postgresql.org/docs/18/indexes-opclass.html
• IlmHamroh kanon o'lchovi (PostgreSQL 18.6, 2 mln vazifa, 2026-10-24); text_pattern_ops, massiv/JSONB GIN, trigramma, GiST bron va mashqlar — biz ishga tushirdik (PostgreSQL 17.6, Windows 11, i5-12500H).