Weet je nog hoe het allemaal begon. Alles was voor de eerste keer en opnieuw

Over hoe we de PostgreSQL-query moesten optimaliseren en wat hieruit voortkwam.
Waarom moest je? Ja, want de afgelopen vier jaar werkte alles rustig, kalm, als een tikkende klok.
als epigraaf.

Gebaseerd op echte gebeurtenissen.
Alle namen zijn veranderd, toevalligheden zijn willekeurig.

Wanneer je een bepaald resultaat bereikt, is het altijd interessant om te onthouden wat de aanleiding was voor het begin, waar het allemaal begon.

Dus wat er als gevolg daarvan gebeurde, wordt kort beschreven in het artikel “Synthese als een van de methoden om de prestaties van PostgreSQL te verbeteren.

Het zal waarschijnlijk interessant zijn om de reeks eerdere evenementen opnieuw te creëren.
De geschiedenis heeft de exacte startdatum opgeslagen - 2018-09-10 18:02:48.
Ook is er in het verhaal een verzoek van waaruit het allemaal begon:
Probleem verzoekSELECT
p.“PARAMETER_ID” als parameter_id,
pd."PD_NAME" AS pd_naam,
pd."CUSTOMER_PARTNUMBER" AS klant_onderdeelnummer,
w. "LRM" ALS LRM,
w. "LOTID" ALS lotid,
w.“RTD_VALUE” ALS RTD_waarde,
w.“LOWER_SPEC_LIMIT” ALS lagere_spec_limiet,
w.“UPPER_SPEC_LIMIT” AS bovenste_specificatie_limiet,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS uitgegeven_naam,
s.“SPENT_DATE” AS besteed_datum,
extract(jaar vanaf "SPENT_DATE") AS jaar,
extract(maand vanaf "SPENT_DATE") als maand,
s."REPORT_NAME" AS rapportnaam,
p."STPM_NAME" AS stpm_naam,
p.“CUSTOMERPARAM_NAME” AS klantparam_naam
VAN wdata w,
besteedde s,
pmtr p,
spend_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
EN p."PARAMETER_ID" = w."PARAMETER_ID"
EN s.“SPENT_ID” = sp.“SPENT_ID”
EN pd."PD_ID" = sp."PD_ID"
EN s.“GEBRUIKDATUM” >= '2018-07-01' EN s.“GEBRUIKDATUM” <= '2018-09-30'
en s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
VAN bestede s2,
wdata w2
WAAR s2.“SPENT_ID” = w2.“SPENT_ID”
EN w2.“LRM” = w.“LRM”);

De beschrijving van het probleem is voorspelbaar standaard: “Alles is slecht. Vertel me wat het probleem is.”
Ik herinnerde me meteen een anekdote uit de tijd van 3 en een halve inch-schijven:

De lamer komt naar de hacker.
-Niets werkt voor mij, vertel me waar het probleem zit.
-In DNA...

Maar dit is uiteraard niet de manier om prestatie-incidenten op te lossen. “Het kan zijn dat ze ons niet begrijpen" (Met). We moeten het uitzoeken.
Nou, laten we graven. Misschien stapelt zich daardoor iets op.

Onderzoek gestart

Dus wat onmiddellijk met het blote oog te zien is, zonder zelfs maar toevlucht te nemen tot UITLEG.
1) JOIN's worden niet gebruikt. Dit is slecht, vooral als het aantal verbindingen meer dan één is.
2) Maar wat nog erger is, zijn de gecorreleerde subquery's en aggregatie. Dit is erg slecht.
Dit is natuurlijk slecht. Maar dit is slechts aan de ene kant. Aan de andere kant is dit heel goed, omdat het probleem duidelijk een oplossing heeft en een verzoek dat verbeterd kan worden.
Ga niet naar een waarzegster (C).
Het zoekplan is niet zo ingewikkeld, maar wel indicatief:
Uitvoeringsplan

Het meest interessante en nuttige bevindt zich, zoals gewoonlijk, aan het begin en het einde.
Geneste lus (kosten=935.84..479763226.18 rijen=3322 breedte=135) (werkelijke tijd=31.536..8220420.295 rijen=8111656 lussen=1)
Planningstijd: 3.807 ms
Uitvoeringstijd: 8222351.640 ms
De doorlooptijd bedraagt ​​ruim 2 uur.

Valse hypothesen die tijd vergen

Hypothese 1 - De optimizer maakt een fout en stelt het verkeerde plan op.

