Volltextsuche mit PostgreSQL

Coding , , ,


Viele Programmierer waren schon frustriert, weil eine
Volltextsuche benötigt wurde. Welche Engine ist die Beste? Warum braucht die
Engine einen eigenen Dienst? Wieso brauch ich dafür eine zweite Datenbank?


Seit PostgreSQL 8.3 ist tsearch2 direkt integriert. Bei tsearch2 handelt
es sich um eine Volltextsuche, welche direkt in PostgreSQL angesprochen wird.
Dadurch ist es möglich, die Informationen für die Suche direkt in die Tabellen einzubetten.

Tsearch2 liefert einige neue Datentypen und Funktionen, mit welchen gearbeitet werden
kann. Ein Datentyp ist z.B. tsvector, welchen man direkt testen kann…

SELECT 'Der erste String zum testen '::tsvector;
               tsvector                
---------------------------------------
 'Der' 'zum' 'erste' 'String' 'testen'
(1 ROW)

Als Ergebnis bekommen wir alle Wörter des Strings. Wörter, welche
doppelt im Text stehen, werden nur einmal angezeigt, was durchaus auch
Sinn macht.

SELECT 'Der erste String zum testen und testen '::tsvector;
               tsvector                   
---------------------------------------------
 'Der' 'und' 'zum' 'erste' 'String' 'testen'
(1 ROW)

Um das ganze mal etwas praktischer anzugehen, legen wir uns als erstes
eine Tabelle an, welche Texte enthalten soll:

CREATE TABLE messages (
	id int4,
	message text);

Und wir brauchen auch noch Inhalte…

INSERT INTO messages VALUES ('1', 'Dies ist eine Testnachricht');
INSERT INTO messages VALUES ('2', 'Dumdidum Nachricht');
INSERT INTO messages VALUES ('3', 'Ich bin ein Blindtext');

Jetzt haben wir ein Paar Daten in der Tabelle, aber noch keine Möglichkeit
einen Volltextindex zu speichern. Den Index können wir wie folgt anlegen:

ALTER TABLE messages ADD COLUMN idxFTI tsvector;

Um das Feld die neue Spalte initial zu füllen sollte man wie folgt vorgehen:

  • Tabelle updaten
  • vacuum full analyze ausführen
  • Index erstellen
  • nochmal vacuum full analyze ausführen

Als erstes erzeugen wir den Volltextindex aus der Spalte message und
führen ein Vacuum aus…

UPDATE messages SET idxFTI=to_tsvector('default',message);
VACUUM FULL ANALYZE;

Wenn z.B. mehrere Spalten in den Volltextindex aufgenommen werden sollen,
dann könnte man es wie folgt machen…

UPDATE messages
  SET idxFTI=to_tsvector('default',COALESCE(message,'') ||' '|| COALESCE(andereSpalte,''));
VACUUM FULL ANALYZE;

Jetzt erzeugen wir einen normalen Index auf der Spalte idxFTI…

CREATE INDEX idxFTI_idx ON messages USING gist(idxFTI);
VACUUM FULL ANALYZE;

Im Prinzip kann man schon jetzt per Volltext suchen. Eine Suche nach
dem Wort “ein” liefert z.B.

SELECT id, message FROM messages WHERE idxfti @@ 'ein'::tsquery;
    id |           message           
-------+-----------------------------
     1 | Dies ist eine Testnachricht
     3 | Ich bin ein Blindtext
(2 ROWS)

Wie aber erzeugen wir den Inhalt von idxFTI, wenn ein neuer
Datensatz eingetragen wird? Hier helfen Trigger und Stored Procedures…

CREATE OR REPLACE FUNCTION vectorupdate()
  RETURNS TRIGGER AS $vectorupdate$
BEGIN
  UPDATE messages
  SET idxFTI=to_tsvector('default',message) WHERE id=NEW.id;
 
  RETURN NEW;
END;
$vectorupdate$ LANGUAGE plpgsql;
 
CREATE TRIGGER tgr_vectorupdate AFTER INSERT OR UPDATE ON messages
  FOR EACH ROW EXECUTE PROCEDURE vectorupdate();

Wenn jetzt ein neuer Datensatz eingetragen bzw. verändert wird, dann
wir auch automatisch der Volltextindex aktualisiert.

