Erinnern Sie sich, wie alles begann? Alles war immer wieder zum ersten Mal

Darüber, wie ich mit der PostgreSQL-Abfrageoptimierung umgehen musste und was dabei herauskam.
Warum musstest du? Ja, denn in den letzten 4 Jahren lief alles ruhig, ruhig, als würde eine Uhr ticken.
Als Inschrift.

Basierend auf realen Ereignissen.
Alle Namen wurden geändert, Zufälle sind zufällig.

Wenn ein bestimmtes Ergebnis erreicht ist, ist es immer interessant, sich daran zu erinnern, was der Anstoß für den Anfang war und wie alles begann.

Was dabei geschah, wird im Artikel kurz beschrieben: „Synthese als eine der Methoden zur Verbesserung der PostgreSQL-Leistung".

Es wird wahrscheinlich interessant sein, die Kette früherer Ereignisse nachzubilden.
Der Verlauf enthielt das genaue Startdatum – 2018 09:10:18.
Außerdem gibt es in der Geschichte eine Bitte, mit der alles begann:
ProblemanfrageSELECT
S. „PARAMETER_ID“ als Parameter_ID,
pd. „PD_NAME“ AS pd_name,
pd. „CUSTOMER_PARTNUMBER“ AS customer_partnumber,
w."LRM" AS LRM,
w. „LOTID“ AS lotid,
w. „RTD_VALUE“ AS RTD_value,
w. „LOWER_SPEC_LIMIT“ AS Lower_spec_limit,
w. „UPPER_SPEC_LIMIT“ AS oberes_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spended_name,
s."SPENT_DATE" ASausgabedatum,
extract(year from „SPENT_DATE“) AS year,
extract(month from „SPENT_DATE“) als Monat,
s."REPORT_NAME" AS report_name,
S. „STPM_NAME“ AS stpm_name,
p"CUSTOMERPARAM_NAME" AS customerparam_name
VON wdataw,
verbrachte s,
pmtrp,
ausgegeben_pdsp,
pd pd
WHERE s."SPENT_ID" = w."SPENT_ID"
UND p."PARAMETER_ID" = w."PARAMETER_ID"
UND s."SPENT_ID" = sp."SPENT_ID"
UND pd. "PD_ID" = sp. "PD_ID"
AND s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30'
und s."SPENT_DATE" = (SELECT MAX(s2."SPENT_DATE")
VON verbrachte s2,
wdata w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
UND w2."LRM" = w."LRM");

Beschreibung des Problems, vorhersehbarer Standard – „Alles ist schlecht. Sagen Sie mir, was das Problem ist.
Mir fiel sofort ein Witz aus der Zeit der 3-Zoll-Laufwerke ein:

Der Lamer kommt zum Hacker.
- Bei mir funktioniert nichts. Sagen Sie mir, wo das Problem liegt.
-In der DNA...

Aber natürlich ist dies nicht der Weg, Leistungsvorfälle zu lösen. „Es kann sein, dass wir nicht verstanden werden" (Mit). Ich muss es herausfinden.
Nun, lasst uns graben. Vielleicht summiert sich das dadurch.

Investition begonnen

Was also sofort mit bloßem Auge sichtbar ist, ohne dass EXPLAIN in Anspruch genommen werden muss.
1) JOINs werden nicht verwendet. Das ist schlecht, insbesondere wenn die Anzahl der Verbindungen mehr als eins beträgt.
2) Aber was noch schlimmer ist – eine korrelierte Unterabfrage, außerdem mit Aggregation. Das ist sehr schlecht.
Das ist natürlich schlecht. Aber das ist nur auf der einen Seite. Andererseits ist dies sehr gut, da das Problem eindeutig eine Lösung hat und die Anfrage verbessert werden kann.
Gehen Sie nicht zum Wahrsager (C).
Der Abfrageplan ist nicht so kompliziert, aber durchaus aussagekräftig:
Ausführungsplan

Das Interessanteste und Nützlichste, wie immer, am Anfang und am Ende.
Verschachtelte Schleife (Kosten=935.84..479763226.18 Zeilen=3322, Breite=135) (tatsächliche Zeit=31.536..8220420.295 Zeilen=8111656 Schleifen=1)
Planungszeit: 3.807 ms
Ausführungszeit: 8222351.640 ms
Laufzeit über 2 Stunden.

Falsche Hypothesen, die Zeit brauchten

Hypothese 1: Der Optimierer liegt falsch und erstellt den falschen Plan.

Zur Visualisierung des Ausführungsplans nutzen wir die Website https://explain.depesz.com/. Allerdings zeigte die Seite nichts Interessantes oder Nützliches. Auf den ersten und zweiten Blick nichts, was wirklich helfen könnte. Es sei denn, der vollständige Scan ist minimal. Fortfahren.

