PostgreSQL Volltextsuche

Einführung

In diesem Lab lernen Sie, wie Sie die Volltextsuche in PostgreSQL mit TSVECTOR implementieren. Sie beginnen mit der Erstellung einer articles-Tabelle und dem Hinzufügen von Beispieldaten. Anschließend fügen Sie eine search_vector-Spalte hinzu und erstellen einen TSVECTOR-Index darauf. Abschließend richten Sie einen Trigger ein, um die search_vector-Spalte automatisch zu aktualisieren, wenn die Spalten title oder content geändert werden. Dies ermöglicht Ihnen die Durchführung effizienter Volltextsuchen.

Skills Graph

Einrichten der Artikeltabelle

In diesem Schritt erstellen Sie die articles-Tabelle und fügen einige Beispieldaten ein. Diese Tabelle wird verwendet, um die Volltextsuchfunktionen in PostgreSQL zu demonstrieren.

Verbinden Sie sich zunächst als Benutzer postgres mit der PostgreSQL-Datenbank, indem Sie den folgenden Befehl in Ihrem Terminal ausführen:

sudo -u postgres psql

Erstellen Sie nun die articles-Tabelle mit dem folgenden SQL-Befehl:

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

Dieser Befehl erstellt eine Tabelle namens articles mit drei Spalten: id, title und content. Die Spalte id ist ein automatisch inkrementierender Primärschlüssel (primary key).

Fügen Sie als Nächstes einige Beispieldaten in die articles-Tabelle ein:

INSERT INTO articles (title, content) VALUES
('PostgreSQL Tutorial', 'This is a comprehensive tutorial on PostgreSQL.'),
('Full Text Search in PostgreSQL', 'Learn how to implement full text search using TSVECTOR in PostgreSQL.'),
('PostgreSQL Performance Tuning', 'Tips and tricks to improve the performance of your PostgreSQL database.');

Dieser Befehl fügt drei Zeilen in die articles-Tabelle ein, jede mit einem anderen Titel und Inhalt.

Sie können überprüfen, ob die Daten korrekt eingefügt wurden, indem Sie die folgende Abfrage ausführen:

SELECT * FROM articles;

Sie sollten die drei Zeilen sehen, die Sie gerade eingefügt haben.

 id |             title              |                                       content
----+----------------------------------+-----------------------------------------------------------------------
  1 | PostgreSQL Tutorial            | This is a comprehensive tutorial on PostgreSQL.
  2 | Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
  3 | PostgreSQL Performance Tuning  | Tips and tricks to improve the performance of your PostgreSQL database.
(3 rows)

Hinzufügen der Spalte search_vector und Erstellen eines GIN-Index

In diesem Schritt fügen Sie die Spalte search_vector zur articles-Tabelle hinzu und erstellen einen GIN-Index darauf. Die Spalte search_vector speichert die TSVECTOR-Darstellung der Spalten title und content, und der GIN-Index beschleunigt die Volltextsuche.

Fügen Sie zunächst die Spalte search_vector zur articles-Tabelle hinzu:

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

Aktualisieren Sie als Nächstes die Spalte search_vector mit der TSVECTOR-Darstellung der Spalten title und content. Sie können die Funktion to_tsvector verwenden, um Text in einen TSVECTOR zu konvertieren.

UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || content);

Im obigen Befehl gibt 'english' die Sprachkonfiguration an, die für die Textverarbeitung verwendet werden soll. Der Operator || verkettet die Spalten title und content mit einem Leerzeichen dazwischen.

Erstellen Sie nun einen GIN-Index (Generalized Inverted Index) für die Spalte search_vector:

CREATE INDEX articles_search_vector_idx ON articles USING GIN (search_vector);

GIN (Generalized Inverted Index) ist ein Indextyp, der sich gut für die Indizierung von TSVECTOR-Spalten eignet. Dieser Index ermöglicht es PostgreSQL, schnell Dokumente zu finden, die einer bestimmten Suchanfrage entsprechen.

Sie können überprüfen, ob der Index erstellt wurde, indem Sie den folgenden Befehl ausführen:

\d articles

Sie sollten den Index articles_search_vector_idx in der Ausgabe sehen.

                                     Table "public.articles"
      Column      |          Type          | Collation | Nullable |                 Default
------------------+------------------------+-----------+----------+-----------------------------------------
 id               | integer                |           | not null | nextval('articles_id_seq'::regclass)
 title            | character varying(255) |           |          |
 content          | text                   |           |          |
 search_vector    | tsvector               |           |          |
Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)
    "articles_search_vector_idx" gin (search_vector)

