Ich möchte erst mal wissen, wer nutzt alles Postgres? Oh, doch sehr viele. Wer hat sich schon mal X-Plane angeschaut? Doch sehr viele. Das freut mich. Wer hat's verstanden?

Okay. Wenn Postgres eine Abfrage bekommt, wird diese Abfrage entsprechend zerlegt. Das nochmal ganz kurz bei mich. Also ich, mein Name ist Andreas Kretschmer. Ich wohne hier auch gleich in der Nähe, in der Nähe von Dresden.

Habe es also nicht weit gehabt heute. Arbeite seit drei Jahren bei Säckenquadrant. Ich habe vorher in Dresden bei Internet Hosting private gearbeitet. Bei Säckenquadrant machen wir halt so Support für Postgresgoel. Machen das weltweit 24 mal 7. Sehr interessante Arbeit und macht Spaß.

So. Wie werden Abfragen in Postgres ausgeführt? Wenn die Datenbank ein Select-Statement bekommt oder auch ein Insert-Statement oder andere Geschichten, wird zuallererst diese Abfrage zerlegt in Teilaufgaben. Ein Select, was sich über mehrere Tabellen hinzieht,

wo fünf, sechs Tabellen zusammengejoint werden, wo Aggregationen drin sind, wo Sortierungen drin sind etc. ist im Ganzen viel zu komplex, um im Ganzen so direkt ausgeführt zu werden. Sondern Postgresgoel verfährt nach der Methode Teile und Herrsche. Das heißt, jede Abfrage wird versucht, in bestimmte Grundelemente zu zerlegen.

Die Entscheidungen, die dazu getroffen sind, beinhalten unter anderem, welche Tabellen sind abzufragen, welche Aggregationen sind notwendig, ist das Ergebnis zu sortieren etc.

Für jeder dieser einzelnen Schritte, die ich hier mache, wir sehen das da noch, wird abgeschätzt, wie hoch der Ausführungsaufwand ist, diesen Schritt auszuführen. Aufwand bedeutet, ich muss so und so viele Blöcke von der Festplatte lesen. Aufwand ist aber auch, ich habe entsprechende Indexoperationen, CPU-Operationen.

Ich habe Aufwand, dass ich Speicher braue, dass ich Speicher allokieren muss, dynamisch für diese Abfragen, diese auszuführen etc. Oft haben wir mehrere Alternativen. Zum Beispiel mache ich einen Sequential-Scan, mache ich einen Index-Scan. Wenn ich mehrere Index zur Verfügung habe, wie nutze ich diese Index?

Nehme ich den Index oder nehme ich den Index, wenn mehrere da sind? Wenn ich zum Beispiel eine Tabelle habe mit zehn Spalten und ich habe auf jeder Spalte einen separaten Index, welche Index nutze ich? Nutze ich alle zehn, nutze ich nur fünf oder acht? Das sind alles Entscheidungen, die getroffen werden müssen. Um diese Entscheidungen treffen zu können, muss ich diese einzelnen Varianten

und Möglichkeiten, die ich habe, bewerten. Das ist die falsche Reihenfolge. Ich muss diese ganzen einzelnen Schritte bewerten. Das heißt, welchen Aufwand betreibe ich? Und ich muss diese unterschiedlichen Möglichkeiten,

diese unterschiedlichen Optionen, die ich habe einordnen, miteinander vergleichen. Um das erreichen zu können, hat PostgreSQL ein sogenanntes Kostenmodell. Das heißt, für jede bestimmte Grundoperation werden fiktive Kosten hinterlegt.

Die wesentlichen Kosten, die bei einer Abfrage entstehen, ist das Lesen der Daten von der Festplatte. Das ist in den meisten Fällen schonmal der absolute Großteil, was ich habe. PostgreSQL orientiert sich in diesem Kostenmodell.

PostgreSQL ist ja über 20 Jahre alt, 25 Jahre. Damals gab es noch keine SSD-Festplatten, sondern damals gab es noch diese Festplatten, die sich gedreht haben mit einem Kopf etc. Die hatten insbesondere Positionierungsseiten. Bis also die Platte sich daran gedreht hat, wo ich meinen Datensatz lesen kann. Daraus resultiert das Grundfragment des Kostenmodells.

