wake-up-neo.net

PostgreSQL LIKE-Abfrageleistungsvariationen

Ich habe ziemlich große Unterschiede bei den Antwortzeiten bezüglich LIKE-Abfragen auf eine bestimmte Tabelle in meiner Datenbank festgestellt. Manchmal bekomme ich Ergebnisse innerhalb von 200-400 ms (sehr akzeptabel), aber manchmal kann es bis zu 30 Sekunden dauern, bis Ergebnisse zurückgegeben werden.

Ich verstehe, dass LIKE-Abfragen sehr ressourcenintensiv sind, aber ich verstehe nicht, warum es so große Unterschiede in den Antwortzeiten gibt. Ich habe einen Btree-Index für das owner1-Feld erstellt, aber ich glaube nicht, dass es bei LIKE-Abfragen hilfreich ist. Hat jemand Ideen?

Beispiel-SQL:

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

Ich habe 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.
Zeilenanzahl der Tabelle: ca. 95.000.

86
Jason

FTS unterstützt LIKE nicht

Die zuvor akzeptierte Antwort ​​war falsch. Volltextsuche mit seinen Volltextindizes ist überhaupt nicht für den Operator LIKE, sondern hat einen eigenen Operatoren und funktioniert nicht für beliebige Zeichenfolgen. Es bearbeitet Wörter basierend auf Wörterbüchern und Stemming. Es unterstützt die Suche nach Präfixen für Wörter , jedoch nicht mit dem LIKE -Operator:

Trigrammindizes für LIKE

Installieren Sie das Zusatzmodul pg_trgm , das Operator-Klassen für GIN- und Gist-Trigramm-Indizes bereitstellt = zur Unterstützung aller LIKE und ILIKE Muster , nicht nur der links verankerten:

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

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:

Es wird angenommen, dass jedem Wort zwei Leerzeichen vorangestellt und ein Leerzeichen angehängt sind, wenn die Menge der in der Zeichenfolge enthaltenen Trigramme bestimmt wird.

Und Suchmuster mit weniger als 3 Buchstaben? Das Handbuch:

Beachten Sie, dass bei der Suche nach LIKE und nach regulären Ausdrücken ein Muster ohne extrahierbare Trigramme zu einem Vollindex-Scan verkommt.

Das bedeutet, dass Index-/Bitmap-Index-Scans weiterhin funktionieren (Abfragepläne für vorbereitete Anweisungen werden nicht unterbrochen). Sie werden jedoch keine bessere Leistung erzielen. In der Regel kein großer Verlust, da Zeichenfolgen mit 1 oder 2 Buchstaben kaum selektiv sind (mehr als einige Prozent der zugrunde liegenden Tabellenübereinstimmungen) und die Indexunterstützung die Leistung zunächst nicht verbessern würde, da ein vollständiger Tabellenscan schneller ist.


_text_pattern_ops_ für den Präfixabgleich

Für nur links verankerte Muster (kein führender Platzhalter) erhalten Sie das Optimum mit einem geeigneten Operator-Klasse für einen Btree-Index: _text_pattern_ops_ oder _varchar_pattern_ops_. Beide integrierten Funktionen von Standard-Postgres, kein zusätzliches Modul erforderlich. Ähnliche Leistung, aber viel kleinerer 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 Datenbank mit dem Gebietsschema 'C' ausführen möchten (effektiv no locale), dann wird ohnehin alles nach Bytereihenfolge sortiert und ein einfacher Btree-Index mit Standard-Operator-Klasse erledigt den Job.

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

260

Möglicherweise sind die schnellen verankerten Muster, bei denen die Groß- und Kleinschreibung beachtet werden muss, so dass Indizes verwendet werden können. d. h. es gibt keinen Platzhalter am Anfang der Übereinstimmungszeichenfolge, so dass der Executor einen Indexbereichsscan verwenden kann. ( Der relevante Kommentar in den Dokumenten ist hier ) Niederer und ähnlicher wird auch Ihre Fähigkeit zur Verwendung des Index verlieren, sofern Sie nicht ausdrücklich einen Index für diesen Zweck erstellen (siehe Funktionsindex ).

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

7
Ants Aasma

Sie können Wildspeed installieren, einen anderen Indextyp in PostgreSQL. Wildspeed funktioniert mit% Word% Wildcards, kein Problem. Der Nachteil ist die Größe des Index, dies kann groß sein, sehr groß.

4
Frank Heikens

Führen Sie die unten genannte Abfrage aus, um die Leistung der LIKE-Abfrage in postgresql ..__ zu verbessern. Erstellen Sie einen solchen Index für größere Tabellen:

CREATE INDEX <indexname> ON <tablename> USING btree (<fieldname> text_pattern_ops)
2
Noyal

Ich hatte kürzlich ein ähnliches Problem mit einer Tabelle mit 200000 Datensätzen und muss wiederholte LIKE-Abfragen durchführen. In meinem Fall wurde die Suchzeichenfolge behoben. Andere Felder variierten. Weil ich es geschafft habe:

SELECT owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%');

wie

CREATE INDEX ix_parcels ON parcels(position(lower('someones name') in lower(owner1)));

SELECT owner1 FROM parcels
WHERE position(lower('someones name') in lower(owner1)) > 0;

Ich war sehr erfreut, als die Abfragen schnell zurückkehrten und verifiziert wurden, dass der Index mit EXPLAIN ANALYZE verwendet wird:

 Bitmap Heap Scan on parcels  (cost=7.66..25.59 rows=453 width=32) (actual time=0.006..0.006 rows=0 loops=1)
   Recheck Cond: ("position"(lower(owner1), 'someones name'::text) > 0)
   ->  Bitmap Index Scan on ix_parcels  (cost=0.00..7.55 rows=453 width=0) (actual time=0.004..0.004 rows=0 loops=1)
         Index Cond: ("position"(lower(owner1), 'someones name'::text) > 0)
 Planning time: 0.075 ms
 Execution time: 0.025 ms
1
Stephen Quan

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

1) Ihre LIKE-Kriterien beginnen mit einem Platzhalter.

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

0
Asaph

Django ORM verwendet UPPER(text) für alle LIKE-Abfragen, um die Groß- und Kleinschreibung zu berücksichtigen. 

Das Hinzufügen eines Indexes zu UPPER(column::text) hat mein System im Gegensatz zu anderen Dingen stark beschleunigt.

Soweit führend%, ja, wird kein Index verwendet. In diesem Blog finden Sie eine großartige Erklärung:

https://use-the-index-luke.com/sql/where-clause/suche-for-ranges/like-performance-tuning

0
MrE