pg_trgm — fuzzy search, ILIKE tezlashtirish

~20 daq · PostgreSQL · Extensions

Mahsulot qidirish: foydalanuvchi "noutbok" deb yozdi (xato). "noutbuk" topilishi kerak. Yoki LIKE '%noutbuk%' so'rovi juda sekin — million qatorda 3 soniya. pg_trgm extension ikkala muammoni hal qiladi.

pg_trgm nima?

Trigram — ketma-ket uchta belgi. "noutbuk" so'zi trigramlarga bo'linadi:

" no" "nou" "out" "utb" "tbu" "buk" "uk "

Ikki matn qanchalik umumiy trigramga ega bo'lsa — shunchalik o'xshash.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Similarity — o'xshashlik

SELECT similarity('noutbuk', 'noutbok'); -- 0.571428
SELECT similarity('noutbuk', 'telefon'); -- 0.0

-- 0 dan 1 gacha: 1 = bir xil, 0 = hech aloqasi yo'q

ILIKE tezlashtirish — GIN index

Oddiy ILIKE '%matn%' sekin — % boshda bo'lganda index ishlamaydi. pg_trgm bilan GIN index yaratilsa — tez ishlaydi:

CREATE INDEX idx_mahsulot_nomi ON mahsulotlar USING GIN (nomi gin_trgm_ops);

-- Endi tez!
SELECT * FROM mahsulotlar WHERE nomi ILIKE '%noutbuk%';

Fuzzy search — xatoli qidiruv

-- Similarity threshold belgilash
SET pg_trgm.similarity_threshold = 0.3; -- 30% o'xshashlik yetarli

-- Xatoli qidiruv
SELECT nomi, similarity(nomi, 'noutbok') AS ball
FROM mahsulotlar
WHERE nomi % 'noutbok'  -- % operatori: similarity > threshold
ORDER BY ball DESC;

-- Natija:
-- nomi     | ball
-- noutbuk  | 0.57  ← topildi, garchi xato yozilgan bo'lsa ham

LIKE optimizatsiya

-- GIN index bilan barcha LIKE/ILIKE tez ishlaydi
SELECT * FROM mahsulotlar WHERE nomi ILIKE 'nout%';   -- ✅ tez
SELECT * FROM mahsulotlar WHERE nomi ILIKE '%nout%';  -- ✅ tez (pg_trgm bilan)
SELECT * FROM mahsulotlar WHERE nomi ILIKE '%nout';   -- ✅ tez

word_similarity — so'z darajasida

-- Uzun matndagi so'zni qidirish
SELECT word_similarity('noutbuk', 'Eng yaxshi noutbuk va telefon'); -- 1.0

SELECT *
FROM maqolalar
WHERE 'noutbok' <% matn  -- word_similarity operatori
ORDER BY word_similarity('noutbok', matn) DESC;

Tez-tez uchraydigan xatolar

Index yaratishni unutish:

-- Extension bor, lekin index yo'q
CREATE EXTENSION pg_trgm;

SELECT * FROM jadval WHERE ustun ILIKE '%matn%'; -- hali ham sekin!

-- Index kerak!
CREATE INDEX ON jadval USING GIN (ustun gin_trgm_ops);

Juda past threshold:

SET pg_trgm.similarity_threshold = 0.1;
-- 10% o'xshashlik — juda ko'p noto'g'ri natija
-- 0.3-0.4 optimal

Xulosa

CREATE EXTENSION pg_trgm;

-- Index (bir marta)
CREATE INDEX ON jadval USING GIN (ustun gin_trgm_ops);

-- So'rovlar
WHERE ustun ILIKE '%qidiruv%'          -- tezlashtirilgan
WHERE ustun % 'xatoli_qidiruv'         -- fuzzy (similarity)
WHERE 'qidiruv' <% ustun               -- word similarity

-- Funksiyalar
similarity(a, b)                       -- 0..1
word_similarity(so_z, matn)            -- 0..1

Keyingi dars

61-dars: TimescaleDB — time-series ma'lumotlar