Wenn ich eine Datei von Platte sequentially lese, also von vorn bis hinten, sprechen wir von einem Sequential-Scan. Dieser Sequential-Scan ist mit dem fiktiven Kostenfaktor 1 bewertet. Wenn ich innerhalb der Datei hier und dort lesen muss,

habe ich einen random Zugriff auf die Festplatte. Für diese random Zugriffe habe ich höhere Kosten, weil ich dort jedes Mal warten muss, bis der Kopf sich positioniert hat, bis die Platte sich dahin gedreht hat. Deswegen sieht das Kostenmodell für solche Zugriffe den Faktor 4 vor.

Die meisten verwenden mittlerweile SSDs. Da ja schon mal ganz allgemein als Hinweis, was man machen kann, um seine Hardware oder seine PostgreSQL-Konfiguration an die Hardware anzupassen. Wer z.B. einen Sofa hat mit Postgres oder Laptop und dort SSDs eingebaut,

kann in der PostgreSQL-Konfigurationsdatei schon mal diesen Kostenfaktor 4 herunter setzen, für diesen Random-Page-Kost. Wir empfehlen dann Werte von 1,5 oder 2.

Was brauchen wir weiter für Voraussetzungen, um passende sinnvolle Abfragepläne zu erstellen? Dazu brauche ich auf alle Fälle auch Informationen, wie viel Daten muss ich denn lesen. Im Vortrag vorher wurde schon genannt, oft hilft, den Befehl Analyse auszuführen.

Was macht dieser Befehl Analyse? Analyse liest die Tabelle durch, entweder eine einzelne Tabelle oder die ganze Datenbank, liest also alle Tabellen durch und schaut erstens, wie groß ist die Tabelle auf Platte, also wie viele Blöcke belegt diese auf Platte.

Es wird geschaut, wie viele Datensätze befinden sich in der Tabelle. Es ist ein Unterschied, ob ich da 5 Datensätze habe oder 50 Millionen. Und ich erstelle Statistiken zu diesen Daten. Ich möchte jetzt nicht ins Detail reingehen, aber Bestandteil dieser Statistiken sind z.B. Histogramme,

wie die Werteverteilung der Daten innerhalb dieser Spalte ist. Und das sind für PostCrossQl ziemlich wichtige Werte. Aus diesen Werten werden dann die Pläne, die erstellt werden, die möglichen Pläne, mit Kosten versehen. Und dann wird einfach der günstigste Plan gewählt, der günstigste Plan aus Sicht des Optimizers.

Dieses Statistikmodell, das PostCrossQl verwendet, ist nicht perfekt. Es wird versucht, möglichst ideal die Tabellen abzubilden, was in der Realität natürlich nicht immer funktioniert.

Was kann passieren? Es kann passieren, dass die Statistiken zu alt sind z.B. Das heißt, dass der Lauf, um diese Statistiken zu erstellen, lief jetzt vor 3 Minuten. Ich habe aber jetzt 10 Millionen Datensätze importiert. Dann werden die Statistiken das nicht berücksichtigen.

Diese 10 Millionen Datensätze sind der Statistik nicht bekannt. Was auch passieren kann bei großen Tabellen, dass die Stichprobe zu gering ist. Das heißt, bei diesem Lauf, bei dieser Analyse der einzelnen Tabellen werden Stichproben genommen und über diese Stichproben diese Histogrammverteilung etc. erstellt.

Diese Stichproben können zu gering sein. Also bei sehr großen Tabellen kann es sinnvoll sein, diesen Wert zu erhöhen. Der Default-Wert ist 100. Die Entwickler von PostCrossQl arbeiten daran, das Statistikmodell weiter zu perfektionieren und zu erhöhen und zu erweitern. Z.B. gibt es neu in PostCrossQl 10 Statistiken über mehrere Spalten einer Tabelle.

Das ist ein neues Feature und für manche Sachen ist das extrem nützlich. PostCrossQl speichert diese ganzen Statistiken in einigen internen Tabellen. Wichtige Tabellen ist die Tabelle Pitchy Class und Pitchy Stats, wobei das nur ein Few ist.

Wenn ich jetzt sehen will, wie wird PostCrossQl die Abfrage ausführen? Welchen Plan verwendet das oder hat es verwendet? Um das zu sehen gibt es den Befehl Explain. Und diesen Befehl gibt es im Prinzip in mehreren Formen, je nachdem wie ich ihn aufrufe.

