FULL-TEXT SEARCH

Ushbu qo'llanmada PostgreSQLning kuchli to'liq matnli qidiruv (Full-text search) mexanizmi bilan tanishasiz va uni ma'lumotlar bazasida qanday tatbiq qilishni o'rganasiz.

PostgreSQL to'liq matnli qidiruviga kirish

Shu paytgacha matn ichidan qidirish uchun quyidagi usullardan foydalandik:

1. Tenglik operatori (=) — aniq moslikni tekshirish uchun:
   query.sql

   Editorda sinashCopy

   SELECT * FROM table_name WHERE column_name = 'search_term';
   

2. LIKE yoki ILIKE operatorlari — qolip (shablon) bo'yicha moslik:
   query.sql

   Editorda sinashCopy

   SELECT * FROM table_name WHERE column_name LIKE '%term%';
   

3. Regulyar ifodalar (~) yoki SIMILAR TO operatori.

Biroq, bu usullar so'zlarning turli grammatik shakllarini (morfologiyasini) tushunmaydi va katta hajmdagi matnli maydonlarda indekslardan unumli foydalana olmaydi. Masalan, foydalanuvchi "advance" so'zini qidirganda, "advanced", "advances" yoki "advancing" so'zlarini ham topish talab etilsa, oddiy LIKE yetarli bo'lmaydi.

Bunday murakkab va yuqori unumdorlik talab qilinadigan vazifalar uchun PostgreSQL to'liq matnli qidiruv (Full-Text Search - FTS) tizimini taqdim etadi. FTS matnlarni so'zlarning o'zak shakllari (leksemalar) bo'yicha indekslaydi va ularning ahamiyati bo'yicha saralaydi (ranking).

To'liq matnli qidiruvda asosiy birlik hujjat (document) hisoblanadi. Hujjat bitta ustun yoki bir nechta jadvallar ustunlarining birlashmasi bo'lishi mumkin.

To'liq matnli qidiruv ma'lumot turlari

PostgreSQL to'liq matnli qidiruvni qo'llab-quvvatlash uchun ikkita maxsus ma'lumot turini taqdim etadi: tsvector va tsquery.

1) tsvector ma'lumot turi

tsvector (text search vector) — bu to'liq matnli qidiruv uchun optimallashtirilgan hujjat ko'rinishidir. U so'zlarning normalizatsiya qilingan va saralangan ro'yxatini (takrorlanishlarsiz) saqlaydi.

Normalizatsiya qilingan so'zlar leksemalar (lexemes) deb ataladi. Masalan, "teaches" va "teaching" so'zlarining o'zagi (leksemasi) "teach" bo'ladi.

Oddiy matnni tsvector ga aylantirish uchun to_tsvector funksiyasidan foydalaniladi:

SELECT
  to_tsvector('teaches'),
  to_tsvector('teaching');

Natija:

 to_tsvector | to_tsvector 
-------------+-------------
 'teach':1   | 'teach':1

Ikkala so'z ham bir xil 'teach' leksemasiga keltirildi.

tsvector quyidagi afzalliklarga ega:

• Leksemalar (stemming): so'zlarning qo'shimchalarini olib tashlab, asosiy shaklga keltiradi.
• Saralash va duplikatlarni tozalash: takrorlangan so'zlarni olib tashlaydi va alifbo tartibida joylashtiradi.
• O'rin (pozitsiya) ma'lumoti: har bir leksemaning matndagi aniq tartib raqamini saqlaydi (so'zlar orasidagi masofani hisoblash uchun).
• Vaznlar (weights): leksemalarga A, B, C, D kabi ustuvorlik vaznlarini belgilash imkonini beradi.

Keling, butun bir gapni tsvector ga aylantirib ko'ramiz:

SELECT
  to_tsvector(
    'The big blue elephant jumps over the lazy dog. The big blue elephant is very big'
  );

Natija:

                                to_tsvector                                
---------------------------------------------------------------------------
 'big':2,11,16 'blue':3,12 'dog':9 'eleph':4,13 'jump':5 'lazi':8

Bu yerda:

• "The", "over", "is" kabi to'xtatuvchi so'zlar (stop words) avtomatik filtrlab tashlangan.
• "jumps" -> 'jump', "elephant" -> 'eleph', "lazy" -> 'lazi' ga normalizatsiya qilingan.
• 'big' so'zi 2-, 11- va 16-o'rinlarda uchrashi ko'rsatilgan.

2) tsquery ma'lumot turi

tsquery (text search query) to'liq matnli qidiruv shartlarini ifodalash uchun ishlatiladi.

tsquery quyidagi mantiqiy operatorlarni qo'llab-quvvatlaydi:

• & — AND (va) operatori.
• | — OR (yoki) operatori.
• ! — NOT (inkor) operatori.
• <-> yoki qo'shtirnoqlar — ibora bo'yicha qidirish (phrase search), ya'ni so'zlar aynan ketma-ket kelishi kerak.
• Qavslar () — qidiruv shartlarini guruhlash.

Matnni tsquery ga aylantirish uchun to_tsquery funksiyasi ishlatiladi:

