Wie kann ich die Leistung einer Abfrage mit regulären Ausdrücken in PostgreSQL 8 verbessern?

Question

Ich führe eine Übereinstimmung mit einem regulären Ausdruck für eine Spalte des Typs character varying(256) in PostgreSQL 8.3.3. Die Spalte hat derzeit keine Indizes. Ich würde gerne die Leistung dieser Abfrage verbessern, wenn ich kann.

Hilft es, einen Index hinzuzufügen? Gibt es noch andere Möglichkeiten, die Leistung zu verbessern?

Answer 1

Sie können keinen Index erstellen, der jeden allgemeinen regulären Ausdruck beschleunigt. Wenn Sie jedoch einen oder eine begrenzte Anzahl von regulären Ausdrücken haben, die Sie abgleichen möchten, haben Sie einige Möglichkeiten.

Wie Paul Tomblin erwähnt, können Sie eine oder mehrere zusätzliche Spalten verwenden, um anzugeben, ob eine bestimmte Zeile mit diesem Regex oder diesen Regexen übereinstimmt oder nicht. Diese Spalte kann indiziert und effizient abgefragt werden.

Wenn Sie noch weiter gehen wollen, dieses Papier erörtert eine interessant klingende Technik für die Indizierung mit regulären Ausdrücken, bei der nach langen Teilzeichenfolgen in der Regex gesucht wird und die Indizierung darauf basiert, ob diese im Text vorhanden sind, um mögliche Übereinstimmungen zu erzeugen. Dadurch wird die Anzahl der Zeilen, die Sie tatsächlich mit der Regex abgleichen müssen, verringert. Sie könnten dies wahrscheinlich implementieren mit GiST Indizes zu erstellen, was allerdings einen nicht unerheblichen Arbeitsaufwand bedeuten würde.

Answer 2

Ein Index kann nichts mit einem regulären Ausdruck anfangen. Sie müssen einen vollständigen Tabellenscan durchführen.

Wenn es möglich ist, z. B. wenn Sie ständig dieselbe Regex abfragen, könnten Sie eine Spalte hinzufügen, die angibt, ob diese Zeile mit dieser Regex übereinstimmt, und diese bei Einfügungen und Aktualisierungen beibehalten.

Answer 3

Regex-Übereinstimmungen funktionieren nicht gut bei ziemlich großen Textspalten. Versuchen Sie, dies ohne Regex zu erreichen, oder führen Sie den Abgleich im Code durch, wenn der Datensatz nicht groß ist.

Answer 4

Dies kann einer der Fälle sein, in denen Sie RegEx nicht verwenden möchten. Wie sieht Ihr RegEx-Code aus? Vielleicht ist das eine Möglichkeit, ihn zu beschleunigen.

Answer 5

Wenn Sie eine begrenzte Anzahl von Regexen haben, die Sie abgleichen müssen, könnten Sie eine Tabelle mit dem Primärschlüssel Ihrer Tabelle und einem Feld erstellen, das angibt, ob es mit dem Regex übereinstimmt, die Sie bei einem Trigger aktualisieren und dann den Schlüssel Ihrer Tabelle in dieser Tabelle indizieren würden. Auf diese Weise wird eine kleine Verringerung der Aktualisierungs- und Einfügegeschwindigkeit gegen eine wahrscheinlich große Geschwindigkeitssteigerung bei der Auswahl eingetauscht.

Alternativ könnten Sie eine Funktion schreiben, die Ihr Feld mit diesem Regex vergleicht (oder sogar den Regex zusammen mit dem Feld, das Sie abgleichen wollen, an die Funktion übergeben), und dann einen funktionalen Index für Ihre Tabelle anhand dieser Funktion erstellen. Dies setzt ebenfalls einen festen Satz von Regexen voraus (aber Sie können auf diese Weise leichter neue Regex-Übereinstimmungen hinzufügen).

Wenn die Regex dynamisch aus Benutzereingaben erstellt wird, müssen Sie möglicherweise mit dem Tabellenscan leben oder die Benutzeranwendung ändern, um eine einfachere Suche zu erzeugen wie Feld wie 'Wert%' die einen Index für ein Feld ( %wert%' würde nicht).