Musterübereinstimmung mit LIKE, SIMILAR TO oder regulären Ausdrücken in PostgreSQL

Ihre Abfrage ist so ziemlich das Optimum. Die Syntax wird nicht viel kürzer, die Abfrage wird nicht viel schneller:

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

Wenn y Wenn Sie die Syntax wirklich verkürzen möchten, verwenden Sie einen regulären Ausdruck mit Zweigen :

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

oder geringfügig schneller mit einer Zeichenklasse :

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

Ein schneller Test ohne Index liefert schnellere Ergebnisse als für SIMILAR TO in beiden Fällen für mich.
Mit einem geeigneten B-Tree-Index gewinnt LIKE dieses Rennen um Größenordnungen.

Lesen Sie die Grundlagen zum Mustervergleich im Handbuch .

Index für überlegene Leistung

Wenn Sie besorgt sind Erstellen Sie mit der Leistung einen Index wie diesen für größere Tabellen:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Beschleunigt diese Art von Abfrage um Größenordnungen. Besondere Überlegungen gelten für die länderspezifische Sortierreihenfolge. Weitere Informationen zu Operatorklassen finden Sie im Handbuch . Wenn Sie das Standardgebietsschema “ C “ verwenden (die meisten Leute tun dies nicht), wird ein einfacher Index (mit Standardoperatorklasse) verwendet do.

Ein solcher Index ist nur für links verankerte Muster geeignet (Übereinstimmung vom Anfang der Zeichenfolge an).

SIMILAR TO oder Reguläre Ausdrücke mit einfachen linksverankerten Ausdrücken können diesen Index ebenfalls verwenden. jedoch nicht mit Zweigen (B|D) oder Zeichenklassen [BD] (zumindest in meinen Tests unter PostgreSQL 9.0).

Trigrammübereinstimmungen oder Textsuche verwenden spezielle GIN- oder GiST-Indizes.

Übersicht über Mustervergleichsoperatoren

• LIKE ( ~~ ) ist einfach und schnell, aber in seinen Fähigkeiten begrenzt.
  ILIKE ( ~~* ) die Variante, bei der die Groß- und Kleinschreibung nicht berücksichtigt wird.
  pg_trgm erweitert die Indexunterstützung für beide.

• ~ (Übereinstimmung mit regulären Ausdrücken) ist leistungsstark, aber komplexer und kann für mehr als grundlegende Ausdrücke langsam sein.

• SIMILAR TO ist nur sinnlos . Eine eigenartige Mischling aus LIKE und regulären Ausdrücken. Ich benutze es nie. Siehe unten.

• % ist der Operator “ Ähnlichkeit „, der vom zusätzlichen Modul pg_trgm. Siehe unten.

• @@ ist der Textsuchoperator. Siehe unten.

pg_trgm – Trigram Matching

Beginnend mit PostgreSQL 9.1 Sie können die Erweiterung pg_trgm vereinfachen, um Indexunterstützung für any LIKE / ILIKE Muster (und einfache Regexp-Muster mit ~) unter Verwendung von a GIN- oder GiST-Index.

Details, Beispiel und Links:

• Wie wird LIKE implementiert?

pg_trgm bietet auch diese Operatoren :

• % – die “ Ähnlichkeit “ Operator
• <% (Kommutator: %>) – die “ word_similarity “ in Postgres 9.6 oder höher
• <<% (Kommutator: %>>) – die “ strict_word_similarity “ Operator in Postgres 11 oder höher

Textsuche

Ist eine spezielle Art der Musterübereinstimmung mit separaten Infrastruktur- und Indextypen. Es verwendet Wörterbücher und Stemming und ist ein großartiges Werkzeug, um Wörter in Dokumenten zu finden, insbesondere für natürliche Sprachen.

Präfixabgleich wird ebenfalls unterstützt:

• Teilweise Übereinstimmung aus der GIN-indizierten TSVECTOR-Spalte abrufen

sowie Phrasensuche seit Postgres 9.6:

• Wie suche ich in der PostgreSQL-Volltextsuche nach getrennten Wörtern?

Einführung in das Handbuch und die Übersicht über Operatoren und Funktionen .

Zusätzliche Tools für den Fuzzy-String-Abgleich

Das zusätzliche Modul fuzzystrmatch bietet einige weitere Optionen, aber die Leistung ist im Allgemeinen allen oben genannten unterlegen.

Insbesondere verschiedene Implementierungen der Funktion levenshtein() können hilfreich sein.

Warum sind reguläre Ausdrücke (~) immer schneller als SIMILAR TO?

Die Antwort ist einfach. SIMILAR TO Ausdrücke werden intern in reguläre Ausdrücke umgeschrieben. Für jeden SIMILAR TO Ausdruck gibt es also mindestens einen schnelleren regulären Ausdruck (wodurch der Aufwand für das Umschreiben des Ausdrucks gespart wird). Es gibt keinen Leistungsgewinn bei der Verwendung von SIMILAR TO jemals .

Und einfache Ausdrücke, die mit LIKE (~~) ausgeführt werden können, sind mit LIKE sowieso.

SIMILAR TO wird nur in PostgreSQL unterstützt, da es in frühen Entwürfen des SQL-Standards endete. Sie haben es immer noch nicht losgeworden. Aber es gibt Pläne, es zu entfernen und stattdessen Regexp-Übereinstimmungen einzuschließen – so hörte ich.

EXPLAIN ANALYZE enthüllt es. Versuchen Sie es einfach selbst mit einer beliebigen Tabelle!

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

Zeigt Folgendes an:

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

SIMILAR TO wurde mit einem regulären Ausdruck (~) umgeschrieben.

Ultimative Leistung für diesen speziellen Fall

Aber EXPLAIN ANALYZE enthüllt mehr. Versuchen Sie es mit dem oben genannten Index:

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

Enthüllt:

... -> 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)) 

Intern mit einem Index, der das Gebietsschema nicht kennt (text_pattern_ops oder mit dem Gebietsschema C) einfache linksverankerte Ausdrücke werden mit den folgenden Textmusteroperatoren neu geschrieben: ~>=~, ~<=~, ~>~, ~<~. Dies ist der Fall für ~, ~~ oder SIMILAR TO gleichermaßen.

Gleiches gilt für Indizes für varchar -Typen mit varchar_pattern_ops oder char mit bpchar_pattern_ops.

Also angewendet Für die ursprüngliche Frage ist dies der schnellstmögliche Weg :

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

Wenn Sie zufällig nach benachbarten Initialen suchen, können Sie dies weiter vereinfachen:

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

Der Gewinn gegenüber der einfachen Verwendung von ~ oder ~~ ist gering. Wenn die Leistung nicht Ihre vorrangige Anforderung ist, sollten Sie sich nur an die Standardoperatoren halten und zu dem gelangen, was Sie bereits in der Frage haben.

Kommentare

• Das OP hat ‚ keinen Index für den Namen, aber wissen Sie zufällig, dass ihre ursprüngliche Abfrage 2 Bereichssuchen und similar ein Scan?
• @MartinSmith: Ein schneller Test mit EXPLAIN ANALYZE zeigt 2 Bitmap-Index-Scans.Mehrere Bitmap-Index-Scans können ziemlich schnell kombiniert werden.
• Danke. Würde es also Meilen geben, wenn Sie die OR durch UNION ALL ersetzen oder name LIKE 'B%' durch name >= 'B' AND name <'C' in Postgres?
• @MartinSmith: UNION hat ‚ t gewonnen Aber ja, das Kombinieren der Bereiche zu einer WHERE -Klausel beschleunigt die Abfrage. Ich habe meiner Antwort mehr hinzugefügt. Natürlich müssen Sie Ihr Gebietsschema berücksichtigen. Die Suche nach Gebietsschema ist immer langsamer.
• @a_horse_with_no_name: Ich erwarte nicht. Die neuen Funktionen von pg_tgrm mit GIN-Indizes sind eine Wohltat für die generische Textsuche. Eine am Anfang verankerte Suche ist bereits schneller.

