Mit der Volltextsuche von PostgreSQL® werden die Indizierung von Dokumenten in natürlicher Sprache und die Identifizierung von indizierten Dokumenten, die einer bestimmten Anfrage entsprechen, erleichtert. Übereinstimmende Dokumente können nach ihrer Relevanz für die Suchanfrage sortiert werden, und es können Auszüge aus den Dokumenten erstellt werden, in denen die übereinstimmenden Begriffe hervorgehoben sind. Es wird eine Reihe von SQL-Datentypen, -Operatoren und -Funktionen bereitgestellt, um die Indizierung, Abfrage und Einstufung von Dokumenten zu unterstützen.

PostgreSQL verwendet den Begriff Dokument für jedes Textfragment in natürlicher Sprache – im Wesentlichen Zeichenfolgen, die lesbare Wörter enthalten und durch Leerzeichen und Interpunktion getrennt sind. Dokumente sind oft als Text-Spalten gespeichert, können aber auch dynamisch erzeugt werden, z. B. durch Verkettung mehrerer Spalten (auch aus mehreren Tabellen).

-- concatenating multiple columns to form a document.
-- the table contains chapters from eBooks, one row per chapter
SELECT book_title || ' ' || book_author || ' ' || title || ' ' || body_html AS document
FROM chapters

Einige Beispiele für natürlichsprachliche Texte sind Blogbeiträge (wie der, den Sie gerade lesen), Bücher, Aufsätze, Benutzerkommentare, Forenbeiträge, Nachrichten in sozialen Medien, E-Mails, Newsgroup- und Chat-Nachrichten, Zeitungs- und Zeitschriftenartikel sowie Produktbeschreibungen in Katalogen. Diese Arten von Dokumenten sind typische Kandidaten für die Indizierung durch die Volltextsuche.

Beachten Sie, dass nicht alle lesbaren Zeichenfolgen natürliche Sprache enthalten. So sind beispielsweise Benutzernamen, Kennwörter und URLs oft lesbar, enthalten aber in der Regel keine natürliche Sprache.

Was gibt es an LIKE nicht zu mögen?

Der Datentyp Text verfügt über mehrere Operatoren zur Durchführung grundlegender Zeichenfolgenmuster-Abgleiche, insbesondere LIKE/ ILIKE (SQL-Platzhalterübereinstimmung unter Beachtung bzw. Nichtbeachtung von Groß- und Kleinschreibung), SIMILAR TO (SQL-Regex) und ~ (POSIX-Regex).

Zwar können mit diesen Operatoren sehr einfache Suchvorgänge durchgeführt werden, jedoch ist der Musterabgleich aufgrund verschiedener Einschränkungen nicht gerade ideal für die Implementierung nützlicher Suchvorgänge. Diesen Operatoren mangelt es an linguistischer Unterstützung, z. B. dem Verstehen der Textstruktur (einschließlich Zeichensetzung), dem Erkennen von Wortvarianten und Synonymen und dem Ignorieren häufig verwendeter Wörter. Sie sind zudem nicht in der Lage, die Ergebnisse nach ihrer Relevanz für die Abfrage zu ordnen, und vor allem können sie aufgrund der begrenzten Indizierungsunterstützung langsam sein.

Um uns mit einigen dieser Einschränkungen zu befassen, sehen wir uns ein paar typische Anforderungen an eine umfassende Suchfunktion einer Anwendung/Website an:

• Die Benutzer geben Suchbegriffe ein, die in Abfragen an die Datenbank umgewandelt werden. Anschließend werden dem Benutzer die Ergebnisse angezeigt.
• Die Suche sollte unabhängig von Groß- und Kleinschreibung erfolgen.
• Wörter in der Abfrage sollten mit Varianten (wie z. B. Suffixen) dieses Wortes im Dokument übereinstimmen, z. B. „cat“ sollte mit „cats“ übereinstimmen (und umgekehrt).
• Dokumente mit verwandten Wörtern/Synonymen sollten gefunden werden, z. B. Dokumente, die „feline“ oder „kitten“ enthalten, sollten bei der Suche nach „cat“ ebenfalls gefunden werden.
• Es kann nach Phrasen gesucht werden (oft in doppelten Anführungszeichen, z. B. „the fat black cat“).
• Die Benutzer können bestimmte Wörter markieren, die ausgeschlossen werden sollen (z. B., indem sie dem Wort einen Bindestrich voranstellen: cat -fat).
• Die Ergebnisse sind nach einer Art Relevanzmetrik geordnet, die sich auf die Suchanfrage des Nutzers bezieht. Wenn z. B. ein Dokument das Wort „cat“ (oder Varianten davon) mehrfach enthält und ein anderes Dokument „cat“ nur einmal erwähnt, wird das erste Dokument höher eingestuft.

Diese Anforderungen wurden absichtlich vage gehalten, da sie oft von den Details der Anwendung abhängen.

