Dopasowywanie wzorców z LIKE, SIMILAR TO lub wyrażeniami regularnymi w PostgreSQL

Musiałem napisać proste zapytanie, w którym szukam nazwiska ludzi, które zaczynają się na B lub D:

SELECT s.name 
FROM spelers s 
WHERE s.name LIKE 'B%' OR s.name LIKE 'D%'
ORDER BY 1

Zastanawiałem się, czy istnieje sposób na przepisanie tego, aby stać się bardziej wydajnym. Więc mogę uniknąć ori / lub like?

Twoje zapytanie jest w zasadzie optymalne. Składnia nie będzie znacznie krótsza, zapytanie nie będzie znacznie szybsze:

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

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

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

Lub nieco szybciej, z klasą postaci :

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

Szybki test bez indeksu daje szybsze wyniki niż SIMILAR TOw obu przypadkach dla mnie.
Przy odpowiednim indeksie B-Tree LIKEwygrywa ten wyścig o rzędy wielkości.

Przeczytaj podstawowe informacje na temat dopasowywania wzorów w instrukcji .

Indeks doskonałej wydajności

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

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops);

Przyspiesza tego rodzaju zapytania o rzędy wielkości. Szczególne uwagi dotyczą kolejności sortowania specyficznej dla danego regionu. Przeczytaj więcej o klasach operatorów w instrukcji . Jeśli używasz standardowych ustawień regionalnych „C” (większość ludzi tego nie robi), zrobi to zwykły indeks (z domyślną klasą operatora).

Taki indeks jest dobry tylko dla wzorców zakotwiczonych w lewo (dopasowanie od początku łańcucha).

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

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

Przegląd operatorów dopasowywania wzorców

• LIKE( ~~) jest prosty i szybki, ale ma ograniczone możliwości.
  ILIKE( ~~*) wariant bez rozróżniania wielkości liter.
  pg_trgm rozszerza obsługę indeksu dla obu.

• ~ (dopasowanie wyrażeń regularnych) jest potężne, ale bardziej złożone i może być powolne w przypadku czegoś więcej niż wyrażeń podstawowych.

• SIMILAR TOjest po prostu bezcelowe . Osobliwa półrasa LIKEi wyrażenia regularne. Nigdy tego nie używam. Patrz poniżej.

• % jest operatorem „podobieństwa” zapewnianym przez dodatkowy modułpg_trgm. Patrz poniżej.

• @@jest operatorem wyszukiwania tekstu. Patrz poniżej.

pg_trgm - dopasowanie trygramu

Począwszy od PostgreSQL 9.1 możesz ułatwić rozszerzenie, pg_trgmaby zapewnić obsługę indeksu dla dowolnego wzorca LIKE/ ILIKE(i prostych wzorców ~wyrażeń regularnych z ) za pomocą indeksu GIN lub GiST.

Szczegóły, przykład i linki:

• Jak wdraża się LIKE?

pg_trgmzapewnia również tych operatorów :

• % - operator „podobieństwa”
• <%(komutator %>:) - operator „word_similarity” w Postgresie 9.6 lub nowszym
• <<%(komutator %>>:) - operator „strict_word_similarity” w Postgres 11 lub nowszy

Wyszukiwanie tekstu

Jest specjalnym rodzajem dopasowania wzorca z osobnymi typami infrastruktury i indeksu. Korzysta ze słowników i wyszukiwania oraz jest doskonałym narzędziem do wyszukiwania słów w dokumentach, szczególnie w przypadku języków naturalnych.

Obsługiwane jest również dopasowanie prefiksu :

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

Jak również wyszukiwanie fraz od Postgres 9.6:

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

Rozważ wprowadzenie w podręczniku oraz przegląd operatorów i funkcji .

Dodatkowe narzędzia do dopasowywania rozmytych ciągów znaków

Dodatkowy moduł fuzzystrmatch oferuje kilka dodatkowych opcji, ale wydajność jest ogólnie gorsza od wszystkich powyższych.

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

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

Odpowiedź jest prosta. SIMILAR TOwyrażenia są wewnętrznie przepisywane na wyrażenia regularne. Tak więc dla każdego SIMILAR TOwyrażenia istnieje co najmniej jedno szybsze wyrażenie regularne (co pozwala zaoszczędzić koszty przepisywania wyrażenia). SIMILAR TO Nigdy nie zyskujesz na wydajności .