Hypothese 2 – Aufprall auf die Basis von der Seite des Autovakuums, Sie müssen die Bremsen loswerden.

Aber die Autovacuum-Daemons verhalten sich gut, es gibt keine langwierigen Prozesse. Jede ernsthafte Belastung – nein. Ich muss nach etwas anderem suchen.

Hypothese 3 – Statistiken sind veraltet, Sie müssen alles neu berechnen, was fliegt

Auch das nicht. Die Statistiken sind aktuell. Was angesichts der fehlenden Probleme mit dem Autovakuum nicht verwunderlich ist.

Beginne mit der Optimierung

Die Haupttabelle „wdata“ ist sicherlich nicht klein, fast 3 Millionen Datensätze.
Und auf dieser Tabelle findet der vollständige Scan statt.

Hash-Bedingung: ((w.“SPENT_ID“ = s.“SPENT_ID“) UND ((SubPlan 1) = s.“SPENT_DATE“))
-> Seq-Scan auf wdata w (Kosten=0.00..574151.49 Zeilen=26886249 Breite=46) (tatsächliche Zeit=0.005..8153.565 Zeilen=26873950 Schleifen=1)
Wir handeln nach dem Standard: „Lass uns einen Index erstellen und alles fliegt“.
Einen Index für das Feld „SPENT_ID“ erstellt
Ergebnis:
Abfrageausführungsplan mithilfe eines Index

Na, hat es geholfen?
Es war: 8 222 351.640ms (etwas mehr als 2 Stunden)
Wurde: 6 ms (fast 985 Stunden)
Im Allgemeinen die gleichen Äpfel, Seitenansicht.
Erinnern wir uns an die Klassiker:
„Hast du das gleiche, aber ohne Flügel? Wird suchen".

Im Prinzip könnte man das als gutes Ergebnis bezeichnen, nun ja, nicht gut, aber akzeptabel. Stellen Sie dem Kunden zumindest einen umfassenden Bericht zur Verfügung, in dem beschrieben wird, wie viel getan wurde und warum das, was getan wurde, gut ist.
Die endgültige Entscheidung liegt jedoch noch in weiter Ferne. Sehr weit.

Und jetzt das Interessanteste: Wir optimieren weiter und verfeinern die Abfrage

Schritt eins – JOIN verwenden

Neu geschriebene Abfrage, sieht jetzt so aus (na ja, zumindest hübscher):
Abfrage mit JOINSELECT
S. „PARAMETER_ID“ als Parameter_ID,
pd. „PD_NAME“ AS pd_name,
pd. „CUSTOMER_PARTNUMBER“ AS customer_partnumber,
w."LRM" AS LRM,
w. „LOTID“ AS lotid,
w. „RTD_VALUE“ AS RTD_value,
w. „LOWER_SPEC_LIMIT“ AS Lower_spec_limit,
w. „UPPER_SPEC_LIMIT“ AS oberes_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spended_name,
s."SPENT_DATE" ASausgabedatum,
extract(year from „SPENT_DATE“) AS year,
extract(month from „SPENT_DATE“) als Monat,
s."REPORT_NAME" AS report_name,
S. „STPM_NAME“ AS stpm_name,
p"CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN ausgegeben s ON w."SPENT_ID"=s."SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN spended_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
WO
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30'AND
s."SPENT_DATE" = (SELECT MAX(s2."SPENT_DATE")
FROM wdata w2 INNER JOIN ausgegeben s2 ON w2."SPENT_ID"=s2."SPENT_ID"
INNER JOIN wdata w
ON w2."LRM" = w."LRM" );
Planungszeit: 2.486 ms
Ausführungszeit: 1223680.326 ms

Hier also das erste Ergebnis.
Es war: 6 985 431.575 ms (fast 2 Stunden).
Wurde: 1 ms (etwas mehr als 223 Minuten).
Gutes Ergebnis. Grundsätzlich wäre es auch hier wieder möglich, aufzuhören. Aber so uninteressant, dass man nicht aufhören kann.
Für

Schritt zwei – Entfernen Sie die korrelierte Unterabfrage