Für die folgenden Beispielabfragen wurden folgende Tabellendefinition und Daten verwendet:

CREATE TABLE example AS
  SELECT * FROM (
      VALUES ('the fat black cat chased after the rat'),
             ('one cat, two cats, many cats!'),
             ('the kitten and the dog played together'),
             ('that is one fine feline'),
             ('it is raining cats and dogs'),
             ('after eating the whole lasagne, he was a fat cat.'),
             ('don''t go into the catacombs after dark'),
             ('the bobcat has a spotted coat'),
             ('check the library catalog'),
             ('for a filesystem with deduplication look at zfs'),
             ('add one or more predicates to the query')
  ) AS t (document);

Würde die Anwendung Suchvorgänge mit dem ILIKE-Operator (der die Groß- und Kleinschreibung nicht berücksichtigt) durchführen, könnte die Abfrage eines Benutzers nach „cat“ in SQL wie folgt umgeschrieben werden: ILIKE '%cat%'. Damit würde jedes Dokument gefunden werden, das die Zeichenfolge „cat“ enthält. Diese Abfrage findet jedoch auch Dokumente mit Wörtern, die die Teilzeichenfolge „cat“ enthalten. Einige Übereinstimmungen, wie z. B. „cats“ wären relevant für die Anfrage. Andere, wie „cathode“, „catalog“, „deduplication“ und „predicate“, sind jedoch eher keine relevanten Ergebnisse.

-- this finds too many irrelevant results.
SELECT document, document ILIKE '%cat%' AS matches FROM example;

+-------------------------------------------------+-------+
|document                                         |matches|
+-------------------------------------------------+-------+
|the cat chased after the rat                     |true   |
|one cat, two cats, many cats!                    |true   |
|the kitten and the dog played together           |false  |
|that is one fine feline                          |false  |
|it is raining cats and dogs                      |true   |
|after eating the whole lasagne, he was a fat cat.|true   |
|don't go into the catacombs after dark           |true   |
|the bobcat has a spotted coat                    |true   |
|check the library catalog                        |true   |
|for a filesystem with deduplication look at zfs  |true   |
|add one or more predicates to the query          |true   |
+-------------------------------------------------+-------+

Der Versuch, die Abfrage mit ILIKE '% cat %' einzugrenzen, macht die Sache auch nicht besser. Wörter wie „catacombs“ und „deduplication“ sind zwar keine Treffer mehr. Aber auch wird „cats“ nicht mehr gefunden, und Sätze, die mit „cat“ beginnen und enden, sowie Satzzeichen außer Leerzeichen verursachen ebenfalls Probleme – „cat!“, „cat,“ und „cat.“ werden ignoriert.

-- too specific. doesn't even match 'cats'.
SELECT document, document ILIKE '% cat %' AS matches FROM example

+-------------------------------------------------+-------+
|document                                         |matches|
+-------------------------------------------------+-------+
|the cat chased after the rat                     |true   |
|one cat, two cats, many cats!                    |false  |
|the kitten and the dog played together           |false  |
|that is one fine feline                          |false  |
|it is raining cats and dogs                      |false  |
|after eating the whole lasagne, he was a fat cat.|false  |
|don't go into the catacombs after dark           |false  |
|the bobcat has a spotted coat                    |false  |
|check the library catalog                        |false  |
|for a filesystem with deduplication look at zfs  |false  |
|add one or more predicates to the query          |false  |
+-------------------------------------------------+-------+

ILIKE ist ebenfalls wenig hilfreich bei der Suche nach Synonymen oder verwandten Wörtern. Wenn die Anwendung für die Abfrage „cat“ Dokumente finden möchte, die „feline“, „kitten“ und „cat“ enthalten, müssen in SQL zusätzliche ILIKE-Operatoren mit OR verbunden werden. Einige Wörter können viele Varianten haben, was diesen Ansatz mühsam macht.

-- this still has all the other shortcomings to, such as '%cat%' matching too many results
SELECT document, (document ILIKE '%cat%' OR document ILIKE '%kitten%' OR document ILIKE '%feline%') AS matches FROM example;

Und schließlich gibt ILIKE nur einen booleschen Wert zurück, der angibt, ob eine Zeichenfolge mit dem Muster übereinstimmt. Er liefert keine Bewertungsmetrik, mit der die Ergebnisse nach Relevanz eingestuft werden können.

Ähnliche Probleme gibt es mit den Regex-Operatoren. Sie sind zwar leistungsfähiger als LIKE, und bestimmte Schwachpunkte von LIKE können mit kreativen Regex-Mustern behoben werden, aber sie führen noch immer einen Musterabgleich durch und haben die gleichen grundlegenden Einschränkungen.

Um die Einschränkungen dieser Operatoren zu umgehen, müsste man wahrscheinlich große Teile der in PostgreSQL integrierten Volltextsuche neu implementieren! Sehen wir uns stattdessen an, was PostgreSQL zu bieten hat.

