Full-text Search — tsvector, tsquery, to_tsvector, plainto_tsquery

~25 daq · PostgreSQL · PostgreSQL Maxsus Imkoniyatlari

Muammodan boshlaylik

Saytda maqolalar bor. Foydalanuvchi qidiruv maydoniga "JavaScript async" yozdi. Siz LIKE bilan qidirasiz:

SELECT * FROM maqolalar
WHERE matn LIKE '%JavaScript async%';

Bu faqat aynan "JavaScript async" ketma-ketligini topadi. Lekin maqolada "async JavaScript dasturlash" yozilgan bo'lsa — topilmaydi. Yoki "javascript" kichik harfda yozilgan bo'lsa — topilmaydi.

ILIKE katta-kichik harf muammosini hal qiladi. Lekin so'zlar tartibi, turli shakllari (run/running/ran), keraksiz so'zlar (va, bilan, uchun) muammolari qoladi.

Bu muammolarni hal qilish uchun Full-text Search yaratilgan.

Full-text Search nima?

Full-text Search — matndan ma'noli so'zlarni ajratib, ularning asosiy shaklini (lemma) topib, keyin qidirish. LIKE matnni harf-harf solishtirsa, FTS so'zlarni tushunadi.

Hayotiy o'xshatma: kutubxonachi. Siz "kitoblar haqida" deb so'rasangiz, kutubxonachi "kitob", "kitoblar", "kitobxona" barchasini ko'rsatadi. LIKE esa faqat aynan "kitoblar haqida" matnini qidiradi.

LIKE '%async%':              Full-text Search:
──────────────────           ──────────────────────────────
"async" → topiladi           "async"     → topiladi
"Async" → topilmaydi         "Async"     → topiladi
"ASYNC" → topilmaydi         "ASYNC"     → topiladi
"asinxron" → topilmaydi      so'zlar munosabati tushuniladi
so'zlar tartibi muhim        so'zlar tartibi muhim emas

Ikki asosiy tushuncha

Full-text Search PostgreSQL da ikki tushunchaga asoslanadi:

tsvector — matnni indekslash uchun tayyorlangan format. Har bir so'z o'zining asosiy shaklida saqlanadi, qaysi pozitsiyada turganini biladi.

tsquery — qidiruv so'rovi formati. Qidirmoqchi bo'lgan so'zlar va ular orasidagi mantiqiy aloqa.

Matn:     "JavaScript da async funksiyalar ishlaydi"
tsvector: 'async':3 'funksiya':4 'javascript':1 'ishla':6
           ↑                      ↑
        pozitsiya                asosiy shakl (lemma)

"funksiyalar" → "funksiya", "ishlaydi" → "ishla" — so'zlar asosiy shaklga keltirildi. Keraksiz so'zlar ("da") o'chirildi.

Namuna jadval

CREATE TABLE maqolalar (
  id       SERIAL PRIMARY KEY,
  sarlavha TEXT,
  matn     TEXT
);

INSERT INTO maqolalar (sarlavha, matn) VALUES
  ('JavaScript Asoslari',    'JavaScript brauzerda ishlaydigan dasturlash tili. Funksiyalar va ob''ektlar bilan ishlash.'),
  ('Async Dasturlash',       'Asinxron dasturlash callback, promise va async await bilan amalga oshiriladi.'),
  ('PostgreSQL va Node.js',  'Node.js da PostgreSQL ga ulanish va so''rovlar yuborish usullari.'),
  ('React Komponentlar',     'React da funksional komponentlar va hooks bilan ishlash asoslari.');

to_tsvector — matnni indekslash

to_tsvector(til, matn) — matnni tsvector formatiga o'giradi:

SELECT to_tsvector('english', 'JavaScript da async funksiyalar ishlaydi');

'async':3 'function':4 'javascript':1 'work':6

Ikkinchi argument — til. Til bo'yicha stop-words (va, bilan, da) va stemming (so'z asosi) aniqlanadi.

Hozircha o'zbek tili uchun PostgreSQL da maxsus konfiguratsiya yo'q. Amalda ikkita yondashuv:

-- 1. English stemming — so'z asoslarini ajratadi
to_tsvector('english', matn)

-- 2. Simple — faqat kichik harfga o'giradi, stemming yo'q
to_tsvector('simple', matn)

O'zbek matni uchun ko'pincha 'simple' yaxshi — chunki inglizcha stemming o'zbek so'zlarini noto'g'ri kesib tashlashi mumkin.

to_tsquery va plainto_tsquery — qidiruv so'rovi

to_tsquery — AND (&), OR (|), NOT (!) operatorlari bilan aniq qidiruv:

SELECT to_tsquery('english', 'javascript & async');
-- 'async' & 'javascript'

SELECT to_tsquery('english', 'javascript | python');
-- 'javascript' | 'python'

SELECT to_tsquery('english', 'javascript & !python');
-- 'javascript' & !'python'

plainto_tsquery — oddiy matndan so'rov yaratadi, & avtomatik qo'yiladi:

SELECT plainto_tsquery('english', 'javascript async funksiyalar');
-- 'async' & 'function' & 'javascript'

Foydalanuvchi kiritgan matndan qidiruv yasash uchun plainto_tsquery qulay — foydalanuvchi & yoki | bilishi shart emas.

websearch_to_tsquery — Google kabi qidiruv sintaksisi:

SELECT websearch_to_tsquery('english', '"async await" OR promise');
-- 'async' <-> 'await' | 'promis'
-- ↑ tirnoq — so'zlar ketma-ket bo'lishi kerak

