Wir bereiten eine Volltextsuche in Postgres vor. Teil 1

UPD Teil 2

Dieser Artikel ist der erste einer kleinen Reihe von Artikeln zur optimalen Konfiguration der Volltextsuche in PostgreSQL. Ich musste kürzlich ein ähnliches Problem bei der Arbeit lösen - und ich war sehr überrascht, dass zu diesem Thema zumindest einige vernünftige Materialien fehlten. Meine Erfahrung im Kampf unter dem Schnitt.

Krawatte

Ich unterstütze ein relativ großes Projekt, bei dem Dokumente öffentlich durchsucht werden. Die Datenbank enthält ~ 500.000 Dokumente mit einem Gesamtvolumen von ~ 3,6 GB. Das Wesentliche bei der Suche ist Folgendes: Der Benutzer füllt ein Formular aus, in dem sowohl eine Volltextabfrage als auch eine Filterung nach einer Vielzahl von Feldern in der Datenbank erfolgt, einschließlich Joins.

Die Suche funktioniert (oder besser gesagt, funktioniert) über Sphinx und hat nicht sehr gut funktioniert. Die Hauptprobleme waren wie folgt:

1. Die Indizierung verbrauchte ungefähr 8 GB RAM. Auf einem Server mit 8 GB RAM ist dies ein Problem. Der Speicher wurde ausgetauscht, was zu einer schrecklichen Leistung führte.
2. Der Index wurde in ca. 40 Minuten erstellt. Es gab keine Frage einer Konsistenz der Suchergebnisse, die Indizierung wurde einmal am Tag gestartet.
3. Die Suche hat lange funktioniert. Besonders lange Anfragen wurden ausgeführt, die einer Vielzahl von Dokumenten entsprachen: Eine Vielzahl von ID-Shniks musste von der Sphinx in die Datenbank übertragen und nach Relevanz im Backend sortiert werden.

Aufgrund dieser Probleme entstand die Aufgabe, die Volltextsuche zu optimieren. Diese Aufgabe hat zwei Lösungen:

1. Sphinx festziehen: Konfigurieren Sie einen Echtzeitindex und speichern Sie Attribute zum Filtern im Index.
2. Verwenden Sie das integrierte FTS PostgreSQL.

Es wurde beschlossen, die zweite Lösung zu implementieren: Auf diese Weise können Sie den Index nativ automatisch aktualisieren, die lange Kommunikation zwischen zwei Diensten vermeiden und einen Dienst anstelle von zwei überwachen.

Es scheint eine gute Lösung zu sein. Aber es lagen Probleme vor uns.

Beginnen wir von vorne.

Wir verwenden naiv die Volltextsuche

Wie in der Dokumentation angegeben, erfordern Volltextsuchen die Verwendung der tsquery tsvector und tsquery . Der erste speichert den Text des Dokuments in einer suchoptimierten Form, der zweite speichert die Volltextabfrage.

Um PostgreSQL zu durchsuchen, gibt es Funktionen to_tsvector , plainto_tsquery , to_tsquery . Um die Ergebnisse zu ts_rank gibt es ts_rank . Ihre Verwendung ist intuitiv und sie sind in der Dokumentation gut beschrieben, sodass wir uns nicht mit den Details ihrer Verwendung befassen.

Eine herkömmliche Suchabfrage, die sie verwendet, sieht folgendermaßen aus:

 SELECT id, ts_rank(to_tsvector("document_text"), plainto_tsquery('')) FROM documents_document WHERE to_tsvector("document_text") @@ plainto_tsquery('') ORDER BY ts_rank(to_tsvector("document_text"), plainto_tsquery('')) DESC; 

Wir haben die IDs von Dokumenten abgeleitet, in deren Text sich das Wort "Abfrage" befindet, und sie in absteigender Reihenfolge ihrer Relevanz sortiert. Alles scheint in Ordnung zu sein? Nein.

Der obige Ansatz hat viele Nachteile:

1. Wir verwenden keinen Index für die Suche.
2. Die Funktion ts_vector wird für jede Zeile der Tabelle aufgerufen.
3. Die Funktion ts_rank wird für jede Zeile der Tabelle aufgerufen.