SELECT to_tsquery('"blue" & "elephant" | "dolphin"');

Natija:

          to_tsquery          
------------------------------
 'blue' & 'eleph' | 'dolphin'

3) Moslik operatori (@@)

PostgreSQLda tsvector va tsquery orasidagi moslikni tekshirish uchun @@ operatori ishlatiladi:

tsvector @@ tsquery

Masalan:

SELECT
  to_tsvector('The big blue elephant jumps over the lazy dog.') @@ to_tsquery('elephant') AS result;

Natija:

 result 
--------
 t

Natija t (true), ya'ni matnda qidirilgan so'z mavjud.

Agar chap tomonda oddiy matn (VARCHAR yoki TEXT) ko'rsatilsa, PostgreSQL uni avtomatik tarzda to_tsvector orqali o'giradi:

SELECT 'The big blue elephant jumps over the lazy dog.' @@ to_tsquery('elephant');

Amaliy misollar

To'liq matnli qidiruvni amalda sinash uchun products jadvalini yaratib, ma'lumotlar kiritamiz:

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    description TEXT
);

INSERT INTO products (product_name, description) VALUES
('Apple iPhone 15 Pro', 'Smartfon with powerful A17 Pro chip and sleek design, advanced camera features'),
('Samsung Galaxy S24 Ultra', 'Features large display, powerful battery and great performance'),
('Dell XPS 15', 'Laptop with high performance, sleek design and great display features'),
('Sony WH-1000XM5', 'Wireless headphones with great noise canceling and large battery life'),
('Apple MacBook Pro', 'Advanced laptop powered by M3 chip, sleek design for professional use');

0) GIN indeksini yaratish

To'liq matnli qidiruv katta ma'lumotlar bazasida chaqmoqdek tez ishlashi uchun GIN (Generalized Inverted Index) indeksidan foydalaniladi:

CREATE INDEX fts_products_description
ON products
USING GIN (to_tsvector('english', description));

1) Oddiy to'liq matnli qidiruv

Quyidagi so'rov tavsifida power o'zagi (masalan powerful) qatnashgan mahsulotlarni qidiradi:

SELECT
  product_name,
  description
FROM
  products
WHERE
  to_tsvector('english', description) @@ to_tsquery('power');

Natija:

       product_name       |                                 description                                  
--------------------------+------------------------------------------------------------------------------
 Apple iPhone 15 Pro      | Smartfon with powerful A17 Pro chip and sleek design, advanced camera features
 Samsung Galaxy S24 Ultra | Features large display, powerful battery and great performance

2) AND (&) operatori bilan qidirish

Quyidagi so'rov tavsifida ham power, ham large so'zlari mavjud bo'lgan mahsulotlarni topadi:

SELECT
  product_name,
  description
FROM
  products
WHERE
  to_tsvector('english', description) @@ to_tsquery('power & large');

Natija:

       product_name       |                          description                          
--------------------------+---------------------------------------------------------------
 Samsung Galaxy S24 Ultra | Features large display, powerful battery and great performance

3) OR (|) operatori bilan qidirish

Tavsifida large yoki great so'zlaridan biri uchragan mahsulotlarni tanlaymiz:

SELECT
  product_name,
  description
FROM
  products
WHERE
  to_tsvector('english', description) @@ to_tsquery('large | great');

4) Ibora (phrase) bo'yicha qidirish

Aynan ketma-ket kelgan "sleek design" iborasini qidirish uchun:

SELECT
  product_name,
  description
FROM
  products
WHERE
  to_tsvector('english', description) @@ phraseto_tsquery('english', 'sleek design');

Natija:

    product_name     |                                 description                                  
---------------------+------------------------------------------------------------------------------
 Apple iPhone 15 Pro | Smartfon with powerful A17 Pro chip and sleek design, advanced camera features
 Dell XPS 15         | Laptop with high performance, sleek design and great display features
 Apple MacBook Pro   | Advanced laptop powered by M3 chip, sleek design for professional use

5) NOT (!) operatori bilan qidirish

Tavsifida features so'zi qatnashmagan mahsulotlarni topamiz:

SELECT
  product_name,
  description
FROM
  products
WHERE
  to_tsvector('english', description) @@ to_tsquery('!features');

Natija:

  product_name   |                             description                             
-----------------+---------------------------------------------------------------------
 Sony WH-1000XM5 | Wireless headphones with great noise canceling and large battery life

Xulosa

• Full-Text Search (FTS) — matnlarni so'z o'zaklari (leksemalar) bo'yicha qidirish, so'z shakllarini hisobga olish va saralash imkonini beradi.
• tsvector hujjatlarni o'zaklarga ajratib, tahlil qilingan holatda saqlaydi.
• tsquery mantiqiy operatorlar (&, |, !) va iboralar yordamida qidiruv so'rovini ifodalaydi.
• @@ operatori orqali tsvector va tsquery mosligi tekshiriladi.
• Katta hajmdagi ma'lumotlarda qidiruv tezligini ta'minlash uchun GIN indekslaridan foydalaniladi.