Die einfachste Art, dass ich ihn aufrufe, sage ich Explain und dahinter die eigentliche Abfrage. Explain, select, Sternform, Table z.B. Wenn ich das so aufrufe, dann zeigt PostCrossQl mir an, wie es die Abfrage ausführen würde.

Sie führt es aber nicht aus. Die andere Variante ist, dass ich mit Explain Analyse arbeite. Explain Analyse, dahinter mein select. Dann sehe ich, wie PostCrossQl die Abfrage plant. PostCrossQl führt die Abfrage aus und zeigt mir am Ende die Ergebnisse an.

Ich kann dann sehen, ob z.B. die Schätzwerte gestimmt haben, basierend auf den Statistiken. Ich gebe dann noch eine weitere Form mit Explain Analyse und Buffers in dieser Form geschrieben z.B. Dann würde ich noch sehen, die Anzahl der einzelnen Festplattenzugriffe.

Sehr, sehr interessant. Oh Gott, ist das lesbar? Okay. Das auch hier unten? Das ist wahrscheinlich ein bisschen klein. Ist auch nicht sehr viel Zeit dabei. Was habe ich gemacht?

Ich habe einfach mal eine Tabelle erstellt. Ich habe dort in dieser Tabelle ein paar Datensätze eingefügt, nämlich fünf Datensätze. Ich habe einen Analyse gemacht und habe jetzt einen Explain aufgerufen. Explain Analyse, let's start from table, mit einer werkondition. Was ich jetzt hier sehe, ist der Abfrageplan. Ich sehe einen Sequential Scan.

Ganz klar, erstens ist die Tabelle sehr klein. Zweitens habe ich hier gar keine Indexer. Ich sehe also jetzt hier meine fiktiven Kosten. Das sind jetzt die zusammengerechneten Festplattenzugriffe und CPU-Zeiten etc. Und ich sehe jetzt hier, er schätzt, dass eine Ergebnisseile rauskommt.

Und er schätzt, dass diese Ergebnisseile 16 bytes breit sein wird. Bei der Tabelle ist das noch simpel, weil ich jetzt hier bloß ein paar Integerwerte habe. Angenommen, ich habe dort große Geometrien z.B. Dann könnte hier unterschiedlich groß sein. Und POSCASQL schätzt oder ermittelt, wie groß eine Zeile im Durchschnitt ist.

Und kann aufgrund dessen eine Zeile und dieser Breite ungefähr schon kalkulieren, wie groß ist die Ergebnismenge. Das ist wichtig, um z.B. abzuschätzen, wie viel Speicher brauche ich dafür, wie viel RAM muss ich allokieren. So, das sind die geschätzten Werte beim Explain Analyse. Und beim Analyse habe ich dahinter noch diese aktuellen Werte.

Und die sind immer sehr interessant. Ich sehe also jetzt den Zeitfaktor, den ich in diesen Schritt gebraucht habe, die aktuelle Zeit. Und ich sehe die tatsächliche Anzahl der Ergebnisse. Das heißt, geschätzt war 1, real ist 0.

Und ich führe das einmal aus, ich führe das nicht in Schleifen aus. Und das ist schon der erste Punkt, wo ich jetzt nachschauen kann, wenn ich so einen Explain Analyse durchführe. Wo ich zuerst draufschaue, ist das, was geschätzt ist, und das, was real ist, ungefähr gleich. Wenn ich an der Stelle Differenzen habe, die mehr als 10er Potenzen unterschiedlich sind, also er schätzt 1 und es kommen 10 Millionen Zeilen raus,

dann weiß ich, dass die Statistiken irgendwie nicht stimmen. Dann ist irgendwas nicht korrekt. Um nochmal zu zeigen, wie das Ganze gespeichert ist. Ich kann mir hier aus meinen Pitchy Stats, jetzt zum Beispiel, und der Spalte A, hier meine Histogrammgrenzen anzeigen lassen.

Ich sorgte schon standardmäßig maximal 100 Werte. Die Tabelle hat jetzt hier nur 5 Werte gehabt. Deswegen sehe ich jetzt hier meine 5 Werte, die in der Tabelle auch real drin sind. Und die Werte sehe ich auch beim entsprechenden Selected hier. Eine andere Tabelle, die ich noch habe, ist die Tabelle Pitchy Class.