Grundlagen der Volltextsuche

Dokumente für die Suche vorbereiten

Die Volltextsuche von PostgreSQL arbeitet nicht direkt mit Dokumenten, die mit dem Datentyp text gespeichert sind. Stattdessen müssen die Dokumente zunächst in den Datentyp tsvector, ein für die Suche optimiertes Format, konvertiert werden.

Um ein Dokument, das als text gespeichert ist, in tsvector zu konvertieren, sollte die Funktion to_tsvector verwendet werden:

to_tsvector([ config regconfig, ] document text) → tsvector

Die Funktion to_tsvector führt eine Reihe von Verarbeitungsschritten am Dokumententext durch. Auf oberster Ebene unterteilt to_tsvector das Dokument zunächst mit einem Parser in Wörter. Jedes Wort wird dann in einem oder mehreren Wörterbüchern nachgeschlagen. Ein Wörterbuch ist eine Zuordnung von Wörtern zu ihren normalisierten Formen. Diese normalisierten Formen werden Lexeme genannt. Wenn das Wort mit einem Wörterbucheintrag übereinstimmt, wird das Lexem dieses Eintrags dem tsvector hinzugefügt.

Der resultierende tsvector ist eine alphabetisch geordnete Sammlung von Lexemen, die im Quelldokument vorhanden sind. Jedes Lexem im tsvector enthält außerdem Positionsinformationen. Dies ist eine Liste von Ganzzahlen, die die Position jedes Quellworts angeben. Diese Positionsinformationen sind für die Suche nach Phrasen erforderlich und dienen dazu, Übereinstimmungen anhand ihrer Nähe in eine Rangfolge zu bringen.

Der Prozess der Normalisierung von Wörtern in Lexeme ist wörterbuchspezifisch, umfasst aber fast immer eine Umwandlung aller Großbuchstaben in Kleinbuchstaben und die Entfernung von Suffixen, um Wörter auf ihre Stammform zu reduzieren (aus „cats“ wird z. B. „cat“). Es können anwendungsspezifische Wörterbücher erstellt werden, mit denen der Normalisierungsprozess angepasst werden kann (z. B., um domänenspezifische Synonyme und Phrasen einem gemeinsamen Begriff zuzuordnen, wie „kitten“ und „feline“ zu „cat“). Dieser Normalisierungsprozess beschleunigt und vereinfacht das Auffinden aller Varianten eines Wortes, ohne dass jede Variante in der Abfrage angegeben werden muss.

Wörterbücher enthalten zudem in der Regel keine Einträge für sehr häufig gebrauchte Wörter (wie „der“ oder „und“). Diese allgemeinen Wörter werden Stoppwörter genannt und sind für die Suche nicht besonders nützlich. Indem sie übersprungen werden, wird der generierte tsvector kleiner, was die Leistung verbessert.

Die Wahl des Parsers und der Wörterbücher wird als Konfiguration bezeichnet und kann mit dem Parameter config für to_tsvector angegeben werden. Dieser Parameter ist optional, es empfiehlt sich aber, ihn immer anzugeben, um sicherzustellen, dass die richtige Konfiguration verwendet wird. Wird er weggelassen, kommt der globale Wert default_text_search_config zum Einsatz. Zwar gibt es verschiedene Standardkonfigurationen ( \dF in psql zeigt eine Liste an), aber meist sollte die Konfiguration angepasst werden.

In Abschnitt 20.11.2 und Abschnitt 12.1.3 der PostgreSQL-Dokumentation finden Sie genauere Details über die Option default_text_search_config und zur Konfiguration der Textsuche im Allgemeinen.

Hier ist ein Beispiel für den Aufruf von to_tsvector für ein Textfragment mithilfe der integrierten Konfiguration english:

SELECT to_tsvector('english', 'Hello world. ' ||
                              'The quick brown fox jumped over the lazy dog. ' ||
                              'Cats and kittens (and dogs too) love to chase mice. ' ||
                              'Hello world!')

Das Ergebnis ist der folgende tsvector:

'brown':5 'cat':12 'chase':20 'dog':11,16 'fox':6 'hello':1,22 'jump':7 'kitten':14 'lazi':10 'love':18 'mice':21 'quick':4 'world':2,23

Alle Lexeme wurden in Kleinbuchstaben umgewandelt, und mehrere Wörter wurden auf ihren Wortstamm reduziert („cats“ → „cat“). Die Satzzeichen („!“ und „.“) und Stoppwörter („the“, „and“ und „to“) wurden entfernt. Wörter, die mehrfach in der Eingabe vorkommen („dog“, „hello“ und „world“), haben mehr als einen Positionseintrag.

Die Erstellung von benutzerdefinierten Konfigurationen und Wörterbüchern ist nicht Gegenstand dieses Artikels.

Abfragen schreiben