Dies alles führt dazu, dass die Suche sehr lange dauert. EXPLAIN Ergebnisse auf einer EXPLAIN :

 Gather Merge (actual time=420289.477..420313.969 rows=58742 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (actual time=420266.150..420267.935 rows=19581 loops=3) Sort Key: (ts_rank(to_tsvector(document_text), plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 2278kB -> Parallel Seq Scan on documents_document (actual time=65.454..420235.446 rows=19581 loops=3) Filter: (to_tsvector(document_text) @@ plainto_tsquery(''::text)) Rows Removed by Filter: 140636 Planning time: 3.706 ms Execution time: 420315.895 ms 

420 Sekunden! Für eine Anfrage!

Die Basis erzeugt auch viele Vorings der Form [54000] word is too long to be indexed . Es gibt nichts zu befürchten. Der Grund dafür ist, dass in meiner Datenbank Dokumente enthalten sind, die im WYSIWYG-Editor erstellt wurden. Es fügt viel   wo immer möglich, und es gibt 54 Tausend in einer Reihe. Postgres ignoriert Wörter dieser Länge und schreibt einen Vorning, der nicht deaktiviert werden kann.

Wir werden versuchen, alle festgestellten Probleme zu beheben und die Suche zu beschleunigen.

Wir optimieren naiv die Suche

Wir werden natürlich nicht mit der Kampfbasis spielen - wir werden eine Testbasis erstellen. Es enthält ~ 12 Tausend Dokumente. Die Anfrage aus dem Beispiel wird dort ~ 35 Sekunden ausgeführt. Unverzeihlich lang!

 Sort (actual time=35431.874..35432.208 rows=3593 loops=1) Sort Key: (ts_rank(to_tsvector(document_text), plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 377kB -> Seq Scan on documents_document (actual time=8.470..35429.261 rows=3593 loops=1) Filter: (to_tsvector(document_text) @@ plainto_tsquery(''::text)) Rows Removed by Filter: 9190 Planning time: 0.200 ms Execution time: 35432.294 ms 

Index

Zunächst müssen Sie natürlich einen Index hinzufügen. Der einfachste Weg: ein Funktionsindex.

 CREATE INDEX idx_gin_document ON documents_document USING gin (to_tsvector('russian', "document_text")); 

Ein solcher Index wird für eine lange Zeit erstellt - es dauerte ~ 26 Sekunden auf der Testbasis. Er muss die Datenbank durchsuchen und die Funktion to_tsvector für jeden Datensatz aufrufen. Obwohl es die Suche immer noch auf 12 Sekunden beschleunigt, ist es immer noch unverzeihlich lang!

 Sort (actual time=12213.943..12214.327 rows=3593 loops=1) Sort Key: (ts_rank(to_tsvector('russian'::regconfig, document_text), plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 377kB -> Bitmap Heap Scan on documents_document (actual time=3.849..12212.248 rows=3593 loops=1) Recheck Cond: (to_tsvector('russian'::regconfig, document_text) @@ plainto_tsquery(''::text)) Heap Blocks: exact=946 -> Bitmap Index Scan on idx_gin_document (actual time=0.427..0.427 rows=3593 loops=1) Index Cond: (to_tsvector('russian'::regconfig, document_text) @@ plainto_tsquery(''::text)) Planning time: 0.109 ms Execution time: 12214.452 ms 

Wiederholter Aufruf to_tsvector

Um dieses Problem zu lösen, müssen Sie tsvector in der Datenbank speichern. Wenn Sie Daten in einer Tabelle mit Dokumenten ändern, müssen Sie diese natürlich aktualisieren - über Trigger in der Datenbank mithilfe des Backends.

Es gibt zwei Möglichkeiten, dies zu tun:

1. Fügen tsvector der Tabelle mit Dokumenten eine Spalte vom Typ tsvector .
2. Erstellen Sie eine separate Tabelle mit Eins-zu-Eins-Kommunikation mit der Dokumententabelle und speichern Sie die Vektoren dort.

Die Vorteile des ersten Ansatzes: das Fehlen von Verknüpfungen bei der Suche.
Die Vorteile des zweiten Ansatzes: Der Mangel an zusätzlichen Daten in der Tabelle mit Dokumenten, es bleibt die gleiche Größe wie zuvor. Mit dem Backup müssen Sie keine Zeit und tsvector Platz auf tsvector , den Sie überhaupt nicht sichern müssen.

Beide Fahrten führen dazu, dass die Daten auf der Festplatte doppelt so groß werden: Die Texte von Dokumenten und ihre Vektoren werden gespeichert.

Ich habe mich für den zweiten Ansatz entschieden, dessen Vorteile für mich wichtiger sind.

 CREATE INDEX idx_gin_document ON documents_documentvector USING gin ("document_text"); 

 SELECT documents_document.id, ts_rank("text", plainto_tsquery('')) FROM documents_document LEFT JOIN documents_documentvector ON documents_document.id = documents_documentvector.document_id WHERE "text" @@ plainto_tsquery('') ORDER BY ts_rank("text", plainto_tsquery('')) DESC; 

Fügen Sie der verknüpften Tabelle Daten hinzu und erstellen Sie einen Index. Das Hinzufügen von Daten dauerte auf Testbasis 24 Sekunden, und das Erstellen eines Index dauerte nur 2,7 Sekunden . Wie wir sehen, hat sich die Aktualisierung des Index und der Daten nicht wesentlich beschleunigt, aber der Index selbst kann jetzt sehr schnell aktualisiert werden.

Und wie oft hat sich die Suche beschleunigt?

 Sort (actual time=48.147..48.432 rows=3593 loops=1) Sort Key: (ts_rank(documents_documentvector.text, plainto_tsquery(''::text))) DESC Sort Method: quicksort Memory: 377kB -> Hash Join (actual time=2.281..47.389 rows=3593 loops=1) Hash Cond: (documents_document.id = documents_documentvector.document_id) -> Seq Scan on documents_document (actual time=0.003..2.190 rows=12783 loops=1) -> Hash (actual time=2.252..2.252 rows=3593 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 543kB -> Bitmap Heap Scan on documents_documentvector (actual time=0.465..1.641 rows=3593 loops=1) Recheck Cond: (text @@ plainto_tsquery(''::text)) Heap Blocks: exact=577 -> Bitmap Index Scan on idx_gin_document (actual time=0.404..0.404 rows=3593 loops=1) Index Cond: (text @@ plainto_tsquery(''::text)) Planning time: 0.410 ms Execution time: 48.573 ms 

 SELECT id, ts_rank("text", plainto_tsquery('')) AS rank FROM documents_documentvector WHERE "text" @@ plainto_tsquery('') ORDER BY rank; 

  Sortieren (tatsächliche Zeit = 44.339..44.487 Zeilen = 3593 Schleifen = 1)
   Sortierschlüssel: (ts_rank (Text, Plainto_tsquery ('Abfrage' :: Text)))
   Sortiermethode: Quicksort Speicher: 265kB
   -> Bitmap-Heap-Scan auf documents_documentvector (tatsächliche Zeit = 0,692..43.682 Zeilen = 3593 Schleifen = 1)
         Überprüfen Sie erneut Cond: (text @@ plago_tsquery ('query' :: text))
         Heap-Blöcke: genau = 577
         -> Bitmap-Index-Scan auf idx_gin_document (tatsächliche Zeit = 0,577..0,577 Zeilen = 3593 Schleifen = 1)
               Index Cond: (text @@ plago_tsquery ('query' :: text))
 Planungszeit: 0,182 ms
 Ausführungszeit: 44.610 ms

Unglaublich! Und das trotz join und ts_rank . Bereits ein durchaus akzeptables Ergebnis, wird die meiste Zeit nicht durch die Suche, sondern durch die Berechnung von ts_rank für jede der Zeilen ts_rank .

ts_rank Mehrfachanruf

Es scheint, dass wir alle unsere Probleme außer diesem erfolgreich gelöst haben. 44 Millisekunden sind eine anständige Vorlaufzeit. Happy End scheint nah? Da war es!

Führen Sie dieselbe Abfrage ohne ts_rank und vergleichen Sie die Ergebnisse.

 SELECT document_id, 1 AS rank FROM documents_documentvector WHERE "text" @@ plainto_tsquery('') ORDER BY rank; 

 Bitmap Heap Scan on documents_documentvector (actual time=0.503..1.609 rows=3593 loops=1) Recheck Cond: (text @@ plainto_tsquery(''::text)) Heap Blocks: exact=577 -> Bitmap Index Scan on idx_gin_document (actual time=0.439..0.439 rows=3593 loops=1) Index Cond: (text @@ plainto_tsquery(''::text)) Planning time: 0.147 ms Execution time: 1.715 ms 

1,7 ms! Dreißigmal schneller! Für eine Kampfbasis sind die Ergebnisse ~ 150 ms und 1,5 Sekunden. Der Unterschied ist in jedem Fall eine Größenordnung, und 1,5 Sekunden sind nicht die Zeit, die Sie auf eine Antwort von der Basis warten möchten. Was tun?

Sie können die Sortierung nach Relevanz nicht ts_rank die Anzahl der zu zählenden Zeilen nicht reduzieren (die Datenbank sollte ts_rank für alle ts_rank Dokumente berechnen, da sie sonst nicht sortiert werden können).

An einigen Stellen im Internet wird empfohlen, die häufigsten Anforderungen zwischenzuspeichern (und dementsprechend ts_rank aufzurufen). Dieser Ansatz gefällt mir jedoch nicht: Es ist ziemlich schwierig, die richtigen Abfragen richtig auszuwählen, und die Suche bei den falschen Abfragen wird immer noch langsamer.

Ich würde es sehr begrüßen, wenn die Daten nach Durchlaufen des Index in einer bereits sortierten Form vorliegen würden, wie es Sphinx tut. Leider kann in PostgreSQL nichts aus der Box gemacht werden.

Aber wir hatten Glück - der RUM-Index kann das. Details dazu finden Sie beispielsweise in der Präsentation der Autoren . Es speichert zusätzliche Informationen zur Anfrage, mit denen Sie die sogenannte direkt auswerten können. "Abstand" zwischen tsvector und tsquery und erzeugen unmittelbar nach dem Scannen des Index ein sortiertes Ergebnis.

Aber einen GIN zu werfen und RUM zu installieren, lohnt sich nicht sofort. Es hat Minuspunkte, Pluspunkte und Anwendungsgrenzen - darüber werde ich im nächsten Artikel schreiben.