Dort kann ich nochmal ablesen, wie viele Zeilen und wie viele Pages die Tabelle insgesamt hat. Also Pages sind nochmal 8K große Blöcke auf der Festplatte. Ich habe jetzt hier mal weitergemacht.

Ich habe an die Tabelle weitere Daten eingefügt, nämlich 50 Millionen Datensätze. Und ich habe ein Analyse gemacht auf dieser Tabelle. Und wenn ich jetzt schaue, wie groß ist die Tabelle, wie viele Datensätze enthält sie, sehe ich also meine 15 Millionen Datensätze und die entsprechende Anzahl von Pages, was diese Tabelle auf Platte belegt.

Und ich kann hier auch nochmal meinen Histogramm jetzt sehen. Ich habe das jetzt nochmal abgekürzt, bloß die ersten 50 Zeichen. Und ich sehe jetzt hier, welche Werte alles in diesem Histogramm drinstehen. Und wenn ich jetzt eine Abfrage mache, Explain Analyse, die gestern von dieser Tabelle mit einer Wehrcondition,

dann sehe ich jetzt, wir haben eine sehr aktuelle Version, das ist Postgres 11, was ich hier verwendet habe. Postgres 11 kann eine Abfrage auf mehreren CPU-Kurs ausführen, das sehe ich jetzt hier auch. Das heißt also, Postgres 11 plant, dass hier zwei Wirker gestartet werden. Das heißt, zwei CPUs oder zwei CPU-Kurs fangen unabhängig voneinander an, die Tabelle zu scannen.

Das ist dieser Schritt, den ich hier sehe. Und ich sehe also parallel sequentially den Scan auf meiner Tabelle. Und ich sehe schätzt, diese Anzahl an Zeilen, diese Anzahl kommt real bei raus, bzw. das ist der eine Scan.

Insgesamt habe ich halt das geschätzt, das kommt raus, ist relativ nah beieinander, das passt also alles. Genau, und hier sehe ich jetzt nochmal, was für ein Scan mache ich. Ich mache also einen parallel sequential Scan mit dem entsprechenden Filter. Ich sehe hier die Schätzwerte hier oben, die realen Werte, hier auch nochmal die Schätzwerte und die realen Werte.

Das heißt also, das passt halbwegs. So, ich will jetzt ein paar verschiedene Scantypen zeigen, das wird knapp, sehe ich gerade. Einen sequential Scan haben wir gerade behandelt.

Ja, also wir haben jetzt noch keine Indexer dabei, nichts gar nichts. Ich überspringe das Ganze einfach mal. Ich mache jetzt einen Index-Scan auf der ersten Spalte der Tabelle. Diese Spalte ist ein primary key, damit habe ich automatisch einen Index drauf. Und ich sehe also jetzt einen Index-Scan, den er ausführt und ich sehe auch die Schätzwerte stimmen, etc.

Ist damit auch relativ flott. Andere Variante ist der Bitmap-Index-Scan. Ich habe jetzt hier auf der Tabelle mit den drei Spalten, sorry, auf meine drei Spalten jeweils einen separaten Index erstellt.

Und mache jetzt eine Abfrage, wo ich alle drei Spalten in meiner Ver-Condition irgendwie drin habe. Und der Plan ändert sich jetzt auf einen sogenannten Bitmap-Index-Scan. Das heißt, PostgreSQL verwendet jetzt an der Stelle zwei von diesen drei Indexen. Ich sehe also auch, welchen Index er verwendet. Er verwendet diese Index, kombiniert diese ganzen Indexer, macht dann mal einen Recheck.

Das hängt damit zusammen, dass der Index keine Information über die Sichtbarkeit hat. Das ist eine andere Geschichte, Multiversion-Konkurrenzikontrolle und Postgres. Und schlussendlich sehe ich also jetzt den Abfrageplan für den Bitmap-Index-Scan. Ein anderer Scan-Typ, eine andere Möglichkeit wäre, dass ich kombinierte Index erstelle.