Die Volltextsuche von PostgreSQL verwendet eine spezialisierte Mini-Abfragesprache (DSL), die das Schreiben komplexer und fortgeschrittener Abfragen ermöglicht, wobei boolesche Logik, Gruppierung und Phrasen-/Umgebungssuche kombiniert werden. Eine in dieser DSL geschriebene Abfrage wird mit dem Datentyp tsquery gespeichert. Abfragen, die als tsquery gespeichert sind, können hinsichtlich des tsvector eines Dokuments ausgewertet werden, um festzustellen, ob das Dokument der Abfrage entspricht.

Die DSL besteht aus einzelnen Lexemen, die durch Operatoren getrennt werden. Mithilfe von Klammern können Operatoren und Lexeme gruppiert werden, um Vorrang/Bindung zu erzwingen. In Abschnitt 8.11.2 der PostgreSQL-Dokumentation finden Sie eine detaillierte Dokumentation der tsquery-DSL.

 

Symbol	Operator	Arity	Precedence
&	Boolean AND	Binary	Lowest
|	Boolean OR	Binary	Lowest
!</	Boolean NOT	Unary	Highest
<N>	Followed By (where N is a positive number)	Binary	High
<->	Followed By (equivalent to <1>)	Binary	High

Boolesches UND prüft, ob beide Lexeme im Dokument vorhanden sind. Es wird eine Übereinstimmung gefunden, wenn beide Lexeme vorhanden sind.

Boolesches ODER prüft, ob eines oder beide Lexeme im Dokument vorhanden sind. Eine Übereinstimmung wird gefunden, wenn eines der beiden Lexeme vorhanden ist.

Boolesches NICHT prüft, ob das Lexem nicht im Dokument vorhanden ist. Eine Übereinstimmung wird gefunden, wenn das Lexem nicht vorhanden ist.

Followed by prüft, ob beide Lexeme im Dokument vorhanden sind und ob der Abstand zwischen den beiden Lexemen dem angegebenen Wert entspricht. Eine Übereinstimmung wird gefunden, wenn die beiden Lexeme existieren und genau N Wörter voneinander entfernt sind.

Es gibt eine Reihe von Funktionen zur Umwandlung von Abfragetext in eine tsquery: to_tsquery, plainto_tsquery, phraseto_tsquery und websearch_to_tsquery.

Jede dieser Funktionen führt eine Normalisierung und die Entfernung von Stoppwörtern am eingegebenen Abfragetext durch, und zwar mit dem gleichen Verfahren wie bei to_tsvector. Das Parsen des Abfragetextes ist von der Funktion abhängig.

to_tsquery

to_tsquery([ config regconfig, ] querytext text) → tsquery

to_tsquery versteht die vollständige DSL. Die Funktion erwartet, dass die Eingabe wohlgeformt ist und der Syntax von tsquery folgt. Alle Syntaxfehler werden zu SQL-Ausnahmen.

SELECT to_tsquery('english', 'The & Quick & Brown & Foxes')

'quick' & 'brown' & 'fox'

plainto_tsquery

plainto_tsquery([ config regconfig, ] querytext text) → tsquery

plainto_tsquery gibt eine Abfrage zurück, die prüft, ob alle Nicht-Stoppwort-Lexeme im Dokument vorhanden sind. Die Funktion analysiert den Abfragetext mit demselben Verfahren wie to_tsvector. Die resultierenden Lexeme werden mit dem booleschen Operator UND kombiniert.

SELECT plainto_tsquery('english', 'Jumped Over The Lazy Dogs')

'jump' & 'lazi' & 'dog'

phraseto_tsquery

phraseto_tsquery([ config regconfig, ] querytext text) → tsquery

phraseto_tsquery gibt eine Abfrage zurück, die prüft, ob die angegebene Phrase im Dokument existiert. Dies ist nützlich für die „Phrasensuche“. Es verhält sich ähnlich wie phraseto_tsquery mit dem Unterschied, dass die Lexeme mit dem Operator Followed By kombiniert werden. Wenn zwischen den Lexemen im Dokument Stoppwörter vorhanden sind, wird dies durch den Operator Followed By berücksichtigt.

SELECT phraseto_tsquery('english', 'Jumped Over The Lazy Dogs')

'jump' <3> 'lazi' <-> 'dog'

In diesem Beispiel werden „over“ und „the“ als Stoppwörter betrachtet. „lazy“ ist das dritte Wort nach „jumped“, daher hat der Followed By Operator zwischen den Lexemen „jump“ und „lazi“ einen Distanzwert von 3. „dogs“ folgt unmittelbar auf „lazy“, so dass der Operator Followed By zwischen den Lexemen „lazi“ und „dog“ einen Abstand von 1 hat.

websearch_to_tsquery

websearch_to_tsquery([ config regconfig, ] querytext text) → tsquery

