Ich habe aus Internetressourcen gelesen, dass eine Abfrage langsam wird, wenn der Offset zunimmt. Aber in meinem Fall finde ich es zu langsam. Ich verwende postgres 9.3
Hier ist die Abfrage ( id ist Primärschlüssel):
Er gibt mir Daten in etwa 10 seconds zurück. Und ich denke, es ist zu langsam. Ich habe um 4 million Datensätze in der Tabelle. Die Gesamtgröße der Datenbank ist 23GB .
Maschinenkonfiguration:
%Vor% Wenige Werte von postgresql.conf Datei, die ich geändert habe, sind wie folgt. Andere sind Standard.
Abgesehen von diesen habe ich auch versucht, die Werte von random_page_cost = 2.0 und cpu_index_tuple_cost = 0.0005 zu ändern und das Ergebnis ist das gleiche.
Explain (analyze, buffers) Ergebnis über die Abfrage ist wie folgt:
Wie Menschen auf der ganzen Welt mit diesem Problem in Postgres verhandeln? Jede alternative Lösung wird auch für mich hilfreich sein.
UPDATE :: Hinzufügen von order by id (versucht auch mit anderen indizierten Spalten) und hier ist die Erklärung:
Es ist langsam, weil es die obersten offset Zeilen suchen und die nächsten 100 scannen muss. Keine Menge der Optimierung wird das ändern, wenn Sie mit großen Offsets zu tun haben.
Dies liegt daran, dass Ihre Abfrage die DB-Engine wörtlich anweist, viele Zeilen mit offset 3900000 zu besuchen - das sind 3,9 Millionen Zeilen. Optionen, um dies etwas zu beschleunigen sind nicht viele.
Superschnelles RAM, SSDs usw. helfen. Aber Sie werden nur durch einen konstanten Faktor dabei gewinnen, was bedeutet, dass es nur die Dose die Straße hinunter tritt, bis Sie einen ausreichend größeren Offset erreichen.
Wenn sichergestellt wird, dass die Tabelle in den Speicher passt und noch viel mehr übrig bleibt, wird dies ebenfalls durch einen größeren konstanten Faktor unterstützt - außer dem ersten Mal . Aber das ist möglicherweise nicht möglich mit einer Tabelle oder einem Index, die groß genug sind.
Sicherzustellen, dass Sie Index-Only-Scans durchführen, funktioniert in gewissem Umfang. (Siehe Antwort von velis; es hat eine Menge Vorteile.) Das Problem hier ist, dass Sie sich praktisch einen Index als eine Tabelle vorstellen können, die einen Speicherort und die indizierten Felder speichert. (Es ist mehr optimiert als das, aber es ist eine vernünftige erste Näherung.) Mit genügend Zeilen, werden Sie immer noch Probleme mit einem genügend großen Offset bekommen.
Der Versuch, die genaue Position der Zeilen zu speichern und beizubehalten, ist sicherlich auch ein teurer Ansatz (dies wird zB von benjist vorgeschlagen), obwohl er technisch machbar ist, unterliegt er Beschränkungen, die denen ähnlich sind, die bei der Verwendung von MPTT mit a auftreten Baumstruktur: Sie gewinnen bei Lesevorgängen erheblich, haben aber zu lange Schreibzeiten, wenn ein Knoten so eingefügt, aktualisiert oder entfernt wird, dass große Teile der Daten parallel aktualisiert werden müssen.
Wie hoffentlich klarer ist, gibt es keine wirkliche magische Kugel, wenn Sie mit so großen Offsets zu tun haben. Es ist oft besser alternative Ansätze zu sehen.
Wenn Sie basierend auf der ID (oder einem Datumsfeld oder einer anderen indexierbaren Gruppe von Feldern) paginieren, besteht ein möglicher Trick (z. B. von blogspot) darin, die Abfrage an einem beliebigen Punkt in zu starten der Index.
Anders gesagt, statt:
%Vor%Machen Sie etwas wie:
%Vor%Auf diese Weise behältst du eine Spur davon, wo du dich in deinem Index befindest, und die Abfrage wird sehr schnell, weil sie direkt zum richtigen Startpunkt gehen kann, ohne durch eine Reihe von Reihen zu pflügen:
%Vor%Sie verlieren natürlich die Fähigkeit, zu z.B. Seite 3000. Aber denken Sie daran: Wann war das letzte Mal, dass Sie zu einer riesigen Seitenzahl auf einer Website gesprungen sind, statt direkt zu ihren monatlichen Archiven zu gehen oder das Suchfeld zu benutzen?
Wenn Sie paginieren, aber den Seitenoffset auf irgendeine Weise beibehalten möchten, ist ein weiterer Ansatz, die Verwendung einer größeren Seitennummer zu verbieten. Es ist nicht albern: Das macht Google mit den Suchergebnissen. Wenn Sie eine Suchanfrage ausführen, gibt Google Ihnen eine geschätzte Anzahl von Ergebnissen (Sie können eine angemessene Anzahl mit explain erhalten) und dann können Sie die ersten paar tausend Ergebnisse durchsuchen - mehr nicht. Unter anderem tun sie dies aus Performance-Gründen - genau das, auf das Sie stoßen.
Ich habe Denis 'Antwort aufgefrischt, werde aber selbst einen Vorschlag hinzufügen, vielleicht kann es für Ihren speziellen Anwendungsfall einen gewissen Leistungsvorteil bringen:
Angenommen, Ihre tatsächliche Tabelle ist nicht test_table , sondern eine große zusammengesetzte Abfrage, möglicherweise mit mehreren Joins. Sie können zuerst die erforderliche Start-ID festlegen:
Dies sollte viel schneller sein als die ursprüngliche Abfrage, da nur der Index im Vergleich zur gesamten Tabelle gescannt werden muss. Durch das Abrufen dieser ID wird eine schnelle Indexsuchoption für das vollständige Abrufen geöffnet:
%Vor%Auf diese Weise erhalten Sie die Zeilen in halb zufälliger Reihenfolge. Sie ordnen die Ergebnisse nicht in einer Abfrage an, sodass Sie die Daten so erhalten, wie sie in den Dateien gespeichert sind. Das Problem ist, dass beim Ändern der Zeilen die Reihenfolge geändert werden kann.
Um das zu beheben, sollten Sie order by zur Abfrage hinzufügen. Auf diese Weise gibt die Abfrage die Zeilen in der gleichen Reihenfolge zurück. Darüber hinaus kann ein Index verwendet werden, um die Abfrage zu beschleunigen.
Also zwei Dinge: fügen Sie einen Index hinzu, fügen Sie order by zur Abfrage hinzu. Beide zur selben Spalte. Wenn Sie die ID-Spalte verwenden möchten, dann fügen Sie keinen Index hinzu, sondern ändern Sie die Abfrage wie folgt:
Sie haben nicht gesagt, ob Ihre Daten hauptsächlich schreibgeschützt sind oder häufig aktualisiert werden. Wenn es Ihnen gelingt, Ihre Tabelle gleichzeitig zu erstellen und nur gelegentlich zu aktualisieren (etwa alle paar Minuten), ist Ihr Problem einfach zu lösen:
Zuerst müssen Sie Limit und Offset mit order by-Klausel definieren oder Sie erhalten ein inkonsistentes Ergebnis.
Um die Abfrage zu beschleunigen, können Sie einen berechneten Index haben, aber nur für diese Bedingung:
So können Sie es tun:
create or replace function id_pos (id) returns bigint
as 'select count(id) from test_table where id <= ;'
language sql immutable;
create index table_by_pos on test_table using btree(id_pos(id));
So nennen Sie es (Offset 3900000 Limit 100):
select * from test_table where id_pos(id) >= 3900000 and sales_pos(day) < 3900100;
Auf diese Weise berechnet die Abfrage nicht die 3900000-Offset-Daten, sondern berechnet nur die 100 Daten, wodurch sie viel schneller wird.
Bitte beachten Sie die zwei Bedingungen, unter denen dieser Ansatz stattfinden kann, oder die Position wird sich ändern.
Ich kenne nicht alle Details Ihrer Daten, aber 4 Millionen Zeilen können ein wenig schwerfällig sein. Wenn es einen vernünftigen Weg gibt, den Tisch zu zerbrechen und ihn im Wesentlichen in kleinere Tabellen aufzuteilen, könnte dies von Vorteil sein.
Um dies zu erklären, lassen Sie mich ein Beispiel verwenden. Nehmen wir an, ich habe eine Datenbank, in der ich eine Tabelle namens survey_answer habe, und sie wird sehr groß und sehr langsam. Nehmen wir an, dass diese Umfrageantworten alle von einer bestimmten Gruppe von Clients stammen (und ich habe auch eine Client-Tabelle, die diese Clients überwacht). Dann könnte ich etwas tun, damit ich eine Tabelle namens survey_answer erstellen kann, die keine Daten enthält, aber eine Elterntabelle ist und eine Reihe von Kindtabellen enthält, die tatsächlich die Daten enthalten, denen sie folgen Namensformat survey_answer_ & lt; clientid & gt;, was bedeutet, dass ich untergeordnete Tabellen survey_answer_1, survey_answer_2 usw. für jeden Client haben würde. Dann, wenn ich Daten für diesen Client auswählen musste, würde ich diese Tabelle verwenden. Wenn ich Daten über alle Clients hinweg auswählen muss, kann ich aus der übergeordneten Tabelle "survey_answer" auswählen, aber sie wird langsam sein. Aber um Daten für einen einzelnen Kunden zu bekommen, was ich meistens mache, wäre es schnell.
Dies ist ein Beispiel dafür, wie man Daten aufteilt, und es gibt viele andere. Ein anderes Beispiel wäre, wenn meine Tabelle "survey_answer" nicht einfach vom Client getrennt wird, aber ich weiß, dass ich normalerweise nur über einen Zeitraum von einem Jahr auf Daten zugreife, dann könnte ich möglicherweise Kindtabellen basierend auf dem Jahr erstellen. wie zum Beispiel survey_answer_2014, survey_answer_2013, usw. Dann, wenn ich weiß, dass ich nicht mehr als ein Jahr auf einmal zugreifen werde, muss ich nur auf vielleicht zwei meiner Kindtabellen zugreifen, um alle Daten zu bekommen, die ich brauche.
In deinem Fall ist alles, was mir gegeben wurde, vielleicht die ID. Das können wir auch auflösen (wenn auch vielleicht nicht so ideal). Lassen Sie uns sagen, dass wir es aufteilen, so dass es nur ungefähr 1000000 Zeilen pro Tabelle gibt. Also wären unsere Kindtabellen test_table_0000001_1000000, test_table_1000001_2000000, test_table_2000001_3000000, test_table_3000001_4000000, usw. Anstatt einen Offset von 3900000 zu übergeben, würden Sie zuerst ein wenig Mathematik machen und bestimmen, dass die Tabelle Tabelle test_table_3000001_4000000 mit einem Offset von 900000 ist stattdessen. So etwas wie:
%Vor%Nun, wenn das Scharfstellen der Tabelle nicht in Frage kommt, können Sie vielleicht Teilindizes verwenden, um etwas Ähnliches zu tun, aber ich empfehle wiederum, zuerst zu schärfen. Erfahren Sie mehr über Teilindizes hier .
Ich hoffe, das hilft. (Außerdem stimme ich Szymon Guz zu, dass Sie eine ORDER BY wollen).
Bearbeiten: Beachten Sie, dass, wenn Sie Zeilen löschen oder Zeilen selektiv ausschließen müssen, bevor Sie Ihr Ergebnis von 100 erhalten, das Sharding nach ID sehr schwierig wird (wie von Denis hervorgehoben; und Sharding von ID ist nicht großartig, mit zu beginnen). Aber wenn Sie die Daten nur "paginieren" und nur einfügen oder bearbeiten (keine gewöhnliche Sache, aber es passiert, es fallen mir Protokolle ein), dann kann Sharding nach ID vernünftig durchgeführt werden (obwohl ich immer noch etwas anderes wählen würde) zu zerbrechen).
Tags und Links postgresql postgresql-9.1