Wie wäre es, wenn Sie der Spalte eine hinzufügen Tabelle. Abhängig von Ihren tatsächlichen Anforderungen:

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

PostgreSQL unterstützt berechnete Spalten in Basistabellen a la SQL nicht Server , aber die neue Spalte kann über einen Trigger verwaltet werden. Diese neue Spalte wird natürlich indiziert.

Alternativ kann ein -Index für einen Ausdruck verwendet werden würde Ihnen das gleiche, billigere geben. Beispiel:

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

Abfragen, die dem Ausdruck in ihren Bedingungen entsprechen, können diesen Index verwenden.

Auf diese Weise wird der Leistungseinbruch beim Erstellen oder Ändern der Daten erzielt. Dies ist möglicherweise nur für eine Umgebung mit geringer Aktivität geeignet (dh viel weniger Schreibvorgänge als Lesevorgänge).

Sie können versuchen

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

Ich habe keine Ahnung, ob der obige oder Ihr ursprünglicher Ausdruck in Postgres sarkierbar ist oder nicht.

Wenn Sie den vorgeschlagenen Index erstellen, wäre es auch interessant zu hören, wie Dies ist vergleichbar mit den anderen Optionen.

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 

Kommentare

• Es hat funktioniert und ich habe Kosten von 1.19 wo ich 1.25 hatte. Vielen Dank!

Was ich in der Vergangenheit angesichts eines ähnlichen Leistungsproblems getan habe, ist: Erhöhen Sie das ASCII-Zeichen des letzten Buchstabens und führen Sie ein ZWISCHEN. Sie erhalten dann die beste Leistung für eine Teilmenge der LIKE-Funktionalität. Natürlich funktioniert es nur in bestimmten Situationen, aber bei extrem großen Datenmengen, bei denen Sie beispielsweise nach einem Namen suchen, wird die Leistung von miserabel zu akzeptabel.

Sehr alte Frage, aber ich habe eine andere schnelle Lösung für dieses Problem gefunden:

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

Seit Funktion ascii ( ) betrachtet nur das erste Zeichen der Zeichenfolge.

Kommentare

• Verwendet dies einen Index für (name)?

Zur Überprüfung der Initialen verwende ich häufig Casting für (mit doppelten Anführungszeichen). Es ist nicht portabel, aber sehr schnell. Intern wird der Text einfach entgast und das erste Zeichen zurückgegeben. Die Vergleichsoperationen „char“ sind sehr schnell, da der Typ eine feste Länge von 1 Byte hat:

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

Beachten Sie, dass das Casting in "char" schneller ist als die ascii() -Slution von @ Sole021, aber nicht UTF8-kompatibel ist (oder eine andere Codierung für Diese Angelegenheit) gibt einfach das erste Byte zurück und sollte daher nur in Fällen verwendet werden, in denen der Vergleich mit einfachen alten 7-Bit-ASCII-Zeichen erfolgt.

Es gibt zwei Methoden, die für solche Fälle noch nicht erwähnt wurden:

1. partieller (oder partitionierter – wenn manuell für den gesamten Bereich erstellt) Index – am nützlichsten, wenn Es ist nur eine Teilmenge der Daten erforderlich (z. B. während einer Wartung oder vorübergehend für eine Berichterstellung):

   CREATE INDEX ON spelers WHERE name LIKE "B%" 

2. Partitionieren der Tabelle selbst (Verwenden des ersten Zeichens als Partitionierungsschlüssel) – Diese Technik ist besonders würzig h Berücksichtigung in PostgreSQL 10+ (weniger schmerzhafte Partitionierung) und 11+ (Partitionsbereinigung während der Abfrageausführung).

Wenn die Daten in einer Tabelle sortiert sind, ist dies außerdem möglich Nutzen Sie die Verwendung des BRIN-Index (über dem ersten Zeichen).