To'liq matn qidiruv

Matn ichidan qidirish - deyarli har bir loyihada uchraydigan vazifa. LIKE bilan boshlanadi, lekin tez orada yetmay qoladi.

CREATE TABLE maqola (
    id       int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sarlavha text NOT NULL,
    matn     text NOT NULL
);

INSERT INTO maqola (sarlavha, matn) VALUES
    ('PostgreSQL indexes',
     'Indexes make queries faster but slow down writing data'),
    ('Database design basics',
     'Good design prevents slow queries and broken data later'),
    ('Writing fast queries',
     'A slow query is often caused by a missing index'),
    ('Backup strategies',
     'Regular backups protect data from hardware failure');

LIKE nima uchun yetmaydi #

SELECT sarlavha FROM maqola WHERE matn LIKE '%quer%' ORDER BY id;

        sarlavha
------------------------
 PostgreSQL indexes
 Database design basics
 Writing fast queries
(3 rows)

LIKE ishladi, lekin uning uchta jiddiy kamchiligi bor:

tsvector va tsquery #

To'liq matn qidiruv matnni leksemalarga - normallashtirilgan so'z o'zaklariga ajratadi:

SELECT to_tsvector('english',
                   'The indexes and the queries are faster') AS leksemalar;

           leksemalar
--------------------------------
 'faster':7 'index':2 'queri':5
(1 row)

Ikkita narsa sodir bo'ldi.

Birinchidan, so'zlar o'zagiga keltirildi: indexes → index, queries → queri.

Ikkinchidan, the, and va are umuman yo'qoldi - ular to'xtash so'zlari (stop words). Ular deyarli har matnda uchraydi, shuning uchun qidiruvda foydasi yo'q.

Raqamlarga qarang: 2, 5, 7. Ular so'zning asl matndagi o'rni - oraliqdagi raqamlar tashlangan so'zlarga tegishli. Bu raqamlar keyinroq saralashda ishga tushadi.

So'rov tomoni ham shunday normallashtiriladi:

SELECT plainto_tsquery('english', 'fast queries') AS sorov,
       to_tsvector('english', 'Writing fast queries') @@
       plainto_tsquery('english', 'fast queries') AS mos_keldimi;

      sorov       | mos_keldimi
------------------+-------------
 'fast' & 'queri' | t
(1 row)

@@ operatori - "shu matn shu so'rovga mos keladimi" degani.

Qidiruv so'rovi #

SELECT sarlavha
FROM maqola
WHERE to_tsvector('english', sarlavha || ' ' || matn)
      @@ plainto_tsquery('english', 'slow queries')
ORDER BY id;

        sarlavha
------------------------
 PostgreSQL indexes
 Database design basics
 Writing fast queries
(3 rows)

LIKE topa olmagan narsani u topdi: queries so'rovi query so'zini ham qamrab oldi.

So'rov turlari #

Uchta funksiya bor va ular turlicha ishlaydi:

SELECT websearch_to_tsquery('english', 'data -backup') AS istisno,
       websearch_to_tsquery('english', '"slow query"') AS ibora,
       websearch_to_tsquery('english', 'index or backup') AS yoki;

      istisno       |       ibora        |        yoki
--------------------+--------------------+--------------------
 'data' & !'backup' | 'slow' <-> 'queri' | 'index' | 'backup'
(1 row)

Natijani saralash #

Qidiruvning yarmi - topish, ikkinchi yarmi - muhimini yuqoriga chiqarish:

SELECT sarlavha,
       round(ts_rank(
           to_tsvector('english', sarlavha || ' ' || matn),
           plainto_tsquery('english', 'slow query')
       )::numeric, 4) AS reyting
FROM maqola
WHERE to_tsvector('english', sarlavha || ' ' || matn)
      @@ plainto_tsquery('english', 'slow query')
ORDER BY reyting DESC;

        sarlavha        | reyting
------------------------+---------
 Writing fast queries   |  0.1878
 Database design basics |  0.0991
 PostgreSQL indexes     |  0.0974
(3 rows)

ts_rank so'z chastotasi va o'rniga qarab baho beradi. Sarlavhani muhimroq qilish uchun og'irlik berish mumkin - setweight funksiyasi bilan.

Tezlashtirish #

Har so'rovda to_tsvector ni qayta hisoblash sekin. Ikki yechim bor:

ALTER TABLE maqola
    ADD COLUMN qidiruv tsvector
    GENERATED ALWAYS AS (
        to_tsvector('english', sarlavha || ' ' || matn)
    ) STORED;

CREATE INDEX maqola_qidiruv_idx ON maqola USING GIN (qidiruv);

SELECT sarlavha FROM maqola
WHERE qidiruv @@ websearch_to_tsquery('english', 'backup')
ORDER BY id;

ALTER TABLE
CREATE INDEX
     sarlavha
-------------------
 Backup strategies
(1 row)

Endi to_tsvector faqat yozishda hisoblanadi va GIN indeks qidiruvni tezlashtiradi (14-bo'limda batafsil).

Xulosa #

• LIKE so'z shakllarini bilmaydi, natijani saralamaydi va % bilan boshlansa sekin.
• To'liq matn qidiruv matnni leksemalarga ajratadi va to'xtash so'zlarini tashlaydi.
• to_tsvector matnni, *_tsquery esa so'rovni normallashtiradi.
• @@ operatori ularni solishtiradi.
• Ikkala tomonda bir xil konfiguratsiya ishlatilishi shart.
• websearch_to_tsquery foydalanuvchi kiritgan matn uchun eng xavfsiz - u xato bermaydi.
• U "ibora", -istisno va or sintaksisini tushunadi.
• ts_rank natijani muhimlik bo'yicha saralaydi.
• Tezlik uchun tsvector ni hisoblanadigan ustunda saqlab, GIN indeks qo'ying.
• O'zbek tili uchun tayyor konfiguratsiya yo'q - amalda ko'pincha pg_trgm ishlatiladi.

Keyingi bo'limda indekslarni batafsil ko'ramiz.