@@ — qidiruv operatori

tsvector @@ tsquery — tsvector ichida tsquery bor-yo'qligini tekshiradi:

SELECT sarlavha
FROM maqolalar
WHERE to_tsvector('simple', matn) @@ plainto_tsquery('simple', 'async');

     sarlavha
------------------
 Async Dasturlash

Bir necha so'z bilan:

SELECT sarlavha
FROM maqolalar
WHERE to_tsvector('simple', sarlavha || ' ' || matn)
   @@ plainto_tsquery('simple', 'javascript funksiya');

      sarlavha
--------------------
 JavaScript Asoslari
 React Komponentlar

sarlavha || ' ' || matn — sarlavha va matnni birlashtirdi. Ikkalasidan qidiradi.

Qidiruv reytingi — ts_rank

Natijalarni dolzarblik bo'yicha tartiblash uchun ts_rank:

SELECT
  sarlavha,
  ts_rank(
    to_tsvector('simple', matn),
    plainto_tsquery('simple', 'funksiya dasturlash')
  ) AS rang
FROM maqolalar
WHERE to_tsvector('simple', matn)
   @@ plainto_tsquery('simple', 'funksiya dasturlash')
ORDER BY rang DESC;

       sarlavha        │   rang
-----------------------+-----------
 JavaScript Asoslari   │ 0.0759906
 Async Dasturlash      │ 0.0759906
 React Komponentlar    │ 0.0607927

So'z ko'proq takrorlangan va muhimroq joyda (sarlavhada) bo'lsa — rang yuqori.

Qidiruv natijasini highlight qilish — ts_headline

Foydalanuvchiga qaysi qismda topilganini ko'rsatish:

SELECT
  sarlavha,
  ts_headline(
    'simple',
    matn,
    plainto_tsquery('simple', 'funksiya'),
    'MaxWords=15, MinWords=5, StartSel=<b>, StopSel=</b>'
  ) AS ko_rinish
FROM maqolalar
WHERE to_tsvector('simple', matn) @@ plainto_tsquery('simple', 'funksiya');

      sarlavha       │ ko'rinish
---------------------+------------------------------------------------
 JavaScript Asoslari │ JavaScript brauzerda ishlaydigan dasturlash tili. <b>Funksiyalar</b> va ob'ektlar...
 React Komponentlar  │ React da <b>funksional</b> komponentlar va hooks...

Topilgan so'z <b> teglari bilan o'ralgan — frontendda qalin shrift bilan ko'rsatish mumkin.

tsvector ustunni saqlash

Har so'rovda to_tsvector() chaqirish — matnni qayta-qayta o'girish. Katta jadvallarda sekin. Yaxshisi — tsvector ni alohida ustunda saqlash:

ALTER TABLE maqolalar ADD COLUMN search_vector tsvector;

UPDATE maqolalar
SET search_vector = to_tsvector('simple', sarlavha || ' ' || matn);

Trigger bilan avtomatik yangilansin:

CREATE FUNCTION maqolalar_search_update() RETURNS trigger AS $
BEGIN
  NEW.search_vector := to_tsvector('simple', NEW.sarlavha || ' ' || NEW.matn);
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER maqolalar_search_trigger
BEFORE INSERT OR UPDATE ON maqolalar
FOR EACH ROW EXECUTE FUNCTION maqolalar_search_update();

Endi qo'shilgan yoki yangilangan har bir qator uchun search_vector avtomatik hisoblanadi. Va so'rovda:

SELECT sarlavha
FROM maqolalar
WHERE search_vector @@ plainto_tsquery('simple', 'funksiya');
-- to_tsvector() yo'q — search_vector ustun to'g'ridan-to'g'ri ishlatildi

Tez-tez uchraydigan xatolar

Har so'rovda to_tsvector() chaqirish:

-- ❌ — har qatorda to_tsvector() chaqiriladi, index ishlamaydi
WHERE to_tsvector('simple', matn) @@ plainto_tsquery('simple', 'qidiruv');

-- ✅ — tsvector ustun saqlangan, index ishlaydi
WHERE search_vector @@ plainto_tsquery('simple', 'qidiruv');

to_tsvector() ni har so'rovda chaqirish indeks ishlamasligiga olib keladi. Saqlangan ustun + GIN index — tez.

Til parametrini bir xil ushlab qolmaslik:

-- ❌ — saqlashda 'english', qidirishda 'simple'
SET search_vector = to_tsvector('english', matn);
WHERE search_vector @@ plainto_tsquery('simple', 'qidiruv');
-- moslik yo'q — natijalar noto'g'ri

-- ✅ — har ikki joyda bir xil til
SET search_vector = to_tsvector('simple', matn);
WHERE search_vector @@ plainto_tsquery('simple', 'qidiruv');

Xulosa

Full-text Search — LIKE dan ancha aqlliroq qidiruv. So'z shakllarini tushunadi, stop-wordslarni o'chiradi, rang va highlight beradi.

to_tsvector      → matnni indekslash formatiga o'girish
plainto_tsquery  → oddiy matndan qidiruv so'rovi
to_tsquery       → AND/OR/NOT bilan aniq qidiruv
@@               → moslik tekshiruvi
ts_rank          → dolzarblik reytingi
ts_headline      → topilgan joyni ko'rsatish

Ishlab chiqarish uchun pattern:

1. tsvector ustun qo'shish
2. Trigger bilan avtomatik yangilanish
3. GIN index — keyingi darsda

Keyingi dars

26-dars: Full-text Search — GIN index, ranking, highlight