Dopasowywanie wzorców za pomocą LIKE, SIMILAR TO lub wyrażeń regularnych w PostgreSQL

Twoje zapytanie jest prawie optymalne. Składnia nie będzie dużo krótsza, zapytanie nie będzie dużo szybsze:

SELECT name FROM spelers WHERE name LIKE "B%" OR name LIKE "D%" ORDER BY 1; 

Jeśli y naprawdę chcesz skrócić składnię , użyj wyrażenia regularnego z gałęziami :

... WHERE name ~ "^(B|D).*" 

Lub trochę szybciej, z klasą znaków :

... WHERE name ~ "^[BD].*" 

Szybki test bez indeksu daje szybsze wyniki niż w przypadku SIMILAR TO w obu przypadkach dla mnie.
Z odpowiednim indeksem B-Tree na miejscu, LIKE wygrywa ten wyścig pod względem wielkości.

Przeczytaj podstawy o dopasowywaniu wzorców w instrukcji .

Indeks zapewniający najwyższą wydajność

Jeśli obawiasz się z wydajnością utwórz taki indeks dla większych tabel:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Sprawia, że tego rodzaju zapytania są szybsze o rząd wielkości. Specjalne uwagi dotyczą kolejności sortowania specyficznej dla lokalizacji. Przeczytaj więcej o klasach operatorów w instrukcji . Jeśli używasz standardowego ” C ” ustawień regionalnych (większość ludzi tego nie robi), zwykły indeks (z domyślną klasą operatora) będzie do.

Taki indeks jest dobry tylko dla wzorców zakotwiczonych w lewo (pasujących od początku ciągu).

SIMILAR TO lub wyrażenia regularne z podstawowymi wyrażeniami zakotwiczonymi w lewo również mogą używać tego indeksu. Ale nie z gałęziami (B|D) lub klasami znaków [BD] (przynajmniej w moich testach na PostgreSQL 9.0).

Dopasowania trygramu lub wyszukiwanie tekstu używają specjalnych indeksów GIN lub GiST.

Przegląd operatorów dopasowywania wzorców

• LIKE ( ~~ ) jest prosta i szybkie, ale ograniczone w swoich możliwościach.
  ILIKE ( ~~* ) wariant bez rozróżniania wielkości liter.
  pg_trgm rozszerza obsługę indeksów dla obu.

• ~ (dopasowanie wyrażenia regularnego) jest potężne, ale bardziej złożone i może działać wolno w przypadku czegokolwiek więcej niż podstawowych wyrażeń.

• SIMILAR TO jest po prostu bezcelowe . Osobliwy półrasa LIKE i wyrażeń regularnych. Nigdy tego nie używam. Zobacz poniżej.

• % to operator ” podobieństwa ” udostępniany przez dodatkowy moduł pg_trgm. Zobacz poniżej.

• @@ to operator wyszukiwania tekstu. Zobacz poniżej.

pg_trgm – dopasowanie trygramu

Zaczynając od PostgreSQL 9.1 możesz ułatwić rozszerzenie pg_trgm , aby zapewnić obsługę indeksu dla dowolnego LIKE / ILIKE wzorzec (i proste wzorce regexp z ~) za pomocą Indeks GIN lub GiST.

Szczegóły, przykład i linki:

• Jak zaimplementowano LIKE?

pg_trgm udostępnia także te operatory :

• % – ” podobieństwo ” operator
• <% (commutator: %>) – the ” word_similarity w Postgres 9.6 lub nowszym
• <<% (commutator: %>>) – the ” strict_word_similarity ” operator w Postgres 11 lub nowszym

Wyszukiwanie tekstowe

Jest to specjalny typ dopasowywania wzorców z oddzielnymi typami infrastruktury i indeksów. Korzysta ze słowników i słowników oraz jest doskonałym narzędziem do znajdowania słów w dokumentach, zwłaszcza w przypadku języków naturalnych.

Dopasowanie prefiksu jest również obsługiwany:

• Uzyskaj częściowe dopasowanie z kolumny TSVECTOR indeksowanej przez GIN

Oprócz wyszukiwania frazy od Postgres 9.6:

• Jak wyszukiwać podzielone słowa w wyszukiwaniu pełnotekstowym PostgreSQL?

Weź pod uwagę wprowadzenie w podręczniku i przegląd operatorów i funkcji .

Dodatkowe narzędzia do rozmytego dopasowywania łańcuchów