Durchführen von Volltextsuchen mit TSQUERY

Nachdem Sie die Spalte search_vector und den GIN-Index eingerichtet haben, können Sie Volltextsuchen mit den TSQUERY-Operatoren durchführen.

PostgreSQL bietet die Funktion to_tsquery, um eine Textzeichenfolge in ein TSQUERY-Objekt zu konvertieren. Sie können dann den Operator @@ verwenden, um die TSQUERY mit der Spalte search_vector abzugleichen.

Um beispielsweise nach Artikeln zu suchen, die sowohl "PostgreSQL" als auch "tutorial" enthalten, können Sie die folgende Abfrage verwenden:

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & tutorial');

Der Operator & in der Funktion to_tsquery gibt an, dass beide Begriffe in der search_vector vorhanden sein müssen.

Sie können auch den Operator | verwenden, um nach Artikeln zu suchen, die entweder "PostgreSQL" oder "tutorial" enthalten:

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL | tutorial');

Um nach Artikeln zu suchen, die den Ausdruck "full text search" enthalten, können Sie die folgende Abfrage verwenden:

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', '''full text search''');

Das Einschließen des Ausdrucks in einfache Anführungszeichen (single quotes) weist to_tsquery an, ihn als einen einzelnen Ausdruck zu behandeln.

Experimentieren Sie mit verschiedenen Suchabfragen, um zu sehen, wie die TSQUERY-Operatoren funktionieren.

Ergebnisse nach Relevanz ordnen (Ranking)

PostgreSQL bietet die Funktion ts_rank, um Suchergebnisse nach Relevanz zu ordnen (ranken). Die Funktion ts_rank nimmt den search_vector und die TSQUERY als Eingabe entgegen und gibt einen Rangwert (rank value) zurück.

Um die Suchergebnisse nach Relevanz zu ordnen, können Sie die folgende Abfrage verwenden:

SELECT title, ts_rank(search_vector, to_tsquery('english', 'PostgreSQL')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'PostgreSQL')
ORDER BY rank DESC;

Diese Abfrage sucht nach Artikeln, die "PostgreSQL" enthalten, berechnet den Rang jedes Artikels mit der Funktion ts_rank und ordnet die Ergebnisse dann nach Rang in absteigender Reihenfolge.

Die Spalte rank in der Ausgabe zeigt den Relevanzwert (relevance score) für jeden Artikel. Artikel mit höheren Relevanzwerten werden zuerst aufgeführt.

Die Spalte search_vector mit einem Trigger aktuell halten

Um sicherzustellen, dass die Spalte search_vector immer auf dem neuesten Stand ist, können Sie einen Trigger erstellen, der die Spalte automatisch aktualisiert, wenn die Spalten title oder content geändert werden.

Erstellen Sie zunächst eine Trigger-Funktion, die die Spalte search_vector aktualisiert:

CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', NEW.title || ' ' || NEW.content);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Diese Funktion übernimmt die neuen Werte der Spalten title und content und aktualisiert die Spalte search_vector mit der TSVECTOR-Darstellung der verketteten Werte.

Erstellen Sie als Nächstes einen Trigger, der die Funktion update_search_vector aufruft, wenn eine Zeile in die Tabelle articles eingefügt oder aktualisiert wird:

CREATE TRIGGER articles_update_search_vector
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION update_search_vector();

Dieser Trigger aktualisiert die Spalte search_vector automatisch, wenn die Spalten title oder content geändert werden.

Um den Trigger zu testen, aktualisieren Sie den Inhalt eines vorhandenen Artikels:

UPDATE articles SET content = 'This is an updated comprehensive tutorial on PostgreSQL with advanced features.' WHERE id = 1;

Suchen Sie nun nach Artikeln, die das Wort "features" enthalten:

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'features');

Sie sollten den aktualisierten Artikel in den Suchergebnissen sehen.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie die Volltextsuche (full-text search) in PostgreSQL mit TSVECTOR implementieren. Sie haben eine articles-Tabelle erstellt, eine search_vector-Spalte hinzugefügt, einen GIN-Index für die search_vector-Spalte erstellt und einen Trigger eingerichtet, um die search_vector-Spalte automatisch zu aktualisieren, wenn die Spalten title oder content geändert werden. Sie haben auch gelernt, wie Sie Volltextrecherchen mit den TSQUERY-Operatoren durchführen und wie Sie Suchergebnisse nach Relevanz ordnen (ranken).