Natürlich war dies nur ein kurzer Abriss von tsearch2, für den
vollen Einblick muss ich auf die
Dokumentation verweisen.


8 Kommentare zu “Volltextsuche mit PostgreSQL”

  1. Grandma Sagt:

    hiho,

    nettes Beispiel, funktioniert nur leider nicht. Befolgt man die Anleitung Schritt für Schritt, wird alles zwar mit Erfolg erstellt, die Suche ist jedoch ergebnislos. In der idxFTI-Spalte ist nach dem Aufbau des Index auch nichts drin, ich vermute das das nicht so sein soll, oder?

  2. Toast Sagt:

    Ich vermute, dass dann der Trigger nicht ausgeführt wird, welcher nach dem insert oder update anspringen sollte. Hast du mal manuell versucht die idxFTI Spalte zu erzeugen?

  3. Grandma Sagt:

    Nein, habe ich noch nicht… die spalte wird aber doch erzeugt, nur eben nicht gefüllt.

    bei der Anleitung hier knallt es schon wenn ich

    UPDATE messages SET idxFTI=to_tsvector(‘default’,message);

    mache, da die Konfiguration default nicht existiert. Wenn ich diese anlegen möchte, dann gibt es einen Fehler. Nur wenn ich diese irgendwie anders nenne, dann gehts, später wird die idxFTI eben nicht gefüllt.

  4. Grandma Sagt:

    PS: um den Trigger geht es auch noch nicht… nach dem zweiten Vacuum versuche ich zu selecten:

    SELECT id, message FROM messages WHERE idxfti @@ ‘ein’::tsquery;

    => liefert keine Ergebnisse

  5. Toast Sagt:

    Ich meinte eigentlich den Inhalt von idxFTI mit einem manuellen Update füllen…

    Probier mal…

    UPDATE messages SET idxFTI=to_tsvector(‘simple’,message);

  6. Grandma Sagt:

    das hat funktioniert, warum verstehe ich jedoch nicht wirklich. Es gibt wohl unterschiedliche Standard-Konfigurationen wie “default” und “simple” nach denen die idxFTI aufgebaut werden kann?

    PS: nun knallt es im Trigger nach einen neuen Insert.
    FEHLER: Grenze für Stacktiefe überschritten
    HINT: Erhöhen Sie den Konfigurationsparameter »maxstackdepth«, nachdem Sie sichergestellt haben, dass die Stacktiefenbegrenzung Ihrer Plattform ausreichend ist.

    FEHLER: Grenze für Stacktiefe überschritten
    HINT: Erhöhen Sie den Konfigurationsparameter »maxstackdepth«, nachdem Sie sichergestellt haben, dass die Stacktiefenbegrenzung Ihrer Plattform ausreichend ist.
    CONTEXT: SQL-Anweisung »UPDATE messages SET idxFTI=totsvector(‘simple’,message) WHERE id= $1 «
    PL/pgSQL function “vectorupdate” line 2 at SQL-Anweisung
    SQL-Anweisung »UPDATE messages SET idxFTI=to    tsvector(‘simple’,message) WHERE id= $1 «
    PL/pgSQL function “vectorupdate” line 2 at SQL-Anweisung
    SQL-Anweisung »UPDATE messages SET idxFTI=to_tsvector(‘simple’,message) WHERE id= $1 «

  7. Grandma Sagt:

    nach langer Suche funktioniert bei mir folgende Funktion:

    CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
    ON messages FOR EACH ROW EXECUTE PROCEDURE
    tsvectorupdatetrigger(idxfti, ‘pg_catalog.german’, message);

    wichtig ist vor ALLEM das Before und nicht das After insert da sonst eine Endlosschleife ausgelöst wird.

  8. Toast Sagt:

    Interessant…bei mir wird der Trigger nach dem Update ausgeführt, was prima funktioniert.
    In meiner DB mach ich das Update allerdings etwas anders, da ich ein Bytea Feld verwende. Ich wollte nämlich eine Volltextsuche in der DBMail Datenbank realisieren…

    UPDATE dbmailmessageblks
    SET idxFTI=to    tsvector(‘simple’,encode(messageblk, ‘escape’)) where messageblk
    idnr=NEW.messageblkidnr;

    Das Update kann nämlich auf die Nase fallen, wenn das Encoding nicht stimmt.

Kommentar hinzufügen