web-development-kb-eu.site

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

Ich musste eine einfache Abfrage schreiben, in der ich nach dem Namen der Leute suchte, die mit einem B oder einem D beginnen:

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

Ich habe mich gefragt, ob es eine Möglichkeit gibt, dies umzuschreiben, um performanter zu werden. Also kann ich or und/oder like vermeiden?

postgresqlperformanceindexregular-expressionpattern-matching
103
Lucas Kauffman

Ihre Anfrage 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 Sie wirklich die Syntax verkürzen möchten, verwenden Sie einen regulären Ausdruck mit Zweigen:

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

Oder etwas schneller mit einer Zeichenklasse:

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

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

Lesen Sie die Grundlagen zu Mustervergleich im Handbuch .

Index für überlegene Leistung

Wenn Sie sich mit der Leistung befassen, erstellen Sie einen Index wie diesen für größere Tabellen:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops);

Beschleunigt diese Art der Abfrage um Größenordnungen. Besondere Überlegungen gelten für die länderspezifische Sortierreihenfolge. Lesen Sie mehr über Operatorklassen im Handbuch . Wenn Sie das Standardgebietsschema "C" verwenden (die meisten Leute tun dies nicht), reicht ein einfacher Index (mit Standardoperatorklasse) aus.

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. Aber 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 ohne Berücksichtigung der Groß- und Kleinschreibung.
    pg_trgm erweitert die Indexunterstützung für beide.

  • ~ (Übereinstimmung mit regulären Ausdrücken) ist mächtig, aber komplexer und kann für alles andere als einfach langsam sein Ausdrücke.

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

  • % ist der "Ähnlichkeits" -Operator, der vom zusätzlichen Modul pg_trgm bereitgestellt wird. Siehe unten.

  • @@ ist der Textsuchoperator. Siehe unten.

pg_trgm - Trigrammabgleich

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

Details, Beispiel und Links:

pg_trgm Bietet auch diese Operatoren :

  • % - der Operator "Ähnlichkeit"
  • <% (Kommutator: %>) - der Operator "Word_similarity" in Postgres 9.6 oder höher
  • <<% (Kommutator: %>>) - der Operator "strict_Word_similarity" 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:

Sowie Phrasensuche seit Postgres 9.6:

Betrachten Sie das Einführung im Handbuch und das Ü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 können verschiedene Implementierungen der Funktion levenshtein() instrumentell 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 schneller.

SIMILAR TO Wird nur in PostgreSQL unterstützt, da es in frühen Entwürfen des SQL-Standards landete. 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 Zeigt es. Versuchen Sie es einfach mit jedem Tisch selbst!

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

Enthüllt:

...  
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 werden mit einem Index, der das Gebietsschema nicht kennt (text_pattern_ops Oder das Gebietsschema C verwendet), einfache linksverankerte Ausdrücke mit den folgenden Textmusteroperatoren neu geschrieben: ~>=~, ~<=~, ~>~, ~<~. Dies gilt gleichermaßen für ~, ~~ Oder SIMILAR TO.

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

Auf die ursprüngliche Frage angewendet 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 natürlich 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 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.

171
Erwin Brandstetter

Wie wäre es, wenn Sie der Tabelle eine Spalte hinzufügen? 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 nicht berechnete Spalten in Basistabellen a la SQL Server , aber die neue Spalte kann über den Trigger verwaltet werden. Offensichtlich würde diese neue Spalte indiziert.

Alternativ würde ein Index für einen Ausdruck Ihnen das gleiche geben, billiger. Z.B.:

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 erzielt, wenn die Daten erstellt oder geändert werden. Dies ist möglicherweise nur für eine Umgebung mit geringer Aktivität geeignet (d. H. Viel weniger Schreibvorgänge als Lesevorgänge).

11
onedaywhen

Sie könnten 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 sind oder nicht.

Wenn Sie den vorgeschlagenen Index erstellen, würde es Sie auch interessieren, wie dieser mit den anderen Optionen verglichen wird.

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
8
Martin Smith

Zur Überprüfung der Initialen verwende ich häufig das Casting in "char" (Mit doppelten Anführungszeichen). Es ist nicht tragbar, 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) und einfach das erste Byte zurückgibt Nur in Fällen verwenden, in denen der Vergleich mit einfachen alten 7-Bit-Zeichen ASCII Zeichen) erfolgt.

2
Ziggy Crueltyfree Zeitgeister

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

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

2
Sole021

Was ich in der Vergangenheit angesichts eines ähnlichen Leistungsproblems getan habe, ist, das Zeichen ASCII des letzten Buchstabens) zu erhöhen und ZWISCHEN zu machen. Sie erhalten dann die beste Leistung für eine Teilmenge 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.

2
Mel Padden

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

  1. teilindex (oder partitioniert - wenn manuell für den gesamten Bereich erstellt) - am nützlichsten, wenn nur eine Teilmenge der Daten erforderlich ist (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 (unter Verwendung des ersten Zeichens als Partitionierungsschlüssel) - Diese Technik ist besonders in PostgreSQL 10+ (weniger schmerzhafte Partitionierung) und 11+ (Partitionsbereinigung während der Abfrageausführung) zu berücksichtigen.

Wenn die Daten in einer Tabelle sortiert sind, kann außerdem die Verwendung von BRIN-Index (über dem ersten Zeichen) genutzt werden.

1
Tomasz Pala