websearch_to_tsquery funktioniert ein wenig anders als die anderen to_tsquery-Funktionen. Sie versteht eine alternative Syntax, bei der es sich um eine grundlegende Variante dessen handelt, was üblicherweise in Web-Suchmaschinen verfügbar ist. Sie kombiniert Teile von plainto_tsquery und phraseto_tsquery, wobei sie auch einige grundlegende Operatoren versteht.

Phrasen ohne Anführungszeichen werden in Lexeme umgewandelt, die mit dem Booleschen UND (wie in plainto_tsquery) verbunden sind. Phrasen mit doppelten Anführungszeichen werden in Lexeme umgewandelt, die mit Followed By (wie in phraseto_tsquery) verbunden sind. or und - (ein Bindestrich) werden in Boolesches ODER bzw. Boolesches NICHT umgewandelt.

SELECT query_text, websearch_to_tsquery(query_text)
FROM (VALUES
    ('the quick brown fox'),
    ('"the quick brown fox"'),
    ('brown or fox'),
    ('"quick brown" fox -jumped')
) AS t(query_text)

+-------------------------+-------------------------------------+
|query_text               |websearch_to_tsquery                 |
+-------------------------+-------------------------------------+
|the quick brown fox      |'quick' & 'brown' & 'fox'            |
|"the quick brown fox"    |'quick' <-> 'brown' <-> 'fox'        |
|brown or fox             |'brown' | 'fox'                      |
|"quick brown" fox -jumped|'quick' <-> 'brown' & 'fox' & !'jump'|
+-------------------------+-------------------------------------+

websearch_to_tsquery ist wahrscheinlich die am besten geeignete integrierte Funktion für die Erstellung einer tsquery aus Benutzereingaben.

Beachten Sie, dass „Phrasensuche“-Abfragen, die Followed By Operatoren verwenden, wie die von phraseto_tsquery und websearch_to_tsquery, manchmal unerwartete Ergebnisse liefern können. Näheres hierzu unter Ungenaue Phrasensuche weiter unten.

Abfragen auswerten

Eine tsquery kann hinsichtlich eines tsvector ausgewertet werden, um festzustellen, ob das Dokument der Abfrage entspricht. Die Auswertung erfolgt mithilfe des SQL-Operators @@. Dieser Operator nimmt eine tsquery und einen tsvector (in beliebiger Reihenfolge) als Operanden und gibt einen booleschen Wert zurück, der angibt, ob eine Übereinstimmung vorliegt.

tsvector @@ tsquery → boolean
tsquery @@ tsvector → boolean


Hier wird beispielsweise ein Dokument über eine tsquery mit einem einzelnem Lexem nach einem Wort durchsucht:

WITH input AS (
    SELECT to_tsvector('english', 'The quick brown fox jumped over the lazy dog.') AS document,
           to_tsquery('english', 'Dogs') AS query
)
SELECT document, query, document @@ query AS matches FROM input;

+-----------------------------------------------------+-----+-------+
|document                                             |query|matches|
+-----------------------------------------------------+-----+-------+
|'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2|'dog'|true   |
+-----------------------------------------------------+-----+-------+

In diesem Fall entspricht „dogs“ dem Wort „dog“, da „dogs“ in der Abfrage auf das Lexem „dog“ normalisiert ist, das auch im Dokument vorhanden ist.

Wie erwartet, wenn die tsquery nicht mit dem tsvector übereinstimmt, gibt der Operator @@ false zurück.

WITH input AS (
    SELECT to_tsvector('english', 'The quick brown fox jumped over the lazy dog.') AS document,
           websearch_to_tsquery('english', 'cats or kittens') AS query
)
SELECT document, query, document @@ query AS matches FROM input;

+-----------------------------------------------------+----------------+-------+
|document                                             |query           |matches|
+-----------------------------------------------------+----------------+-------+
|'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2|'cat' | 'kitten'|false  |
+-----------------------------------------------------+----------------+-------+

Um die Abfrage etwas komplexer zu gestalten, wird hier ein Dokument nach einer Phrase durchsucht:

WITH input AS (
    SELECT to_tsvector('english', 'The quick brown fox jumped over the lazy dog.') AS document,
           phraseto_tsquery('english', 'Jumped Over The Lazy') AS query
)
SELECT document, query, document @@ query AS matches FROM input;

+-----------------------------------------------------+-----------------+-------+
|document                                             |query            |matches|
+-----------------------------------------------------+-----------------+-------+
|'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2|'jump' <3> 'lazi'|true   |
+-----------------------------------------------------+-----------------+-------+

Und wie erwartet, wird eine Übereinstimmung gefunden.

Dokumente ablegen und indizieren

Bislang haben wir gesehen, wie man Dokumente in tsvector sowie Abfragetext in tsquery konvertiert und wie man sie miteinander kombiniert, um festzustellen, ob ein Dokument einer Abfrage entspricht. Die Beispiele waren jedoch nur auf ein einziges Dokument bezogen, und der Text des Dokuments wurde immer linear eingefügt.

