Wyszukiwanie rozmyte z podobieństwem pg_trgm
Twórz odporne na literówki wyszukiwanie i autouzupełnianie za pomocą indeksów trigramowych oraz progów podobieństwa.
Wyszukiwanie rozmyte z podobieństwem pg_trgm to bezpłatna lekcja PostgreSQL Performance & Query Optimization na CoddyKit. To lekcja 3 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej PostgreSQL Performance & Query Optimization, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs PostgreSQL Performance & Query Optimization zawiera 4 lekcji w sumie.
Dlaczego dopasowanie rozmyte?
Użytkownicy popełniają literówki. Wpisują jonh zamiast john albo postgers zamiast postgres. Zwykłe porównanie za pomocą =, a nawet LIKE, zwraca dla takich literówek nic.
Dopasowanie rozmyte wyszukuje wiersze wystarczająco podobne do wyszukiwanego terminu, a nie tylko dokładnie pasujące. PostgreSQL udostępnia tę funkcję w rozszerzeniu pg_trgm, które umożliwia:
- Wyszukiwanie odporne na literówki — dopasowanie mimo niewielkich błędów pisowni
- Autouzupełnianie — sugerowanie wyników podczas pisania
- Usuwanie duplikatów — znajdowanie niemal zduplikowanych nazw lub adresów
Najważniejsza idea polega na mierzeniu podobieństwa, a nie równości.
Czym jest trigram?
Trigram to grupa trzech kolejnych znaków w ciągu. pg_trgm dzieli każdy ciąg na zbiór trigramów, dodając spacje na początku i na końcu.
Dla słowa cat PostgreSQL tworzy trigramy: " c", " ca", "cat", "at ". Możesz sprawdzić je samodzielnie za pomocą show_trgm().
Dwa ciągi uznaje się za podobne, gdy mają wiele wspólnych trigramów. Ponieważ trigramy nakładają się na siebie, pojedyncza literówka uszkadza tylko kilka z nich, więc podobne słowa nadal mają większość trigramów wspólną.
-- Enable the extension once per database
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Inspect the trigrams of a word
SELECT show_trgm('cat');
-- {" c"," ca","at ","cat"}Funkcja similarity()
Podstawową funkcją jest similarity(a, b). Zwraca wartość real z zakresu od 0 (brak wspólnych trygramów) do 1 (identyczne ciągi znaków).
Wewnętrznie jest to liczba wspólnych trygramów podzielona przez liczność sumy obu zbiorów trygramów (iloraz w stylu współczynnika Jaccarda). Im bardziej zbliżona pisownia, tym wyższy wynik.
Proszę zauważyć, że pojedyncza literówka obniża wynik tylko nieznacznie, podczas gdy niepowiązane słowo otrzymuje wynik bliski zeru.
SELECT
similarity('postgres', 'postgres') AS exact, -- 1
similarity('postgres', 'postgers') AS typo, -- ~0.45
similarity('postgres', 'banana') AS unrelated; -- 0Operator podobieństwa %
Zapisywanie wszędzie similarity(a, b) > threshold jest rozwlekłe, a co ważniejsze — nie może bezpośrednio korzystać z indeksu trygramowego. Zamiast tego pg_trgm udostępnia operator %.
a % b zwraca true, gdy podobieństwo obu ciągów znaków przekracza bieżący próg podobieństwa. Ten operator uwzględnia indeksy, więc indeks trygramowy GIN lub GiST może przyspieszyć jego działanie.
Domyślny próg wynosi 0.3. Wartość sesji można odczytać za pomocą show_limit() (starszy interfejs) lub ustawienia GUC pg_trgm.similarity_threshold.
-- These two rows are 'similar enough' at the default 0.3 threshold
SELECT 'postgres' % 'postgers' AS is_similar; -- t
-- See the current threshold
SHOW pg_trgm.similarity_threshold; -- 0.3Dostosowywanie progu podobieństwa
Próg określa kompromis między pełnością (wykrywaniem większej liczby dopasowań) a precyzją (unikaniem przypadkowych dopasowań).
- Niższy próg (np. 0.2) → więcej dopasowań, więcej wyników, więcej fałszywych trafień
- Wyższy próg (np. 0.5) → bardziej rygorystyczne dopasowanie, mniej wyników, ryzyko pominięcia rzeczywistych literówek
Ustawia się go dla sesji za pomocą SET pg_trgm.similarity_threshold. Operator % natychmiast uwzględnia nową wartość, a każde skanowanie indeksu nadal pozostaje prawidłowe.
-- Tighten matching for this session
SET pg_trgm.similarity_threshold = 0.45;
SELECT name
FROM products
WHERE name % 'wireles keyboad'
ORDER BY similarity(name, 'wireles keyboad') DESC;Indeksy trygramowe: GIN a GiST
Bez indeksu operator % wymusza skanowanie sekwencyjne, które oblicza podobieństwo dla każdego wiersza — jest to odpowiednie dla setek wierszy, ale uciążliwe przy milionach. pg_trgm obsługuje dwa typy indeksów:
- GIN (
gin_trgm_ops) — szybsze wyszukiwanie, krótsze tworzenie indeksu w przypadku wyszukiwania z przewagą odczytów; zazwyczaj domyślny wybór. - GiST (
gist_trgm_ops) — obsługuje porządkowanie według odległości dla KNN (<->) i może być tańszy w aktualizacji.
W typowym wyszukiwaniu odpornym na literówki należy użyć GIN. Indeks należy utworzyć na przeszukiwanej kolumnie.
-- GIN index for fast % and LIKE/ILIKE acceleration
CREATE INDEX idx_products_name_trgm
ON products
USING gin (name gin_trgm_ops);Jak indeks przyspiesza działanie % i LIKE
Indeks GIN dla trygramów pomaga nie tylko operatorowi %. Ponieważ PostgreSQL może wyodrębniać trygramy ze wzorca LIKE lub ILIKE, ten sam indeks przyspiesza również wyszukiwanie z symbolami wieloznacznymi, na przykład '%board%' — także z symbolami na początku, z których zwykły indeks B-tree nie może korzystać.
Proszę uruchomić EXPLAIN ANALYZE i sprawdzić, czy na indeksie trygramowym występuje Bitmap Index Scan zamiast Seq Scan. Potwierdza to, że planer korzysta z indeksu.
EXPLAIN ANALYZE
SELECT name
FROM products
WHERE name ILIKE '%keyboard%';
-- -> Bitmap Index Scan on idx_products_name_trgmSortowanie wyników według podobieństwa
Dopasowanie to tylko połowa zadania — użytkownicy oczekują, że najpierw pojawi się najlepszy wynik. Proszę filtrować za pomocą % (operatora przyjaznego indeksom), a następnie sortować za pomocą similarity() w ORDER BY.
Proszę zachować predykat WHERE name % :q, aby indeks ograniczył liczbę kandydatów, a następnie uszeregować pozostałe wyniki. Obliczanie similarity() tylko dla przefiltrowanego zbioru jest tanie.
SELECT name, similarity(name, 'mechancal keybord') AS score
FROM products
WHERE name % 'mechancal keybord'
ORDER BY score DESC
LIMIT 10;Porządkowanie odległości KNN za pomocą <->
W przypadku zapytań typu „podaj N najbliższych nazw” rozszerzenie pg_trgm udostępnia operator odległości <->, zdefiniowany jako 1 - similarity(a, b). Mniejsza odległość oznacza większe podobieństwo.
Gdy użyją Państwo ORDER BY column <-> :q, indeks trygramowy GiST może zwrócić wiersze bezpośrednio w kolejności odległości (skanowanie indeksu KNN) — bez etapu sortowania i bez jawnego progu. Jest to idealne rozwiązanie dla autouzupełniania i wyszukiwania „najbliższego dopasowania”.
-- Requires a GiST trigram index for the KNN scan
CREATE INDEX idx_products_name_gist
ON products USING gist (name gist_trgm_ops);
SELECT name
FROM products
ORDER BY name <-> 'wireles mouse'
LIMIT 5;word_similarity na potrzeby autouzupełniania
Zwykłe similarity() obniża wynik przy różnicach długości: dopasowanie krótkiego zapytania app do długiego ciągu apple smartphone pro otrzymuje niski wynik, ponieważ większość trygramów należy do dłuższego tekstu.
word_similarity(a, b) rozwiązuje ten problem, wyszukując najlepiej dopasowany spójny fragment ciągu b. Ma własny operator <% oraz własne ustawienie GUC pg_trgm.word_similarity_threshold (domyślnie 0.6) — doskonale sprawdza się w autouzupełnianiu, gdy zapytanie jest prefiksem lub pojedynczym słowem.
SELECT
similarity('app', 'apple smartphone') AS plain, -- low
word_similarity('app', 'apple smartphone') AS word; -- higher
-- Index-friendly autocomplete filter
SELECT name FROM products WHERE 'app' <% name;Praktyczne pułapki
Podczas pracy z pg_trgm często pojawiają się następujące problemy:
- Bardzo krótkie zapytania (1–2 znaki) mają prawie żadnych trygramów, więc podobieństwo jest niewiarygodne — autouzupełnianie należy uruchamiać dopiero po osiągnięciu minimalnej długości.
- Wielkość liter i akcenty: dopasowywanie trygramów jest niewrażliwe na wielkość liter, ale jeśli wymagają tego dane, należy normalizować akcenty (np. za pomocą
unaccent). - Wybór indeksu: nie należy wybierać GiST, chyba że potrzebne jest porządkowanie KNN za pomocą
<->; w przypadku%GIN jest zazwyczaj szybszy. - Próg zależny od zastosowania: wyszukiwanie, autouzupełnianie i usuwanie duplikatów często wymagają różnych progów — należy ustawiać je dla sesji, a nie globalnie.
Szybkie sprawdzenie
Proszę sprawdzić swoją wiedzę na temat wydajności wyszukiwania trygramowego.
Podsumowanie
Mogą już Państwo budować w PostgreSQL szybkie wyszukiwanie odporne na literówki za pomocą pg_trgm:
- Trygramy dzielą ciągi znaków na fragmenty o długości 3 znaków; wspólne fragmenty oznaczają podobieństwo.
similarity()zwraca wynik od 0 do 1; operator % filtruje wedługpg_trgm.similarity_threshold(domyślnie 0.3) i uwzględnia indeksy.- Należy utworzyć indeks GIN (
gin_trgm_ops), aby przyspieszyć działanie%,LIKEiILIKE— także przy początkowych symbolach wieloznacznych. - Należy filtrować za pomocą
%, a następnie uszeregować wyniki za pomocąsimilarity()wORDER BY; można też użyć odległości<->z indeksem GiST do porządkowania KNN. - Do autouzupełniania należy używać
word_similarity/<%oraz dostosowywać progi do danego zastosowania.
Należy zawsze potwierdzić za pomocą EXPLAIN ANALYZE, że uzyskiwane jest Bitmap (lub KNN) Index Scan zamiast Seq Scan.
Ucz się SQL dzięki korepetycjom AI — za darmo
Pisz i uruchamiaj kod w przeglądarce, otrzymuj natychmiastową pomoc od korepetytora AI dostępnego 24/7 i kontynuuj naukę w sieci lub w aplikacji.
- Kursy
- 22
- Lekcje
- 88
Często zadawane pytania
Czy lekcja „Wyszukiwanie rozmyte z podobieństwem pg_trgm” jest bezpłatna?
Tak — pełny tekst „Wyszukiwanie rozmyte z podobieństwem pg_trgm” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu PostgreSQL Performance & Query Optimization, przejdź na CoddyKit PRO. Kurs PostgreSQL Performance & Query Optimization zawiera 4 lekcji w sumie.
Co nauczysz się w „Wyszukiwanie rozmyte z podobieństwem pg_trgm”?
Twórz odporne na literówki wyszukiwanie i autouzupełnianie za pomocą indeksów trigramowych oraz progów podobieństwa. Ćwiczysz PostgreSQL Performance & Query Optimization z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.
Czy potrzebuję doświadczenia, aby zacząć PostgreSQL Performance & Query Optimization?
Nie wymagamy żadnego doświadczenia. PostgreSQL Performance & Query Optimization w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 3 z 4.
Ile czasu zajmuje lekcja „Wyszukiwanie rozmyte z podobieństwem pg_trgm”?
Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.
Czy mogę pisać i uruchamiać kod w tej lekcji PostgreSQL Performance & Query Optimization?
Tak. Każda lekcja PostgreSQL Performance & Query Optimization zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.
Wszystkie lekcje w tym kursie
- Projektowanie kolumn tsvector i indeksów GIN
- Ranking i dostrajanie trafności za pomocą ts_rank
- Wyszukiwanie rozmyte z podobieństwem pg_trgm
- Łączenie filtrów z predykatami wyszukiwania