Was habe ich jetzt gemacht? Ich habe einen neuen Index erstellt über alle drei Spalten. Und ich habe jetzt dieselbe Abfrage wie vorhin gemacht. Ich habe einen deutlich einfacheren Plan. Und ich sehe auch, die Abfragezeiten sind erheblich schneller. Das heißt also, ich habe zwar hier drei Index, aber ich habe eine relativ lange Abfragezeit.

Hier ist es immer einiges schneller. Und dann eine andere Variante noch, Index-Scan, Index-Only-Scan. Das wollte ich noch mal zeigen. Der Unterschied in den zwei Abfragen ist relativ gering. Ich habe hier einen Select-Stern von der Tabelle mit meinen Spalten irgendwie.

Und ich sehe, ich mache einen Index-Scan. Und ich habe hier Explain Analyse Select A, B, C. Ich habe jetzt nur diese drei Spalten, die in diesem kombinierten Index drin sind. Und dann sehe ich an der Stelle einen Index-Only-Scan. Das heißt, Fosco ist alles hier in der Lage, den Index wesentlich effektiver zu nutzen.

Er braucht nur in diesen Index reinzuschauen. Beziehungsweise es kann unter bestimmten Voraussetzungen oder Umständen erforderlich sein, dass er nochmal in die Tabelle reinschaut. Wie oft er in die Tabelle reinschauen muss, sehe ich jetzt hier. In dem Fall ist es gleich null. Und wir haben also einen sehr schnellen Index-Only-Scan.

Das ist eigentlich das, was man am liebsten haben möchte in Foscos. Hier habe ich noch ein Beispiel für eine Aggregation. Ich habe also jetzt gesetzt, dass ich die parallele Ausführung quasi auf null gesetzt habe. Mache jetzt eine Aggregation mit average unsam.

Und ich sehe also jetzt, er macht jetzt einen entsprechenden Aggregate. Er macht einen Scan auf der Tabelle. Und was ich zeigen wollte ist, hier in dem Beispiel, dass Foscos seit Version 9.6 und insbesondere jetzt auch die 10. und 11. Version, können also Abfragen auf mehreren CPU-Kurs parallel ausführen.

Und wir sehen also, dass die Abfragezeiten dann bei zwei Worker-Prozessen, die ich eingeschaltet habe, sich mehr als halbiert hat. Also das ist eine ziemlich krasse Performance-Geschichte. Und so kann man halt genau sehen, wie arbeitet Foscos, welchen Plan nimmt er, welche Indexe nimmt er.

Und ich kann ihm quasi bei der Arbeit zuschauen, wie er die entsprechende Abfrage ausführt. Ich habe mir das nochmal mit dem ORDERBYE gemacht. ORDERBYE ist eine interessante Geschichte, weil bei ORDERBYE muss er sortiert werden.

Für das Sortieren gibt es verschiedene Ansätze, wie ich sortiere. Wenn ich den Parameter WORKMAM relativ gering habe, sortiert er external on disk. Das heißt, das ist relativ langsam, vor allem wenn ich langsame Platten habe, wird das ziemlich langsam sein. Wenn ich WORKMAM entsprechend groß gesetzt habe, sehe ich eine andere Sortier-Methode, QuickSort.

Es hat sich auch noch der Plan geändert, das ist jetzt eine andere Geschichte an der Stelle. Was ich zeigen wollte, ist, dass ich durch eine Änderung eines Speicherparameters, nämlich WORKMAM, auch einen ganz anderen Plan bekomme, sowohl fürs Sortieren als auch für die eigentliche Abfrage.

Das ist eine wichtige Sache. Wir machen gern bei uns so Performance-Workshops etc. Schulungen. Da kann man im Detail drauf eingehen, was diese Speicherparameter bedeuten, wie man sie setzen kann, welche Fallstriche bestehen etc. Zum Schluss nochmal zusammengefasst, wie finde ich langsame Abfragen oder wie ich sie finden kann.

Da gibt es eine Extension, die wurde von meinem Vorredner schon genannt. Das ist die Pitches that Statements Extension, mit der werden alle ausgeführten Abfragen katalogisiert von Postgres. Ich sehe, welche Abfrage wie oft ausgeführt worden ist und was sie für durchschnittliche Abfragezeiten hat.