Typischer bei der Volltextsuche ist es jedoch, eine ganze Tabelle von Dokumenten in einem Durchgang zu durchsuchen:

SELECT title FROM documents WHERE to_tsvector(body) @@ to_tsquery('...')

Ohne die entsprechenden Indizes wird nun to_tsvector bei jedem SELECT auf den Inhalt der Spalte angewendet. Die Ausführung in jeder Zeile einer großen Tabelle bei jeder Suche führt zu einer miserablen Leistung.

Einen Ausdrucksindex hinzuzufügen, ist eine Möglichkeit, die Leistung zu verbessern. Wie dies im Einzelnen erfolgt, soll jedoch dem Leser überlassen bleiben (siehe Abschnitt 12.2.2. Creating Indexes der PostgreSQL-Dokumentation). Wir betrachten den unten beschriebenen Ansatz, bei dem eine separate, generierte tsvector-Spalte verwendet wird, der eine Reihe von Vorteilen gegenüber einem reinen Ausdrucksindex-basierten Ansatz bietet, insbesondere eine bessere Leistung. Bei der Verwendung von Indizes muss to_tsvector möglicherweise erneut ausgeführt werden, um Indexübereinstimmungen zu überprüfen.

Da tsvector ein Datentyp ist, kann in einer Tabelle eine Spalte vom Typ tsvector erstellt werden, in der die Ergebnisse von to_tsvector gespeichert werden. Deshalb ist es üblich, den tsvector des Dokuments zusammen mit dem Original zu speichern:

CREATE TABLE documents (
    name text PRIMARY KEY,
    body text NOT NULL,
    tsv tsvector NOT NULL
);

Die tsvector-Spalte als GENERATED (PostgreSQL 12 und höher) zu markieren, vereinfacht die Dinge für Client-Anwendungen, wenn sie ein INSERT oder UPDATE durchführen. Bei älteren Versionen von PostgreSQL, die keine Unterstützung für generierte Spalten bieten, sind zwei Trigger-Funktionen enthalten, die dazu dienen, den tsvector aus anderen Spalten in einer Tabelle zu generieren (siehe Abschnitt 12.4.3. Triggers for Automatic Updates der PostgreSQL-Dokumentation). Natürlich kann auch eine benutzerdefinierte Trigger-Funktion verwendet werden. Die spezifischen Vor- und Nachteile von GENERATED-Spalten im Vergleich zu BEFORE INSERT- ODER UPDATE-Triggern sind nicht Gegenstand dieses Artikels. Wir sind jedoch der Meinung, dass generierte Spalten einfacher zu verstehen sind und verwendet werden sollten, solange die Einschränkungen der generierten Spalten die Verwendung von Trigger-Funktionen erzwingen.

-- using GENERATED
CREATE TABLE documents_generated (
    name text PRIMARY KEY,
    body text NOT NULL,
    tsv tsvector NOT NULL GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || body)) STORED
);

Anschließend kann ein Index über die tsvector-Spalte generiert werden, um Abfragen zu beschleunigen:

CREATE INDEX textsearch_idx ON documents USING GIN (tsv);

Sobald die tsvector-Spalte eingerichtet ist, kann sie ganz normal in Abfragen verwendet werden:

SELECT title FROM documents WHERE tsv @@ to_tsquery('...')

Und das war es auch schon mit der einfachen Textsuche. Zusammenfassend kann man sagen, dass es 3 Hauptschritte gibt:

1. Jedes Dokument wird in einen tsvector konvertiert, wofür to_tsvector verwendet wird. Normalerweise wird dies einmal bei INSERT/UPDATE durchgeführt, und der resultierende tsvector wird in einer Spalte neben dem Originaldokument gespeichert. Indizes für diese Spalte beschleunigen die Suche.
2. Der Abfragetext wird in eine tsquery mit einer der ...to_tsquery-Funktionen konvertiert.
3. Die tsquery wird hinsichtlich des tsvector des Dokuments ausgewertet, wofür der Operator @@ verwendet wird.

Einige Fallstricke

Text in tsvector/tsquery umwandeln

Es ist möglich, Text direkt in tsvector und tsquery umzuwandeln. Das Ergebnis ist allerdings nicht, was Sie erwarten!

Wie die meisten PostgreSQL-Datentypen haben tsvector und tsquery ein kompaktes binäres Format und eine textuelle Darstellung. Die Umwandlung von Text erwartet eine Zeichenfolge im textuellen Darstellungsformat von tsvector/ tsquery (siehe Abschnitt 8.11. Text Search Types der PostgreSQL-Dokumentation). Wenn die Zeichenfolge wohlgeformt ist, klappt die Umwandlung, aber es werden keine Stoppwörter entfernt, Wörter normalisiert oder andere Vorverarbeitungsschritte durchgeführt – es wird davon ausgegangen, dass die Lexeme in der Eingabe bereits normalisiert wurden.