Dodatkowy moduł fuzzystrmatch oferuje więcej opcji, ale wydajność jest generalnie gorsza od wszystkich powyższych.

W szczególności różne implementacje funkcji levenshtein() mogą być instrumentalne.

Dlaczego wyrażenia regularne (~) są zawsze szybsze niż SIMILAR TO?

Odpowiedź jest prosta. Wyrażenia SIMILAR TO są wewnętrznie przepisywane na wyrażenia regularne. Tak więc dla każdego wyrażenia SIMILAR TO istnieje co najmniej jedno szybsze wyrażenie regularne (co oszczędza narzut związany z przepisywaniem wyrażenia). Używanie SIMILAR TO kiedykolwiek nie daje żadnego wzrostu wydajności.

A proste wyrażenia, które można wykonać za pomocą LIKE (~~), są szybsze dzięki LIKE w każdym razie.

SIMILAR TO jest obsługiwany tylko w PostgreSQL, ponieważ znalazł się we wczesnych wersjach standardu SQL. Nadal się go nie pozbyli. Ale są plany, aby go usunąć i zamiast tego dołączyć dopasowania regexp – a przynajmniej tak słyszałem.

EXPLAIN ANALYZE to ujawnia. Po prostu spróbuj samodzielnie z dowolną tabelą!

EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO "B%"; 

Ujawnia:

... Seq Scan on spelers (cost= ... Filter: (name ~ "^(?:B.*)$"::text) 

SIMILAR TO został przepisany za pomocą wyrażenia regularnego (~).

Najwyższa wydajność w tym konkretnym przypadku

Ale EXPLAIN ANALYZE ujawnia więcej. Spróbuj, mając wspomniany indeks na miejscu:

EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ "^B.*; 

Ujawnia:

... -> Bitmap Heap Scan on spelers (cost= ... Filter: (name ~ "^B.*"::text) -> Bitmap Index Scan on spelers_name_text_pattern_ops_idx (cost= ... Index Cond: ((prod ~>=~ "B"::text) AND (prod ~<~ "C"::text)) 

Wewnętrznie, z indeksem, który nie obsługuje ustawień regionalnych (text_pattern_ops lub używając ustawień regionalnych C) proste wyrażenia zakotwiczone w lewo są przepisywane przy użyciu tych operatorów wzorców tekstowych: ~>=~, ~<=~, ~>~, ~<~. Tak jest w przypadku ~, ~~ lub SIMILAR TO podobnie.

To samo dotyczy indeksów typów varchar z varchar_pattern_ops lub char z bpchar_pattern_ops.

Zastosowano więc do pierwotnego pytania, oto najszybszy możliwy sposób :

SELECT name FROM spelers WHERE name ~>=~ "B" AND name ~<~ "C" OR name ~>=~ "D" AND name ~<~ "E" ORDER BY 1; 

Oczywiście, jeśli zdarzy Ci się wyszukać sąsiednie inicjały , możesz jeszcze bardziej uprościć:

WHERE name ~>=~ "B" AND name ~<~ "D" -- strings starting with B or C 

Zysk w porównaniu z zwykłym użyciem ~ lub ~~ jest niewielki. Jeśli wydajność nie jest Twoim najważniejszym wymaganiem, powinieneś po prostu trzymać się standardowych operatorów – dochodząc do tego, co już masz w pytaniu.

Komentarze

• OP nie ' nie ma indeksu nazwy, ale czy wiesz, jeśli tak, to czy ich pierwotne zapytanie obejmowałoby 2 poszukiwania zakresowe i similar skan?
• @MartinSmith: Szybki test z EXPLAIN ANALYZE pokazuje 2 skany indeksu bitmap.Wiele skanów indeksów bitmap można połączyć dość szybko.
• Dzięki. Czy byłoby zatem jakikolwiek przebieg zastępowania OR na UNION ALL lub zamiany name LIKE 'B%' na name >= 'B' AND name <'C' w Postgres?
• @MartinSmith: UNION won ' t ale tak, połączenie zakresów w jedną klauzulę WHERE przyspieszy zapytanie. Dodałem więcej do mojej odpowiedzi. Oczywiście musisz wziąć pod uwagę swoje ustawienia regionalne. Wyszukiwanie z uwzględnieniem lokalizacji jest zawsze wolniejsze.
• @a_horse_with_no_name: Nie spodziewam się. Nowe możliwości pg_tgrm z indeksami GIN to gratka dla ogólnego wyszukiwania tekstu. Wyszukiwanie zakotwiczone na początku jest już szybsze.

Co powiesz na dodanie kolumny do stół. W zależności od twoich rzeczywistych wymagań:

person_name_start_with_B_or_D (Boolean) person_name_start_with_char CHAR(1) person_name_start_with VARCHAR(30) 

PostgreSQL nie obsługuje kolumn obliczonych w tabelach podstawowych a la SQL Server , ale nowa kolumna może być utrzymywana przez wyzwalacz. Oczywiście ta nowa kolumna byłaby indeksowana.

Alternatywnie indeks w wyrażeniu dałoby to samo, tańsze. Np .:

CREATE INDEX spelers_name_initial_idx ON spelers (left(name, 1)); 

Zapytania, które pasują do wyrażenia w ich warunkach, mogą wykorzystywać ten indeks.

W ten sposób wynik wydajności jest brany pod uwagę, gdy dane są tworzone lub zmieniane, więc może być odpowiednie tylko dla środowiska o niskiej aktywności (tj. znacznie mniej zapisów niż odczytów).

Możesz spróbować

SELECT s.name FROM spelers s WHERE s.name SIMILAR TO "(B|D)%" ORDER BY s.name 

Nie mam pojęcia, czy powyższe lub twoje oryginalne wyrażenie można rozpatrzyć w Postgresie.

Jeśli utworzysz sugerowany indeks, chciałbym również usłyszeć, jak to porównuje się z innymi opcjami.

SELECT name FROM spelers WHERE name >= "B" AND name < "C" UNION ALL SELECT name FROM spelers WHERE name >= "D" AND name < "E" ORDER BY name 

Komentarze

• Udało się i mam koszt 1.19, gdzie miałem 1,25. Dzięki!

To, co robiłem w przeszłości, mając podobny problem z wydajnością, to zwiększ znak ASCII ostatniej litery i wykonaj BETWEEN. Otrzymujesz wtedy najlepszą wydajność dla podzbioru funkcji LIKE. Oczywiście działa to tylko w niektórych sytuacjach, ale w przypadku bardzo dużych zbiorów danych, w których „poszukujesz na przykład nazwy, wydajność przechodzi od fatalnej do akceptowalnej.

Bardzo stare pytanie, ale znalazłem inne szybkie rozwiązanie tego problemu:

SELECT s.name FROM spelers s WHERE ascii(s.name) in (ascii("B"),ascii("D")) ORDER BY 1 

Ponieważ funkcja ascii ( ) patrzy tylko na pierwszy znak ciągu.

Komentarze

• Czy to używa indeksu na (name)?

Aby sprawdzić inicjały, często używam rzutowania na "char" (z podwójnymi cudzysłowami). Nie jest przenośny, ale bardzo szybki. Wewnętrznie po prostu usuwa przywiązanie do tekstu i zwraca pierwszy znak, a operacje porównania „char” są bardzo szybkie, ponieważ typ ma stałą długość 1-bajtową:

SELECT s.name FROM spelers s WHERE s.name::"char" =ANY( ARRAY[ "char" "B", "D" ] ) ORDER BY 1 

Pamiętaj, że przesyłanie do "char" jest szybsze niż ascii() slution przez @ Sole021, ale nie jest zgodne z UTF8 (ani żadnym innym kodowaniem dla to ma znaczenie), zwracając po prostu pierwszy bajt, więc powinno być używane tylko w przypadkach, gdy porównanie dotyczy zwykłych starych 7-bitowych znaków ASCII.

Istnieją dwie niewymienione jeszcze metody radzenia sobie z takimi przypadkami:

1. indeks częściowy (lub podzielony na partycje – jeśli utworzono dla pełnego zakresu ręcznie) – najbardziej przydatny, wymagany jest tylko podzbiór danych (na przykład podczas konserwacji lub tymczasowy w przypadku niektórych raportów):

   CREATE INDEX ON spelers WHERE name LIKE "B%" 

2. partycjonowanie samej tabeli (używając pierwszego znaku jako klucza partycjonującego) – ta technika jest szczególnie wort h biorąc pod uwagę PostgreSQL 10+ (mniej bolesne partycjonowanie) i 11+ (czyszczenie partycji podczas wykonywania zapytania).

Ponadto, jeśli dane w tabeli są posortowane, można skorzystaj z indeksu BRIN (nad pierwszym znakiem).