Esprit & Schmackes

Voll im Text

Einführung in die Volltextsuche mit PostgreSQL 9.1 so um die 841 Wörter

Die Volltextsuche baut auf zwei essenziellen Datentypen auf:

 

tsvector ist eine sortierte Liste von eindeutigen Bausteinen einer festgelegten Sprache. Aus

SELECT to_tsvector('german', 'ich sehe dumme menschen');

wird

'dumm':3 'mensch':4 'seh':2

und aus

SELECT to_tsvector('german', 'wenn Fliegen über Fliegen fliegen, fliegen Fliegen über Fliegen.');

wird dann

'flieg':2,4,5,6,7,9

Der übergebene Text wird an den Sonderzeichen auseinandergenommen, die Begriffe werden auf ihren Wortstamm reduziert und durch die Stopword-Liste gekübelt, wobei jeweils die Wörterbücher der übergebenen Sprache oder standardmäßig die für Englisch (bzw. der eingestellen Standardsprache, was in den meisten Fällen auf's Selbe hinausläuft) genutzt werden. Und im Gegensatz zu MySQL sind die Stopword-Listen bei PostgreSQL doch, ja, ordentlich, sodass beim zweiten Query nur ein jämmerlicher Wortstamm übrig bleibt.

Die nachfolgenden Ziffern geben die Positionen der Vorkommnisse im String zurück.

 

Der zweite Typ, tsquery, stellt die Suchbegriffe inklusive Operatoren dar. Operatoren sind die üblichen Verdächtigen AND, OR und NOT sowie weitere. Erwähnenswert ist noch der Match-Operator @@, der boolean ist und auf Treffer prüft.

 

Bauen wir uns also mal eine Tabelle mit Volltextsuche.

CREATE TABLE article (
    id serial,
    title character varying(250) NOT NULL,
    description text,
    content text NOT NULL
);
INSERT INTO article (title, description, content) VALUES ('dies ist nur ein test', 'gehen Sie weiter', 'was im Titel steht');

Wir wollen nun eine Volltextsuche über die Spalten title, description und content erstellen. Dazu benötigen wir zuerst eine weitere Spalte vom Typ tsvector, in der nachher die Tokens der Inhalte landen.

ALTER TABLE article ADD COLUMN fts tsvector;

Des Weiteren muss für den vorhandenen Datensatz die tsvector-Spalte befüllt werden.

UPDATE article SET fts = to_tsvector('german', title || ' ' || description || ' ' || content);

Hierbei werden die Inhalte aus title, description und content per Concetenator als einzelner String an die Funktion übergeben, damit über alle drei Spalten gesucht werden kann.

Die Spalte fts hat danach folgenden Inhalt

'geh':6 'steht':12 'test':5 'titel':11

und könnte theoretisch schon für eine Suchabfrage herhalten. Was jetzt aber noch fehlt, ist ein automatisiertes Update, und natürlich ein Index auf fts.

 

Es gibt derer zwei für Volltextspalten: GIN und GIST.

  • GIN bedeutet Generalized Inverted Index.
  • GIN-Indizes können nur auf Spalten des Typs tsvector gesetzt werden.
  • GIN-Indizes sind größer, was das Speichervolumen angeht, und langsamer im Aufbau, also bei UPDATEs.
  • GIN-Indizes sind schneller bei der Abfrage, also der eigentlichen Suche.
  • GIST bedeutet Generalized Search Tree.
  • GIST-Indizes können auf Spalten der Typen tsvector oder tsquery gesetzt werden.
  • + jeweils die Gegenteile von GIN.
  • + was hier noch so steht.

 

Da ich auf die Performance des Abfragens deutlich mehr Wert lege als auf die beim Eintragen wähle ich also GIN.

CREATE INDEX fts_idx ON article USING GIN(fts);

Zum Schluss noch den Trigger setzen, damit neue Inhalte automatisch indexiert werden:

CREATE TRIGGER fts_update 
BEFORE UPDATE OR INSERT ON article 
FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger(fts, 'pg_catalog.german', title, description, content);

tsvector_update_trigger ist eine built-in Procedure, man braucht also nichts Eigenes zu definieren (was zum Beispiel bei multilingualen Inhalten nötig würde - man müsste in der Procedure oder im Trigger unterscheiden, zu welcher Sprache der Datensatz gehört).

Zum Test der Suche noch einen weiteren Datensatz einfügen:

INSERT INTO article (title, description, content) VALUES ('lorem ipsum', 'blablubb', 'ist es nicht furchtbar, wenn einem absolut nichts Sinnstiftendes einfällt?');

 

Um die Suche durchzuführen müssen die Suchbedingungen nach ts_query konvertiert werden. Das folgende Beispiel liefert alle Titel zurück, deren indexierte Wörter den Begriff blablubb beinhalten:

SELECT title FROM article WHERE fts @@ to_tsquery('german', 'blablubb');
title
lorem ipsum

 

Auch eine Teil-Token-Suche ist möglich, wie bei MySQL aber nur mit Präfixen durchführbar.

SELECT title FROM article WHERE fts @@ to_tsquery('german', 'furcht:*');

liefert uns also einen Treffer, während

SELECT title FROM article WHERE fts @@ to_tsquery('german', '*:bar')

uns nur einen Syntaxfehler um die Ohren haut. Eine absolute Trefferwahrscheinlich für alle möglichen Begriffunarten (sprich, eine Alternative zu Like beziehungsweise iLIKE) sind also mit Volltext nicht möglich, da man nur mit dem arbeiten kann, was als Wortstamm übrig bleibt (das gleiche Problem wie bei MySQL). Ob man zur Beruhigung des perfektionistischen Egos dann zumindest alle Begriffe als Präfix sucht, bleibt jedem selbst überlassen. Hier spielt dann aber auch die Performance wieder rein: Präfix-Suchabfragen sind immer langsamer als normale Suchen. Im Test immerhin 0,003 Millisekunden. Das ist freilich geschissen; bei zwölf Millionen Datensätzen und komplizierteren Suchmustern sähe das Ganze dann schon wieder anders aus.

 

Die OR-verknüpfte Suche erreicht man durch ein | im tsquery:

SELECT title FROM article WHERE fts @@ to_tsquery('german', 'absolut | titel')
title
dies ist nur ein test
lorem ipsum

 

Zum UND-verknüpften Suchen ersetze man | mit &.

Für alles Weitere seien die PostgreSQL-Dokumentation und die Funktionsübersicht ans Herz gelegt.

Kommentare

was zu melden?