WITH input AS (
    SELECT 'the QuIcK brown!! fox'::tsvector AS document,
           plainto_tsquery('quick brown fox') AS query
)
SELECT document, query, document @@ query AS matches FROM input

+-----------------------------+-------------------------+-------+
|document                     |query                    |matches|
+-----------------------------+-------------------------+-------+
|'QuIcK' 'brown!!' 'fox' 'the'|'quick' & 'brown' & 'fox'|false  |
+-----------------------------+-------------------------+-------+

Ungenaue Phrasensuche

Die Phrasensuche verhält sich möglicherweise nicht wie erwartet, wenn der Abfragetext Stoppwörter enthält.

Im folgenden Beispiel berücksichtigt die integrierte english-Konfiguration „over“ und „the“ als Stoppwörter, und phraseto_tsquery ersetzt sie durch den Operator Followed By mit Distanz 3 ( <3>). Folglich stimmt diese Abfrage mit jedem Dokument überein, in dem das Lexem „jump“, gefolgt von zwei beliebigen Wörtern, z. B. „lazi“, vorkommt:

WITH input AS (
    SELECT to_tsvector('english', 'The quick brown fox jumped past a lazy dog. ') AS document,
           phraseto_tsquery('english', 'Jumped Over The Lazy') AS query
)
SELECT document, query, document @@ query AS matches FROM input;

+--------------------------------------------------------------+-----------------+-------+
|document                                                      |query            |matches|
+--------------------------------------------------------------+-----------------+-------+
|'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'past':6 'quick':2|'jump' <3> 'lazi'|true   |
+--------------------------------------------------------------+-----------------+-------+

Hier passt „jumped over the lazy“ zu „jumped past a lazy dog“. Der Abgleich ist erfolgreich, weil der tsvector die Lexeme „jump“ und „lazi“ an den Positionen 5 bzw. 8 enthält – ein Abstand von 3 Wörtern.

Einschränkungen

Limit	Value	Behaviour
Size of each individual lexeme	2047 bytes	SQL ERROR raised
Total size of a tsvector. This includes lexemes and positional information. (type binary repr/on-disk size, for details see ts_type.h)	1048575 bytes (~1MiB)	SQL ERROR raised
Total number of lexemes per tsvector	264	unknown (size limit is reached first)
Total number of position entries per lexeme per tsvector	255	Position entries after 255th are discarded
Maximum value of a lexeme position entry	16383	Values greater than 16383 are rounded to 16383
Total number of lexemes and operators (nodes) per tsquery	32768	SQL ERROR raised

Bei der Verwendung der PostgreSQL-Volltextsuche gibt es eine Reihe von Einschränkungen zu beachten.

Solange Sie nicht versuchen, einen Artikel über das längste englische Wort der Welt zu indizieren (der chemische Name für Titin, der ~189819 Buchstaben lang ist!), ist es unwahrscheinlich, dass Sie die Grenze der Lexemgröße erreichen. Die Indizierung großer Textfragmente kann jedoch die Größenbeschränkung für tsvector von ~1 MiB überschreiten. Beachten Sie, dass diese Grenze auf der Größe der Binärdarstellung basiert (die zur Speicherung eines tsvector im Speicher und auf der Festplatte verwendet wird). Eine typische Abhilfemaßnahme ist, das Dokument in einzelne Abschnitte aufzuteilen (z. B. bei Büchern in Kapitel).

Wenn Ihre Anwendung die Phrasensuche verwenden soll, achten Sie sorgfältig auf die Beschränkungen von tsvector im Hinblick auf Lexempositionen. Es wird kein Fehler und keine Warnung ausgegeben, wenn einer dieser Grenzwerte bei der Generierung von tsvector erreicht wird. Ein tsvector kann nur 255 Positionseinträge pro Lexem speichern. Wenn ein Lexem mehr als 255-mal in einem Dokument vorkommt, werden nur die ersten 255 Positionen gespeichert. Jeder dieser Positionseinträge hat zudem einen Maximalwert, der auf 16.383 begrenzt ist. Wenn ein Lexem nach der 16.383. Position im Dokument auftritt, wird seine Position auf 16.383 gerundet. Beachten Sie, dass Stoppwörter zwar nicht im tsvector enthalten sind, die Lexempositionen jedoch beeinflussen.

Gesamtzahl der Lexeme pro tsvector

Im folgenden Beispiel wird das Wort „fox“ 300-mal im Quelldokument wiederholt, aber nur 255 Positionen werden im tsvector aufgezeichnet. ( unnest ist eine integrierte Hilfsfunktion, die einen tsvector in eine Tabelle umwandelt, sodass verschiedene Abfragen darauf durchgeführt werden können.)

WITH document AS (
    SELECT repeat('fox ', 300) as body
), lexemes AS (
    SELECT (unnest(to_tsvector(document.body))).* FROM document
), positions AS (
    SELECT lexeme, unnest(positions) AS position FROM lexemes
)
SELECT * FROM positions

