PostgreSQL LIKE Abfrageleistungsschwankungen

Ich habe eine große Variation in den Antwortzeiten in Bezug auf LIKE Abfragen zu einer bestimmten Tabelle in meiner database gesehen. Manchmal erhalte ich Ergebnisse innerhalb von 200-400 ms (sehr akzeptabel), aber zu anderen Zeiten kann es bis zu 30 Sekunden dauern, bis Ergebnisse zurückgegeben werden.

Ich verstehe, dass LIKE Abfragen sehr ressourcenintensiv sind, aber ich verstehe einfach nicht, warum es so große Unterschiede in den Antwortzeiten geben würde. Ich habe einen btree-Index auf dem owner1 Feld erstellt, aber ich denke nicht, dass es mit LIKE Abfragen hilft. Hat jemand Ideen?

Beispiel SQL:

 SELECT gid, owner1 FORM parcels WHERE owner1 ILIKE '%someones name%' LIMIT 10 

Ich habe es auch versucht:

 SELECT gid, owner1 FROM parcels WHERE lower(owner1) LIKE lower('%someones name%') LIMIT 10 

Und:

 SELECT gid, owner1 FROM parcels WHERE lower(owner1) LIKE lower('someones name%') LIMIT 10 

Mit ähnlichen Ergebnissen.
Anzahl der Tabellenzeilen: etwa 95.000.

FTS unterstützt LIKE

Die zuvor angenommene Antwort war falsch. Die Volltextsuche mit ihren Volltextindizes ist überhaupt nicht für den LIKE Operator, sie hat ihre eigenen Operatoren und funktioniert nicht für beliebige Strings. Es arbeitet mit Wörtern, die auf Wörterbüchern und Stammverweisen basieren. Es unterstützt die Präfix-Übereinstimmung für Wörter , aber nicht mit dem LIKE Operator:

  • Get partielle Übereinstimmung von der GIN indizierten TSVECTOR-Spalte

Trigramm-Indizes für LIKE

Installieren Sie das zusätzliche Modul pg_trgm , das Operatorklassen für GIN- und GiST-Trigramm-Indizes zur Unterstützung aller ILIKE und ILIKE Muster ILIKE , nicht nur für links verankerte Muster :

Beispielindex:

 CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops ); 

Oder:

 CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops ); 
  • Unterschied zwischen GiST und GIN-Index

Beispielabfrage:

 SELECT * FROM tbl WHERE col LIKE '%foo%'; -- leading wildcard SELECT * FROM tbl WHERE col ILIKE '%foo%'; -- works case insensitively as well 

Trigramme? Was ist mit kürzeren Saiten?

Wörter mit weniger als 3 Buchstaben in indizierten Werten funktionieren weiterhin. Das Handbuch:

Jedes Wort hat zwei vorangestellte Leerzeichen und ein Leerzeichen als Suffix, wenn die Menge der in der Zeichenfolge enthaltenen Trigramme festgelegt wird.

Und Suchmuster mit weniger als 3 Buchstaben? Das Handbuch:

LIKE Sie bei LIKE -Suchen und Suchen mit regulärem Ausdruck, dass ein Muster ohne extrahierbare Trigramme zu einem vollständigen Index-Scan degeneriert.

Das bedeutet, dass Index- / Bitmap-Index-Scans immer noch funktionieren (Abfragepläne für vorbereitete statementen werden nicht unterbrochen), es wird Ihnen einfach keine bessere performance bringen. In der Regel kein großer Verlust, da Zeichenketten mit 1 oder 2 Buchstaben kaum selektiv sind (mehr als ein paar Prozent der zugrunde liegenden Tabelle) und die Indexunterstützung die performance nicht verbessern würde, weil eine vollständige Tabellensuche schneller ist.

text_pattern_ops für das Präfix-Matching

Für nur links verankerte Muster (kein führender Platzhalter) erhalten Sie das Optimum mit einer geeigneten Operatorklasse für einen btree-Index: text_pattern_ops oder varchar_pattern_ops . Beide integrierten functionen von Postgres Standard, kein zusätzliches Modul benötigt. Ähnliche performance, aber viel kleiner Index.

Beispielindex:

 CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops ); 

Beispielabfrage:

 SELECT * FROM tbl WHERE col LIKE 'foo%' ; -- no leading wildcard 

Oder , wenn Sie Ihre database mit dem Gebietsschema “C” ausführen sollten (effektiv kein Gebietsschema), dann wird alles nach der Byte-Reihenfolge sortiert und ein einfacher btree-Index mit der Standard-Operator-class erledigt den Job.

Weitere Details, Erklärungen, Beispiele und Links in diesen verwandten Antworten auf dba.SE:

  • Mustererkennung mit LIKE, SIMILAR TO oder regulären Ausdrücken in PostgreSQL
  • Wie wird LIKE implementiert?
  • Ähnliche Strings mit PostgreSQL schnell finden

Möglicherweise sind die schnellen verankerten Muster mit Groß- / Kleinschreibung, wie sie Indizes verwenden können. dh am Anfang der Match-Zeichenfolge gibt es keine Platzhalter, sodass der Executor einen Indexbereichs-Scan verwenden kann. ( Der entsprechende Kommentar in der Dokumentation befindet sich hier ) Lower und ilike verlieren auch Ihre Fähigkeit, den Index zu verwenden, es sei denn, Sie erstellen speziell einen Index für diesen Zweck (siehe functionsindizes ).

Wenn Sie in der Mitte des Feldes nach einem String suchen möchten, sollten Sie sich Volltext- oder Trigramm-Indizes ansehen. Der erste ist im core von Postgres, der andere ist in den contrib-Modulen verfügbar.

Sie könnten Wildspeed installieren, eine andere Art von Index in PostgreSQL. Wildspeed funktioniert mit% word% Wildcards, kein Problem. Der Nachteil ist die Größe des Index, dieser kann groß sein, sehr groß.

Bitte führen Sie die unten angegebene Abfrage aus, um die LIKE-Abfrage-performance in postgresql zu verbessern. Erstellen Sie einen Index wie diesen für größere Tabellen:

 CREATE INDEX  ON  USING btree ( text_pattern_ops) 

Ihre ähnlichen Abfragen können die von Ihnen erstellten Indizes wahrscheinlich nicht verwenden, weil:

1) Ihre LIKE-Kriterien beginnen mit einem Platzhalter.

2) Sie haben eine function mit Ihren LIKE-Kriterien verwendet.