Es gibt noch die Möglichkeit, dass ich generell im Logging einschalte, dass sich Abfragen, die länger als eine bestimmte Dauer, die kann ich definieren sind, im Log auftauchen. Ich habe noch eine weitere Extension, die nennt sich Auto Explain.

Wenn ich die entsprechend einschalte und konfiguriere, sehe ich im Datenbank Log direkt die Explain-Ausgaben, der mich interessierenden langsamen Abfragen, sodass ich am Ende des Tages mir das Log anschauen kann. Es gibt Analysewerkzeuge dafür und ich dort auf die Art und Weise sehe, welche Abfragen langsam sind.

Ich sehe auch gleich den Abfrageplan dazu und kann überlegen, welchen Index brauche ich zum Beispiel. Okay, das war jetzt wahrscheinlich ein bisschen viel. Andreas Ketschmer für die Erläuterung zum Explain-Befehl. Gibt es dazu Fragen? Wir haben noch Zeit, Fragen zu beantworten.

Wie stabil ist das Ganze zwischen verschiedenen Datenbankversionen? Also wenn ich meine Anfragen und Indizes bei einer Version getunet habe und ganz genau in Explain geschaut habe und ich, keine Ahnung, meine Version installiere, muss ich von vorne anfangen oder ist das halbwegs stabil?

Das ist eigentlich über Versionsgrenzen hinweg stabil bzw. es wird in höheren Versionen in der Regel besser, weil ich ja neue Features habe, wie zum Beispiel seit Postkurs 9.6 habe ich ja die Ausführung über mehrere Kurs zum Beispiel.

Also die Abfragepläne sind in der Richtung stabil. Es können sich Details ändern, interne Optimierungen, aber in der Regel sollte es besser werden, nicht schlechter. Es gibt Negativbeispiele, aber das ist eher die Ausnahme.

Das parallele Arbeiten mit mehreren Workers ist per Default an, da muss ich nichts aktivieren, oder? Das ist, in 9.6 ist es Default aus, in 10 und 11, ich bin mir jetzt nicht ganz sicher, man sollte es nochmal checken, ob es eingeschaltet ist. Ich glaube in 11 ist es eingeschaltet, aber relativ konservativ.

Man muss natürlich schauen, wie viele CPU-Kerner habe ich und was stelle ich ein. Da gibt es ungefähr 10 verschiedene Parameter, die ich einstellen kann. Vielen Dank. Wir haben doch im Talk vorher eben schon gehört, dass manchmal das Vacuum ganz hilfreich sein kann.

Und hier haben wir jetzt dieses Log-Min-Duration-Statement. Sie haben gesagt, das geht dann ins Log-File raus. Kann man das auch in eine Table oder sowas reinschreiben, dass es automatisch analysieren kann, um dann ein Vacuum automatisch aus dem Programm raus anzutriggern, wenn ich feststelle, dass manche Queries langsam werden? Das Vacuum startet normalerweise automatisch.

Ich habe ein Auto-Vacuum in PostgreSQL, seit vielen, vielen Versionen. Früher hat man Vacuum bei Groundshop gestartet, in den 7er-Versionen, also vor 15 Jahren. Mittlerweile habe ich ein Auto-Vacuum und das kann ich konfigurieren. Das hat Default-Werte, die gelten für alle Tabellen. Und wenn ich weiß, manche Tabellen sind kritisch, kann ich dort auch per Tebe die Parameter entsprechend anpassen,

sodass im Normalbetrieb, wo ich jetzt Inserts habe, Deletes habe, Updates habe, etc., PostgreSQL selber merkt, wann welche Tabelle ein Vacuum braucht und führt das automatisch selbstständig aus. Ich muss das im Normalfall nicht manuell eingreifen.

Ich kann auch im Log einstellen, dass ich Vacuumläufe mit einem Log-File habe und kann dann auch analysieren, welche Tabelle hat einen Vacuum erfahren. Was man sich als generelle Regel merken sollte, ist, wenn ich große Änderungen am Datenbestand mache, also irgendwelche Daten importiere oder Daten lösche oder irgendwas in die Richtung mache,

dass ich anschließend manuell einen Analyse aufrufe auf diesen Tabellen. Das ist immer empfehlenswert. Gibt es noch weitere Fragen? Dann nochmal herzlichen Dank an den Andreas Kretschmer.