+------+--------+
|lexeme|position|
+------+--------+
|fox   |1       |
|fox   |2       |
|fox   |3       |
|fox   |4       |

|fox   |254     |
|fox   |255     |
+------+--------+

Maximaler Wert eines Eintrags für eine Lexemposition

Hier wird ein Dokument, bestehend aus „hello“, gefolgt von 20.000 Kopien von „the“ und endend mit „world“, in einen tsvector umgewandelt. „the“ wurde als Lückenfüller gewählt, weil es ein Stoppwort ist. Wenngleich kein Lexem für „the“ im tsvector vorhanden ist, beeinflusst es dennoch die Lexempositionen von „hello“ und „world“. „world“ ist das 20.001. Wort im Dokument, doch da die maximale Positionswertgrenze erreicht ist, rundet PostgreSQL den Positionswert auf 16.383:

WITH document AS (
    SELECT 'hello ' || repeat('the ', 20000) || 'world' as body
), lexemes AS (
    SELECT (unnest(to_tsvector(document.body))).* FROM document
), positions AS (
    SELECT lexeme, unnest(positions) AS position FROM lexemes
)
SELECT * FROM positions

+------+--------+
|lexeme|position|
+------+--------+
|hello |1       |
|world |16383   |
+------+--------+

Beispiel für Einschränkungen der Phrasensuche und großer Dokumente

Die Phrasensuche arbeitet bei großen Textfragmenten möglicherweise unzuverlässig, da die Informationen zur Lexemposition entweder falsch sind (auf 16.383 gerundet) oder ganz fehlen, wie das folgende Beispiel zeigt:

SELECT name,
       to_tsvector(t.body) @@ phraseto_tsquery('hello world') AS phrase_matches,
       to_tsvector(t.body) @@ plainto_tsquery('hello world') AS plain_matches
FROM (
      VALUES ('phrase', 'hello world'),
             ('positions discarded', repeat('hello ', 300) || ' hello world'),
             ('positions rounded', repeat('the ', 20000) || ' hello world')
     ) AS t(name, body)

+-------------------+--------------+-------------+
|name               |phrase_matches|plain_matches|
+-------------------+--------------+-------------+
|phrase             |true          |true         |
|positions discarded|false         |true         |
|positions rounded  |false         |true         |
+-------------------+--------------+-------------+

In den beiden Tests positions discarded und positions rounded steht phrase_matches auf false, weil der Operator Followed By (wie von phraseto_tsquery generiert) den tsvector prüft und auf der Grundlage der verfügbaren Informationen fälschlicherweise schlussfolgert, dass es keinen Fall gibt, in dem ein „world“-Lexem direkt auf ein „hello“ folgt (d. h. eine Positionsdifferenz von 1 hat).

Der Phrasentest für positions discarded schlägt fehl, weil nur die ersten 255 Positionseinträge des Lexems „hello“ im tsvector behalten werden. Die letzte aufgezeichnete „hello“-Position ist 255, aber „world“ hat eine Position von 301, was eine Differenz von 46 ergibt.

Der Phrasentest positions rounded schlägt fehl, weil die 20.000 Instanzen des Stoppworts „the“ die Positionswerte der Lexeme „hello“ und „world“ beeinflussen. Beide Positionswerte überschreiten 16.383 und werden auf diesen Wert gerundet, sodass sie eine Differenz von 0 haben.

Testen Sie die PostgreSQL®-Volltextsuche

Die in PostgreSQL integrierten Volltextsuchfunktionen sind mehr als geeignet, um grundlegende Textsuchfunktionen für eine Anwendung zur Verfügung zu stellen. Entwickler greifen oft zuerst zu schwergewichtigen Lösungen, wie OpenSearch. Wenn Sie PostgreSQL bereits als Datenspeicher für Ihre Anwendung verwenden, sollten Sie zunächst die integrierte Textsuche ausprobieren, bevor Sie weitere Abhängigkeiten zu Ihrer Anwendung hinzufügen. Es ist einfacher, die indizierten Dokumente mit den Originalen zu synchronisieren, wenn sie zusammen gespeichert sind, und generierte Spalten vereinfachen dies noch weiter. Volltext-Suchabfragen können mit anderen SQL-Operatoren kombiniert werden, was leistungsstarke Abfrage-, Filter- und Sortierfunktionen innerhalb einer Datenbank ermöglicht.

Wenn Sie die Volltextsuche von PostgreSQL ausprobieren möchten, dann schauen Sie sich das verwaltete PostgreSQL von Instaclustr an, das volle Unterstützung für die Volltextsuche bietet. Mit unserer kostenlosen Testversion können Sie es ohne Kreditkarte noch heute ausprobieren. Besuchen Sie einfach unsere Konsole.

Der Orininalartikel stammt von Adam Zegelin und wurde auf Instaclustr.com am 9. November 2022 veröffentlicht.

Kategorien:	credativ® Inside

---

---