În dezvoltarea de aplicații reale, utilizatorii introduc frecvent greșeli de tipar în bara de căutare (de exemplu, caută „postgrs” în loc de „postgresql” sau „bucureti” în loc de „bucurești”). O interogare clasică WHERE nume = 'postgrs' sau LIKE '%postgrs%' va returna 0 rezultate. Extensia oficială pg_trgm rezolvă această problemă prin descompunerea textului în trigrame (grupuri de 3 caractere consecutive) și măsurarea gradului de similitudine matematică.
Activarea și utilizarea pg_trgm
-- 1. Activăm extensia oficială inclusă în PostgreSQL
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 2. Cum vede PostgreSQL un cuvânt descompus în trigrame:
SELECT show_trgm('postgres');
-- Rezultat: {" p"," po",est,gre,ost,res,"es "}
-- 3. Măsurarea similitudinii dintre două șiruri (valoare între 0.0 și 1.0):
SELECT similarity('postgresql', 'postgrs');
-- Rezultat: 0.58 (Grad ridicat de potrivire!)
-- 4. Căutare fuzzy în tabelul de utilizatori:
SELECT id, nume, similarity(nume, 'Alexandru') AS potrivire
FROM utilizatori
WHERE nume % 'Alecsandru' -- Operatorul '%' verifică dacă similitudinea depășește pragul configurat
ORDER BY potrivire DESC;Operatori Esențiali din pg_trgm
| Operator | Semnificație | Tip Returnat |
|---|---|---|
text % pattern |
Verifică dacă similitudinea depășește pragul implicit (0.3). Configurabil prin set_limit(). |
BOOLEAN |
text <-> pattern |
Calculează distanța de similitudine (1 - similarity). Ideal pentru clauza ORDER BY! |
REAL |
text <% pattern |
Verifică similitudinea dintre un cuvânt și orice subșir dintr-un text mai lung. | BOOLEAN |
Soluția de Aur: Accelerarea căutărilor LIKE ‘%termen%’ cu GIN
-- Problema: Un index B-Tree NU poate fi folosit pentru clauze LIKE cu wildcard la început!
-- SELECT * FROM produse WHERE descriere LIKE '%laptop%'; -- Va face Full Table Scan!
-- Soluția: Creăm un index GIN cu clasa de operatori gin_trgm_ops:
CREATE INDEX idx_produse_descriere_trgm ON produse USING gin (descriere gin_trgm_ops);
-- Acum, interogarea cu wildcard funcționează INSTANTANEU folosind indexul GIN:
EXPLAIN ANALYZE
SELECT * FROM produse WHERE descriere ILIKE '%laptop gaming%';
-- Planul va arăta: "Bitmap Index Scan on idx_produse_descriere_trgm" !Optimizarea Autocomplete-ului în Timp Real
Datorită operatorului distanță <->, puteți combina un index GiST cu operatorul KNN (K-Nearest Neighbors) pentru a oferi sugestii de căutare instantanee în sub 2 milisecunde:
CREATE INDEX idx_produse_nume_gist ON produse USING gist (nume gist_trgm_ops);
-- Returnează cele mai apropiate 5 sugestii pentru termenul tastat:
SELECT nume
FROM produse
ORDER BY nume <-> 'samsun'
LIMIT 5;