A proste wyrażenia, które można wykonać za pomocą LIKE( ~~), są i LIKEtak szybsze .

SIMILAR TOjest obsługiwany tylko w PostgreSQL, ponieważ skończył we wczesnych wersjach językowych standardu SQL. Nadal się tego nie pozbyli. Ale są plany, aby go usunąć i dołączyć dopasowania wyrażeń regularnych - a przynajmniej tak słyszałem.

EXPLAIN ANALYZEujawnia to. Po prostu spróbuj sam z dowolnym stołem!

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

Ujawnia:

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

SIMILAR TOzostał przepisany wyrażeniem regularnym ( ~).

Najwyższa wydajność w tym konkretnym przypadku

Ale EXPLAIN ANALYZEujawnia więcej. Spróbuj, korzystając z wyżej wymienionego indeksu:

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 jest świadomy Locale ( text_pattern_opslub przy użyciu locale C) proste wyrażenia lewe zakotwiczone są przepisywane z tych operatorów wzór tekst: ~>=~, ~<=~, ~>~, ~<~. Tak jest w przypadku ~, ~~lub SIMILAR TOpodobnie.

To samo dotyczy indeksów varchartypów z varchar_pattern_opslub charz bpchar_pattern_ops.

Tak więc, zastosowany do pierwotnego pytania, jest to 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ę szukać sąsiednich inicjałów , możesz uprościć dalej:

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

Zysk w porównaniu do zwykłego użycia ~lub ~~jest niewielki. Jeśli wydajność nie jest twoim najważniejszym wymogiem, powinieneś po prostu trzymać się standardowych operatorów - osiągając to, co już masz w pytaniu.

Co powiesz na dodanie kolumny do tabeli. W zależności od aktualnych 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 obliczanych w tabelach podstawowych a SQL Server, ale nową kolumnę można obsługiwać za pomocą wyzwalacza. Oczywiście ta nowa kolumna zostałaby zaindeksowana.

Alternatywnie, indeks wyrażenia dałby ci to samo, tańsze. Na przykład:

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

Zapytania pasujące do wyrażenia w ich warunkach mogą korzystać z tego indeksu.

W ten sposób uderzenie wydajności jest pobierane, gdy dane są tworzone lub zmieniane, więc może być odpowiednie tylko w środowisku 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, czy też twoje oryginalne wypowiedzi są dostępne w Postgres.

Jeśli utworzysz sugerowany indeks, zainteresuje Cię również porównanie tego 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

W przeszłości, w obliczu podobnego problemu z wydajnością, zwiększałem znak ASCII ostatniej litery i robiłem MIĘDZY. Otrzymujesz wtedy najlepszą wydajność, dla podzbioru funkcjonalności LIKE. Oczywiście działa to tylko w niektórych sytuacjach, ale w przypadku bardzo dużych zestawów danych, w których np. Szukasz nazwy, wydajność spada z 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.

Do sprawdzania inicjałów często używam rzutowania na "char"(z podwójnymi cudzysłowami). Nie jest przenośny, ale bardzo szybki. Wewnętrznie po prostu usuwa tekst i zwraca pierwszy znak, a operacje porównywania „char” są bardzo szybkie, ponieważ typ ma stałą długość 1-bajta:

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

Zauważ, że rzutowanie na "char"jest szybsze niż ascii()odchylenie przez @ Sole021, ale nie jest kompatybilne z UTF8 (ani żadnym innym kodowaniem w tym zakresie), zwracając po prostu pierwszy bajt, więc powinno się go używać tylko w przypadkach, gdy porównanie jest przeciwko zwykłemu staremu 7 -bitowe znaki ASCII.

Istnieją dwie niewymienione jeszcze metody postępowania w takich przypadkach:

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

   CREATE INDEX ON spelers WHERE name LIKE 'B%'

2. partycjonowanie samej tabeli (użycie pierwszego znaku jako klucza partycjonowania) - ta technika jest szczególnie warta rozważenia w PostgreSQL 10+ (mniej bolesne partycjonowanie) i 11+ (czyszczenie partycji podczas wykonywania zapytania).

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

Prawdopodobnie szybciej wykonać porównanie jednego znaku:

SUBSTR(s.name,1,1)='B' OR SUBSTR(s.name,1,1)='D'