Ich habe eine Tabelle mit einer Milliarde Zeilen und möchte die durchschnittliche Zeit und Standardabweichung der Zeit für mehrere Abfragen des Formulars bestimmen:
%Vor%Ich habe tausend zufällige Werte für col1 in einer anderen Tabelle gespeichert.
Gibt es eine Möglichkeit zu speichern, wie lange jede dieser Abfragen (in Millisekunden) in einer separaten Tabelle dauerte, damit ich einige Statistiken zu ihnen ausführen kann? Etwas wie: für jeden col1 in meiner zufälligen Tabelle, führen Sie die Abfrage aus, notieren Sie die Zeit und speichern Sie sie in einer anderen Tabelle.
Ein völlig anderer Ansatz wäre in Ordnung, solange ich in PostgreSQL bleiben kann (d. h. ich möchte kein externes Programm schreiben, um dies zu tun).
Ist Ihnen die EXPLAIN -Anweisung bekannt?
Dieser Befehl zeigt den Ausführungsplan an, den der PostgreSQL-Planer für die gelieferte Anweisung generiert. Der Ausführungsplan zeigt, wie die von der Anweisung referenzierten Tabellen gescannt werden - durch einfachen sequenziellen Scan, Index-Scan usw. - und wenn mehrere Tabellen referenziert werden, welche Join-Algorithmen verwendet werden, um die erforderlichen Zeilen von jedem zu vereinigen Eingabetabelle.
Der kritischste Teil der Anzeige sind die geschätzten Kosten für die Anweisungsausführung. Dabei schätzt der Planer, wie lange die Ausführung der Anweisung dauern wird (gemessen in Einheiten der Festplattenseitenabrufe). Tatsächlich werden zwei Zahlen angezeigt: die Startzeit vor der ersten Zeile und die Gesamtzeit für die Rückgabe aller Zeilen. Für die meisten Abfragen ist die Gesamtzeit von Bedeutung, aber in Kontexten wie einer Unterabfrage in EXISTS wählt der Planer die kleinste Startzeit anstelle der kleinsten Gesamtzeit (da der Executor sowieso anhält, nachdem er eine Zeile erhalten hat). Wenn Sie die Anzahl der Zeilen begrenzen, die mit einer LIMIT-Klausel zurückgegeben werden sollen, führt der Planer eine geeignete Interpolation zwischen den Endpunktkosten durch, um zu schätzen, welcher Plan wirklich der günstigste ist.
Die Option
ANALYZEbewirkt, dass die Anweisung tatsächlich ausgeführt und nicht nur geplant wird. Die gesamte verstrichene Zeit, die innerhalb jedes Planknotens verbraucht wurde (in Millisekunden) und die Gesamtzahl der tatsächlich zurückgegebenen Zeilen werden der Anzeige hinzugefügt. Dies ist nützlich, um zu sehen, ob die Schätzungen des Planers der Realität nahe kommen.
Könnte ziemlich einfach ein Skript schreiben, das für jede der zufälligen Werte in einer Tabelle ein EXPLAIN ANALYZE in Ihrer Abfrage ausführt, und die Ausgabe in einer Datei / Tabelle / etc speichern.
Sie müssen Ihre PostgreSQL-Konfigurationsdatei ändern.
Aktivieren Sie diese Eigenschaft:
%Vor%Danach wird die Ausführungszeit protokolliert und Sie können genau herausfinden, wie schlecht (oder gut) Ihre Abfragen sind.
Sie können auch einige LOG PARSING-Dienstprogramme verwenden, um eine hervorragende HTML-Ausgabe für weitere Analysen wie pgfouine bereitzustellen.
Direkt, nein, ist es nicht. Aber Sie können eine indirekte und ziemlich genaue Schätzung vornehmen, indem Sie die Zeit direkt vor und nach der Abfrage, die Sie zeitlich bestimmen möchten, überprüfen.
%Vor%Die Funktion clock_timestamp () gibt Ihnen die tatsächliche Uhrzeit des Servers an, zu der die Anweisung beginnt. Da SELECT keine Tabellen enthält, können wir erwarten, dass es fast augenblicklich ist. Ich denke, jeder Pg-Treiber bietet Unterstützung für mehrere Abfragen; Es ist wichtig, dass diese 3 Abfragen (die echte und die 2 Extras) zusammenpassen, sonst würden Sie auch die Datentransportzeiten messen ...
Für PHP habe ich eine Funktion, damit umzugehen. Zusammenfassend heißt es:
%Vor%Ich habe nur die Grundlagen dort gelassen. $ conn enthält eine Verbindung zu einer Pg-Verbindung und $ resources ist ein Array von zurückgegebenen pg-Ressourcen (falls Sie mehrere Abfragen in Ihrem $ sql gesendet haben).
$ time enthält die Gesamtzeit seit der Abfrage für den Pg-Server, bis das Ergebnis eintrifft. $ q-time enthält nur die tatsächliche Abfragezeit (oder eine sehr gute Annäherung).
Fügen Sie Fehlerbehandlung und andere Verarbeitung nach Ihren Wünschen hinzu, ich habe viel, aber es ist irrelevant für Ihre Frage.
Sie können das NICHT in SQL tun, denn selbst wenn Sie jede dieser Anweisungen in einer Schleife aufrufen könnten, würde jeder Aufruf von now () das gleiche Ergebnis liefern, da Sie sich in einer einzigen Transaktion befinden.
Dies ist nur möglich, indem Sie eine eigene volatile now () -Funktion erstellen, die bei jedem Aufruf einen anderen Wert zurückgibt.
Tags und Links sql postgresql performance