Geänderter Anfragetext:
Keine korrelierte UnterabfrageSELECT
S. „PARAMETER_ID“ als Parameter_ID,
pd. „PD_NAME“ AS pd_name,
pd. „CUSTOMER_PARTNUMBER“ AS customer_partnumber,
w."LRM" AS LRM,
w. „LOTID“ AS lotid,
w. „RTD_VALUE“ AS RTD_value,
w. „LOWER_SPEC_LIMIT“ AS Lower_spec_limit,
w. „UPPER_SPEC_LIMIT“ AS oberes_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spended_name,
s."SPENT_DATE" ASausgabedatum,
extract(year from „SPENT_DATE“) AS year,
extract(month from „SPENT_DATE“) als Monat,
s."REPORT_NAME" AS report_name,
S. „STPM_NAME“ AS stpm_name,
p"CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN ausgegeben s ON s."SPENT_ID" = w"SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN spended_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
INNER JOIN (SELECT w2."LRM", MAX(s2"SPENT_DATE")
FROM verbraucht s2 INNER JOIN wdata w2 ON s2. "SPENT_ID" = w2. "SPENT_ID"
GRUPPE NACH w2.LRM
) md auf w. „LRM“ = md. „LRM“
WO
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Planungszeit: 2.291 ms
Ausführungszeit: 165021.870 ms

Es war: 1 ms (etwas mehr als 223 Minuten).
Wurde: 165 021.870 ms (etwas mehr als 2 Minuten).
Das ist schon ganz gut.
Wie die Engländer jedoch sagen:Aber es gibt immer ein Aber". Ein zu gutes Ergebnis sollte automatisch Verdacht erregen. Da stimmt etwas nicht.

Die Hypothese, die Abfrage zu korrigieren, um die korrelierte Unterabfrage zu entfernen, ist richtig. Es bedarf jedoch einer kleinen Feinabstimmung, um das richtige Endergebnis zu erzielen.
Als Ergebnis das erste Zwischenergebnis:
Bearbeitete Abfrage ohne korrelierte UnterabfrageSELECT
S. „PARAMETER_ID“ als Parameter_ID,
pd. „PD_NAME“ AS pd_name,
pd. „CUSTOMER_PARTNUMBER“ AS customer_partnumber,
w."LRM" AS LRM,
w. „LOTID“ AS lotid,
w. „RTD_VALUE“ AS RTD_value,
w. „LOWER_SPEC_LIMIT“ AS Lower_spec_limit,
w. „UPPER_SPEC_LIMIT“ AS oberes_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spended_name,
s."SPENT_DATE" ASausgabedatum,
extract(year from s. „SPENT_DATE“) AS year,
extract(month from s. „SPENT_DATE“) als Monat,
s."REPORT_NAME" AS report_name,
S. „STPM_NAME“ AS stpm_name,
p"CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN ausgegeben s ON s."SPENT_ID" = w"SPENT_ID"
INNER JOIN pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN spended_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd."PD_ID" = sp."PD_ID"
INNER JOIN ( SELECT w2."LRM", MAX(s2"SPENT_DATE") AS "SPENT_DATE"
FROM verbraucht s2 INNER JOIN wdata w2 ON s2. "SPENT_ID" = w2. "SPENT_ID"
GRUPPE NACH w2.LRM
) md ON md."SPENT_DATE" = s."SPENT_DATE" AND md."LRM" = w."LRM"
WO
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Planungszeit: 3.192 ms
Ausführungszeit: 208014.134 ms

Das Ergebnis ist also das erste akzeptable Ergebnis, das wir dem Kunden gerne zeigen:
Begann mit: 8 ms (mehr als 222 Stunden)
Erreicht: 1 ms (knapp über 223 Minuten).
Ergebnis (mittelschwer): 208 014.134 ms (etwas mehr als 3 Minuten).

Tolles Ergebnis.

Ergebnis

Das hätte aufhören können.
ABER ...
Der Appetit kommt beim Essen. Der Weg wird zu Fuß bewältigt. Jedes Ergebnis ist ein Zwischenergebnis. Stehengeblieben. Usw.
Fahren wir mit der Optimierung fort.
Eine super Idee. Vor allem, wenn man bedenkt, dass der Kunde nicht einmal dagegen war. Und sogar stark - für.

Es ist also an der Zeit, die Datenbank neu zu gestalten. Die Abfragestruktur selbst kann nicht mehr optimiert werden (obwohl, wie sich später herausstellte, die Möglichkeit besteht, dass alles wirklich fliegt). Aber nun das Design der Datenbank zu optimieren und weiterzuentwickeln, das ist schon eine sehr vielversprechende Idee. Und vor allem interessant. Denken Sie noch einmal an die Jugend. Schließlich bin ich nicht sofort DBA geworden, ich bin aus Programmierern hervorgegangen (Basic, Assembler, Si, Si Double Plused, Oracle, Plsql). Ein interessantes Thema natürlich für separate Memoiren ;-).
Lassen Sie uns jedoch nicht abschweifen.

somit

Und vielleicht hilft uns die Unterteilung?
Spoiler – „Ja, es hat geholfen, auch bei der Optimierung der Leistung.“

Aber das ist eine ganz andere Geschichte ...

Fortsetzung folgt…

Source: habr.com