Warum sind PostgreSQL-LIKE-Abfragen so langsam und wie kann ich sie schneller machen?

PostgreSQL-LIKE-Abfragen optimieren: Leistungsprobleme beheben

Die LIKE-Abfragen von PostgreSQL, die für den Teilstring-Abgleich verwendet werden, weisen häufig eine unvorhersehbare Leistung auf, die von Millisekunden bis hin zu Sekunden reicht. In diesem Artikel werden die Ursachen und wirksamen Optimierungsstrategien untersucht.

Leistungsvariabilität verstehen

Die Leistungsinkonsistenzen sind auf den inhärenten Ressourcenbedarf von LIKE Abfragen zurückzuführen. Im Gegensatz zu Gleichheitsprüfungen, die Indizes effektiv nutzen, erfordern LIKE-Abfragen normalerweise vollständige Tabellenscans.

Indexeinschränkungen

Obwohl B-Tree-Indizes für die Leistung von entscheidender Bedeutung sind, sind sie grundsätzlich nicht für LIKE Operationen geeignet. Ein Index für eine Spalte (z. B. owner1) verbessert exakte Übereinstimmungen (SELECT * FROM parcels WHERE owner1 = 'John Doe'), bietet aber nur begrenzte Vorteile für LIKE-Abfragen.

Techniken zur Leistungssteigerung

Mehrere Techniken können die LIKEAbfrageleistung erheblich verbessern:

Volltextsuche (FTS)

PostgreSQLs FTS ermöglicht eine effiziente Textsuche. Allerdings unterstützt es den Operator LIKE nicht direkt und arbeitet mit Wörtern, nicht mit beliebigen Teilzeichenfolgen.

Präfix-Matching-Optimierung

Für Muster ohne führende Platzhalter (z. B. 'foo%') verwenden Sie Operatorklassen wie text_pattern_ops oder varchar_pattern_ops mit einem B-Tree-Index. Diese optimieren den linksverankerten Mustervergleich.

Trigramm-Indizes für flexible Zuordnung

Die pg_trgm-Erweiterung bietet in Verbindung mit GIN- oder GiST-Indizes eine Trigramm-Indizierung. Dies unterstützt alle LIKE- und ILIKE-Muster, unabhängig von der Platzhalterposition.

Weitere Überlegungen

• Stringlänge: Indizierte Wörter, die kürzer als drei Zeichen sind, funktionieren, aber Muster mit weniger als drei Zeichen lösen möglicherweise trotzdem vollständige Indexscans aus.
• PostgreSQL 11 und höher: PostgreSQL 11 führte den ^@-Operator und die starts_with()-Funktion für einen effizienten Präfixabgleich mit SP-GiST-Indizes ein.
• Sortierung und Indizierung: Durch die Verwendung von COLLATE "C" mit Indizes oder Operatorklassen, die auf bestimmte Sortierungen zugeschnitten sind, können der Präfixabgleich und die LIKE Abfrageleistung für bestimmte Szenarien optimiert werden.

Das obige ist der detaillierte Inhalt vonWarum sind PostgreSQL-LIKE-Abfragen so langsam und wie kann ich sie schneller machen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!