Om het uitvoeringsplan te visualiseren maken wij gebruik van de site https://explain.depesz.com/. De site toonde echter niets interessants of nuttigs. Op het eerste en tweede gezicht is er niets dat echt kan helpen. Is het mogelijk dat Volledige scan minimaal is. Doe Maar.

Hypothese 2 - Impact op de basis vanaf de autovacuümzijde, je moet de remmen verwijderen.

Maar de autovacuum-daemons gedragen zich goed, er zijn geen langlopende processen. Geen serieuze belasting. We moeten op zoek naar iets anders.

Hypothese 3 - Statistieken zijn verouderd, alles moet opnieuw worden berekend

Nogmaals, dat niet. De statistieken zijn up-to-date. Wat, gezien het gebrek aan problemen met autovacuüm, niet verrassend is.

Laten we beginnen met optimaliseren

De hoofdtabel 'wdata' is zeker niet klein, bijna 3 miljoen records.
En het is deze tabel die Volledige scan volgt.

Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") EN ((Subplan 1) = s."SPENT_DATE"))
-> Seq-scan op wdata w (kosten=0.00..574151.49 rijen=26886249 breedte=46) (werkelijke tijd=0.005..8153.565 rijen=26873950 lussen=1)
We doen het standaard ding: “kom op, laten we een index maken en alles zal vliegen.”
Er is een index gemaakt in het veld “SPENT_ID”.
Als gevolg:
Query-uitvoeringsplan met behulp van index

Nou, heeft het geholpen?
Het was: 8 222 351.640 ms (iets meer dan 2 uur)
Het werd: 6 985 431.575 ms (bijna 2 uur)
Over het algemeen dezelfde appels, zijaanzicht.
Laten we de klassiekers onthouden:
“Heb jij dezelfde, maar dan zonder vleugels? Zal zoeken".

In principe zou dit een goed resultaat kunnen worden genoemd, nou ja, niet goed, maar acceptabel. Zorg op zijn minst voor een groot rapport aan de klant, waarin wordt beschreven hoeveel er is gedaan en waarom het goed was wat er is gedaan.
Maar toch is de uiteindelijke beslissing nog ver weg. Heel ver.

En nu het meest interessante: we blijven optimaliseren, we zullen het verzoek oppoetsen

Stap één - Gebruik JOIN

Het herschreven verzoek ziet er nu zo uit (nou ja, in ieder geval mooier):
Query uitvoeren met JOINSELECT
p.“PARAMETER_ID” als parameter_id,
pd."PD_NAME" AS pd_naam,
pd."CUSTOMER_PARTNUMBER" AS klant_onderdeelnummer,
w. "LRM" ALS LRM,
w. "LOTID" ALS lotid,
w.“RTD_VALUE” ALS RTD_waarde,
w.“LOWER_SPEC_LIMIT” ALS lagere_spec_limiet,
w.“UPPER_SPEC_LIMIT” AS bovenste_specificatie_limiet,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS uitgegeven_naam,
s.“SPENT_DATE” AS besteed_datum,
extract(jaar vanaf "SPENT_DATE") AS jaar,
extract(maand vanaf "SPENT_DATE") als maand,
s."REPORT_NAME" AS rapportnaam,
p."STPM_NAME" AS stpm_naam,
p.“CUSTOMERPARAM_NAME” AS klantparam_naam
FROM wdata w INNER JOIN besteedde s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spend_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
BINNENSTE JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
WAAR
s.“SPENT_DATE” >= '2018-07-01' AND s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (SELECTEER MAX(s2.“SPENT_DATE”)
VAN wdata w2 INNER JOIN besteedde s2 AAN w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
AAN w2.“LRM” = w.“LRM” );
Planningstijd: 2.486 ms
Uitvoeringstijd: 1223680.326 ms

Het eerste resultaat dus.
Het was: 6 ms (bijna 985 uur).
Het werd: 1 223 680.326 ms (iets meer dan 20 minuten).
Goed resultaat. In principe zouden we daar weer kunnen stoppen. Maar het is zo oninteressant, je kunt niet stoppen.
VOOR

Stap twee: verwijder de gecorreleerde subquery

