Wie ich bist du ein Postgres-Noob. Sie sind ein Softwareentwickler (und ein guter!), Aber Sie müssen ab und zu Datenbankabfragen schreiben, und oft sind sie ziemlich langsam. Da ist Alexa, der DBA – Sie können sie um Hilfe bitten. Aber obwohl sie wirklich höflich sind, weißt du, dass es Dinge gibt, die sie lieber tun würden. Und selbst wenn Sie das Ganze nicht selbst machen können, möchten Sie mit zumindest einem gewissen Verständnis für das Problem zu Alexa gehen können.
Dies ist für Sie.
Postgres hat einige ziemlich großartige Tools, um langsame Abfragen zu verstehen – Sie können einfach die Datenbank bitten, Ihnen zu sagen, warum es so lange dauert. Dazu führen Sie einen EXPLAIN-Befehl aus. Wenn Sie also Ihre Abfrage verstehen möchten, um Personen namens "Alice" zu finden, führen Sie Folgendes aus:
EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS)
SELECT first_name, last_name
FROM people
WHERE first_name = 'Alice';
Moment mal, lass mich ERKLÄREN
Okay, lassen Sie uns das aufschlüsseln.
Die Zeilen 2–4 sind einfach jede (DML) Postgres-Abfrage – sie sind der Code, den wir verbessern wollen.
EXPLAIN ist der Befehl, den wir hier ausführen. Es gibt den Abfrageplan zurück, mit dem die Datenbank beschreibt, was sie tun wird. Wenn Sie EINFACH EXPLAIN für die Abfrage ausführen, ohne alle Optionen in Klammern, erhalten Sie eine Textausgabe wie folgt:
Seq Scan on people (cost=0.00..16.49 rows=2 width=1032)
Filter: ((first_name)::text = 'Alice'::text)
Ich persönlich finde dieses Format wirklich schwer zu lesen! Ich würde empfehlen, die Ausgabe immer als JSON (oder YAML oder XML auf Knopfdruck, je nach Ihren Vorlieben) formatiert anzufordern.
Wenn Sie EXPLAIN (FORMAT JSON) für die Abfrage ausführen, sollte Folgendes angezeigt werden:
[
{
"Plan": {
"Node Type": "Seq Scan",
"Parallel Aware": false,
"Relation Name": "people",
"Alias": "people",
"Filter": "((first_name)::text = 'Alice'::text)",
"Startup Cost": 0.00,
"Total Cost": 16.49,
"Plan Rows": 2,
"Plan Width": 1032
}
}
]
Wir werden jetzt nicht zu sehr ins Detail zu all diesen Bereichen gehen – wir werden uns später mit vielen von ihnen befassen. Die wichtigsten Dinge, die im Moment zu beachten sind, sind, dass es einige Details über den stattfindenden Vorgang gibt – seinen Typ (einen sequenziellen Scan), den verwendeten Filter und das Datenbankobjekt, auf dem er arbeitet.
Dies ist ein wirklich einfaches Beispiel – eine Abfrage mit einem Vorgang. Die meisten Abfragen, die Sie schreiben, bestehen aus mehreren Vorgängen. Hier ist ein Abfrageplan mit einer ganzen Reihe von verschiedenen Operationen,verschachtelt und gestapelt auf spannende Weise, der aus einem Beispiel auf der ausgezeichneten postgresqltutorial.com generiert wurde.
Um auf das ursprüngliche Beispiel zurückzukommen, haben wir über die Bits gesprochen, die erklären, was die Datenbank tun wird, wenn sie Ihre Abfrage ausführt, aber es gibt vier zusätzliche Felder, die Ihnen eine Vorstellung davon geben, wie die verschiedenen Operationen ausgeführt werden. Die Werte “Startup Cost” und “Total Cost” geben Ihnen eine ungefähre Vorstellung von der relativen Zeit (in beliebigen Einheiten), die für jeden Vorgang aufgewendet wird, während die Werte “Plan rows” und “Plan Width” Ihnen die erwarteten Zeilen und die Größe jeder Zeile (in Byte) mitteilen.
Das Wichtigste, woran man sich bei all diesen Werten erinnern sollte, ist jedoch, dass es sich um Schätzungen handelt. Der Abfrageplaner hat Zugriff auf eine Reihe von Informationen über Ihre Datenbank, die er verwendet, um herauszufinden, wie lange die einzelnen Vorgänge dauern und welchen Ansatz Sie wählen müssen. Oft ist das ziemlich genau, besonders bei kleineren Datensätzen. Aber es ist definitiv nützlich, um ein realistischeres Bild zu bekommen.
ANALYSIEREN Sie dies
Durch hinzufügen der Option ANALYZE zum EXPLAIN-Befehl erhalten wir konkretere Informationen. Die Datenbank führt Ihre Abfrage tatsächlich aus (so dass alle Nebenwirkungen auftreten!) und gibt Ihnen einige Daten über die reale Leistung Ihrer Abfrage – Timings (in ms),Zeilenanzahl und Anzahl der Schleifen / Anwendungen des Schritts.
EXPLAIN (FORMAT JSON, ANALYZE) auf unserer Beispielabfrage gibt uns:
[
{
"Plan": {
"Node Type": "Seq Scan",
"Parallel Aware": false,
"Relation Name": "people",
"Alias": "people",
"Filter": "((first_name)::text = 'Alice'::text)",
"Startup Cost": 0.00,
"Total Cost": 16.49,
"Plan Rows": 2,
"Plan Width": 1032,
"Actual Startup Time": 0.099,
"Actual Total Time": 0.357,
"Actual Rows": 2,
"Actual Loops": 1,
"Rows Removed by Filter": 2684
},
"Planning Time": 0.108,
"Execution Time": 0.381,
"Triggers": []
}
]
Unnötig zu sagen, dass die Kenntnis der tatsächlich benötigten Zeit und der für jeden Vorgang erzeugten Zeilen von unschätzbarem Wert sein kann, insbesondere in Fällen, in denen der Abfrageplaner aufgrund unvollständiger oder veralteter Informationen schlechte Entscheidungen getroffen hat. Weitere Informationen zu Schätzungen der Zeilenanzahl finden Sie hier.
Treffer zu den PUFFERN
Durch Hinzufügen der Option BUFFERS erhalten wir einige Informationen über Cache-Treffer/-Fehlschläge, die Ihnen viel über die für E/A aufgewendete Zeit aussagen können. EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) für unsere Beispielabfrage gibt zurück
[
{
"Plan": {
"Node Type": "Seq Scan",
"Parallel Aware": false,
"Relation Name": "people",
"Alias": "people",
"Filter": "((first_name)::text = 'Alice'::text)",
"Startup Cost": 0.00,
"Total Cost": 16.49,
"Plan Rows": 2,
"Plan Width": 1032,
"Actual Startup Time": 0.026,
"Actual Total Time": 0.106,
"Actual Rows": 2,
"Actual Loops": 1,
"Rows Removed by Filter": 2684,
"Shared Hit Blocks": 9,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
"Planning Time": 0.120,
"Execution Time": 0.125,
"Triggers": []
}
]
Dies kann nützlich sein, wenn Sie versuchen, die Cache-Nutzung zu verstehen und sicherzustellen, dass so viel wie möglich im RAM passiert, aber im Moment werden wir nur darauf hinweisen, dass es existiert.
Wenn Sie einen Profi um Hilfe bitten möchten (z. B. Ihren DBA Alexa oder die sachkundigen und freundlichen Postgres-Performance-Mailinglisten),dann ist dies eines der Dinge, die Sie einbeziehen sollten, um ein guter Bürger und kein lästiger Noob zu sein – zusammen mit der Sicherstellung, dass Sie Ihr Bestes getan haben, um das Problem selbst zu verstehen.