In meiner PostgreSQL 9.3 Datenbank habe ich eine Tabelle namens articles . Es sieht so aus:
Es gibt mehr Spalten in der Tabelle, aber ich denke nicht, dass sie für die Frage entscheidend sind. Die Gesamtgröße der Tabelle beträgt 94 GB und etwa 29 Mio. Zeilen.
Ich versuche, eine Abfrage für eine Stichwortsuche in einer Untergruppe von 23 Millionen der article -Zeilen auszuführen. Dazu benutze ich die folgende Abfrage:
Das Problem dabei ist, dass es erscheint, indem Sie ts_rank_cd für jedes Ergebnis zuerst ausführen, bevor es sie sortieren kann. Daher ist diese Abfrage sehr langsam, etwa 2-3 Minuten. Ich habe viel herumgelesen, um zu versuchen, eine Lösung zu finden, und es wurde vorgeschlagen, die Suchabfrage in eine andere Abfrage einzubinden, damit das Ranking nur auf die gefundenen Ergebnisse angewendet wird:
Da die Abfrage jedoch so kurz ist, gibt es 450K-Ergebnisse in der Teilmenge. Es dauert also noch eine lange Zeit, es könnte ein bisschen schneller sein, aber ich brauche das im Wesentlichen sofort.
Die Frage: Gibt es irgendetwas , das ich tun kann, um diese Suchfunktionalität in PostgreSQL zu halten?
Es ist nett, wenn diese Logik in der Datenbank gespeichert wird und bedeutet, dass ich keine zusätzlichen Server oder Konfigurationen für etwas wie Solr oder Elasticsearch benötige. Würde zum Beispiel die Erhöhung der Kapazität der Datenbankinstanz helfen? Oder wäre die Kosteneffizienz im Vergleich zur Verlagerung dieser Logik in eine dedizierte Elasticsearch-Instanz nicht sinnvoll?
Die EXPLAIN-Antwort von der ersten Abfrage lautet wie folgt:
%Vor%Und für die zweite Frage:
%Vor%Sie können einfach keinen Index über ts_rank_cd verwenden, da der daraus resultierende Ranking-Wert von Ihrer Abfrage abhängt. Daher müssen alle Rangwerte für die gesamte Ergebnismenge bei jeder Abfrage berechnet werden, bevor die Ergebnismenge sortiert und durch diesen Wert begrenzt werden kann.
Wenn Ihre Suchlogik es Ihnen ermöglicht, diesen Engpass zu vermeiden, indem Sie einmal einen Relevanzwert für jeden Datensatz vorberechnen, erstellen Sie einen Index darüber und verwenden Sie diese Spalte als Sortierspalte anstelle der Deckungssensitivität für jede Abfrage.
Obwohl Sie gesagt haben, dass Sie nicht wollen, schlage ich vor, dass Sie sich eine Suchmaschine ansehen, die mit Postgresql zusammenarbeiten könnte, wie Sphinx. Der standardmäßige BM25-Ranker sollte gut funktionieren. Sie können auch noch Spaltengewichte setzen, wenn Sie müssen ( Ссылка ).
>Update: Dies wird auch in der Dokumentation angegeben:
"Ranking kann teuer werden, da der tsvector eines jeden passenden Dokuments abgefragt werden muss, das I / O-gebunden und daher langsam sein kann. Leider ist dies fast unmöglich zu vermeiden, da praktische Abfragen oft zu einer großen Anzahl von Übereinstimmungen führen."
Siehe Ссылка
Vielleicht ... Ihre Klausel in der Kategorie wird möglicherweise entfernt, wenn Sie einen HASH-Index verwenden. Ihre Abfrage auf tsv könnte mit einem GIN-Index optimiert werden, Wenn Ihre Kategorie eine (eher kleine) endliche Menge ist, sollten Sie vielleicht eine Aufzählung für die Kategorie verwenden, anstatt sie zu variieren (oder zumindest nicht mit varchar). (Ich frage mich, ob das Gewicht in Ihrem Fall wirklich wichtig ist).
%Vor%Sie sollten die Kategoriespalte indizieren und Sie könnten versuchen, den Arbeitsspeicher für diese bestimmte Abfrage zu erhöhen, um den Bitmap-Heap-Scan zu vermeiden, wenn die Kategorie nicht das verlangsamt, was sie verlangsamt:
LOCAL setzen work_mem = '64MB';
Dies könnte die Speicherauslastung stark erhöhen, wenn die Abfrage gleichzeitig ausgeführt wird.
Tags und Links sql postgresql full-text-search