Gewijzigde verzoektekst:
Zonder gecorreleerde subquerySELECT
p.“PARAMETER_ID” als parameter_id,
pd."PD_NAME" AS pd_naam,
pd."CUSTOMER_PARTNUMBER" AS klant_onderdeelnummer,
w. "LRM" ALS LRM,
w. "LOTID" ALS lotid,
w.“RTD_VALUE” ALS RTD_waarde,
w.“LOWER_SPEC_LIMIT” ALS lagere_spec_limiet,
w.“UPPER_SPEC_LIMIT” AS bovenste_specificatie_limiet,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS uitgegeven_naam,
s.“SPENT_DATE” AS besteed_datum,
extract(jaar vanaf "SPENT_DATE") AS jaar,
extract(maand vanaf "SPENT_DATE") als maand,
s."REPORT_NAME" AS rapportnaam,
p."STPM_NAME" AS stpm_naam,
p.“CUSTOMERPARAM_NAME” AS klantparam_naam
VAN wdata w INNER JOIN besteedde s AAN s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spend_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
BINNENSTE JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”)
VAN bestede s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROEPEREN OP w2.“LRM”
) md aan w.“LRM” = md.“LRM”
WAAR
s."SPENT_DATE" >= '2018-07-01' EN s."SPENT_DATE" <= '2018-09-30';
Planningstijd: 2.291 ms
Uitvoeringstijd: 165021.870 ms

Het was: 1 223 680.326 ms (iets meer dan 20 minuten).
Het werd: 165 021.870 ms (iets meer dan 2 minuten).
Dit is al behoorlijk goed.
Maar zoals de Britten zeggen "Maar er is altijd een maar" Een te goed resultaat zou automatisch argwaan moeten wekken. Er is hier iets mis.

De hypothese over het corrigeren van de query om de gecorreleerde subquery te verwijderen is correct. Maar je moet het een beetje aanpassen om het eindresultaat correct te laten zijn.
Als gevolg hiervan het eerste tussenresultaat:
Bewerkte query zonder gecorreleerde subquerySELECT
p.“PARAMETER_ID” als parameter_id,
pd."PD_NAME" AS pd_naam,
pd."CUSTOMER_PARTNUMBER" AS klant_onderdeelnummer,
w. "LRM" ALS LRM,
w. "LOTID" ALS lotid,
w.“RTD_VALUE” ALS RTD_waarde,
w.“LOWER_SPEC_LIMIT” ALS lagere_spec_limiet,
w.“UPPER_SPEC_LIMIT” AS bovenste_specificatie_limiet,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS uitgegeven_naam,
s.“SPENT_DATE” AS besteed_datum,
extract(jaar vanaf s.“SPENT_DATE”) AS jaar,
extract(maand vanaf s.“SPENT_DATE”) als maand,
s."REPORT_NAME" AS rapportnaam,
p."STPM_NAME" AS stpm_naam,
p.“CUSTOMERPARAM_NAME” AS klantparam_naam
VAN wdata w INNER JOIN besteedde s AAN s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spend_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
BINNENSTE JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (SELECTEER w2.“LRM”, MAX(s2.“SPENT_DATE”) ALS “SPENT_DATE”
VAN bestede s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROEPEREN OP w2.“LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” EN md.“LRM” = w.“LRM”
WAAR
s."SPENT_DATE" >= '2018-07-01' EN s."SPENT_DATE" <= '2018-09-30';
Planningstijd: 3.192 ms
Uitvoeringstijd: 208014.134 ms

Het resultaat is dus het eerste acceptabele resultaat, dat geen schande is om aan de klant te laten zien:
Begonnen met: 8 222 351.640 ms (meer dan 2 uur)
We hebben het volgende bereikt: 1 ms (iets meer dan 223 minuten).
Resultaat (tussentijds): 208 014.134 ms (iets meer dan 3 minuten).

Uitstekend resultaat.

Totaal

Daar hadden we kunnen stoppen.
MAAR…
Eetlust komt met eten. Wie loopt, zal de weg beheersen. Elk resultaat is tussenliggend. Gestopt en overleden. Enz.
Laten we doorgaan met optimaliseren.
Goed idee. Vooral als je bedenkt dat de klant het niet eens erg vond. En er zelfs sterk voor.

Het is dus tijd voor een herontwerp van de database. De querystructuur zelf is niet meer te optimaliseren (hoewel er, zo bleek later, wel een optie is om ervoor te zorgen dat alles ook daadwerkelijk mislukt). Maar om te beginnen met het optimaliseren en ontwikkelen van het databaseontwerp is al een veelbelovend idee. En vooral interessant. Nogmaals, denk aan je jeugd. Ik ben niet meteen DBA geworden, ik ben opgegroeid als programmeur (BASIC, assembler, C, double-plus C, Oracle, plsql). Een interessant onderwerp natuurlijk voor een aparte memoires ;-).
Laten we ons echter niet laten afleiden.

aldus

Of kan het verdelen ons misschien helpen?
Spoiler - “Ja, het heeft geholpen, ook bij het optimaliseren van de prestaties.”

Maar dat is een heel ander verhaal...

Wordt vervolgd…

Bron: www.habr.com