Om hur jag var tvungen att optimera PostgreSQL-frågan och vad som kom ut ur det hela.
Varför var du tvungen? Ja, för de senaste 4 åren fungerade allt tyst, lugnt, som en klocka som tickade.
som epigraf.
Baserat på verkliga händelser.
Alla namn har ändrats, tillfälligheter är slumpmässiga.
När du uppnår ett visst resultat är det alltid intressant att komma ihåg vad som var drivkraften till början, var allt började.
Så vad som hände som ett resultat beskrivs kortfattat i artikeln "".
Det ska nog bli intressant att återskapa kedjan av tidigare händelser.
Historiken sparade det exakta startdatumet - 2018-09-10 18:02:48.
I berättelsen finns det också en begäran från vilken allt började:
ProblemförfråganVÄLJA
s. "PARAMETER_ID" som parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" SOM LRM,
w. "LOTID" SOM lotid,
w."RTD_VALUE" AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS övre_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s.“SPENT_DATE” SOM spend_date,
extrahera(år från "SPENT_DATE") AS år,
extrahera (månad från "SPENT_DATE") som månad,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.”CUSTOMERPARAM_NAME” AS customerparam_name
FRÅN wdata w,
spenderade s,
pmtr p,
spend_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
OCH p."PARAMETER_ID" = w."PARAMETER_ID"
OCH s.“SPENT_ID” = sp.“SPENT_ID”
AND pd."PD_ID" = sp."PD_ID"
OCH s.“SPENT_DATE” >= '2018-07-01' OCH s.“SPENT_DATE” <= '2018-09-30'
och s.“SPENT_DATE” = (VÄLJ MAX(s2.“SPENT_DATE”)
FRÅN spenderat s2,
wdata w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
AND w2."LRM" = w."LRM");
Beskrivning av problemet är förutsägbart standard - "Allt är dåligt. Berätta vad problemet är."
Jag kom genast ihåg en anekdot från tiden med 3 och en halv tums körningar:
Lameren kommer till hackaren.
-Inget fungerar för mig, berätta var problemet är.
-I DNA...
Men det här är naturligtvis inte sättet att lösa prestationsincidenter. "De kanske inte förstår oss"(Med). Vi måste ta reda på det.
Nåväl, låt oss gräva. Kanske kommer något att ackumuleras som ett resultat.
Utredning startade
Så, vad kan ses omedelbart med blotta ögat, utan att ens ta till EXPAIN.
1) JOINs används inte. Detta är dåligt, särskilt om antalet anslutningar är fler än en.
2) Men vad som är ännu värre är korrelerade delfrågor, dessutom med aggregering. Det här är väldigt dåligt.
Detta är naturligtvis dåligt. Men detta är bara å ena sidan. Å andra sidan är detta mycket bra, eftersom problemet helt klart har en lösning och en begäran som kan förbättras.
Gå inte till en spåkvinna (C).
Frågeplanen är inte så komplicerad, men den är ganska vägledande:
Utförandeplan
Det mest intressanta och användbara, som vanligt, är i början och slutet.
Kapslad loop (kostnad=935.84..479763226.18 rader=3322 bredd=135) (faktisk tid=31.536..8220420.295 rader=8111656 loopar=1)
Planeringstid: 3.807 ms
Utförandetid: 8222351.640 ms
Genomförandetiden är mer än 2 timmar.
Falska hypoteser som tog tid
Hypotes 1 - Optimeraren gör ett misstag och bygger fel plan.
För att visualisera genomförandeplanen kommer vi att använda sajten . Men sajten visade inget intressant eller användbart. Vid första och andra anblicken finns det inget som verkligen skulle kunna hjälpa. Är det möjligt att Full Scan är minimal. Varsågod.
Hypotes 2-Påverkan på basen från autovakuumsidan, du måste bli av med bromsarna.
Men autovakuumdemonerna beter sig bra, det finns inga långa processer. Ingen allvarlig belastning. Vi måste leta efter något annat.
Hypotes 3 - Statistik är föråldrad, allt måste räknas om
Återigen, inte det. Statistiken är aktuell. Vilket, med tanke på bristen på problem med autovakuum, inte är förvånande.
Låt oss börja optimera
Huvudtabellen 'wdata' är verkligen inte liten, nästan 3 miljoner poster.
Och det är denna tabell som Full Scan följer.
Hash Cond: ((w."SPENT_ID" = s"SPENT_ID") OCH ((SubPlan 1) = s"SPENT_DATE"))
-> Seq Scan på wdata w (kostnad=0.00..574151.49 rader=26886249 bredd=46) (faktisk tid=0.005..8153.565 rader=26873950 loopar=1)
Vi gör standardgrejen: "kom igen, låt oss göra ett index och allt kommer att flyga."
Skapat ett index i fältet "SPENT_ID".
Som ett resultat:
Fråga exekveringsplan med hjälp av index
Nåväl, hjälpte det?
Var: 8 222 351.640ms (lite mer än 2 timmar)
Det blev: 6 985 431.575 ms (nästan 2 timmar)
I allmänhet samma äpplen, sidovy.
Låt oss komma ihåg klassikerna:
”Har du samma, men utan vingar? Kommer att söka".
I princip skulle detta kunna kallas ett bra resultat, tja, inte bra, men acceptabelt. Ge åtminstone en stor rapport till kunden som beskriver hur mycket som har gjorts och varför det som gjordes var bra.
Men ändå är det slutgiltiga beslutet långt borta. Väldigt långt.
Och nu är det mest intressanta - vi fortsätter att optimera, vi kommer att polera begäran
Steg ett - Använd JOIN
Den omskrivna begäran ser nu ut så här (väl åtminstone vackrare):
Fråga med JOINVÄLJA
s. "PARAMETER_ID" som parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" SOM LRM,
w. "LOTID" SOM lotid,
w."RTD_VALUE" AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS övre_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s.“SPENT_DATE” SOM spend_date,
extrahera(år från "SPENT_DATE") AS år,
extrahera (månad från "SPENT_DATE") som månad,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.”CUSTOMERPARAM_NAME” AS customerparam_name
FRÅN wdata w INNER JOIN spenderade s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
VAR
s.“SPENT_DATE” >= '2018-07-01' OCH s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (VÄLJ MAX(s2.“SPENT_DATE”)
FRÅN wdata w2 INNER JOIN spenderade s2 PÅ w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
Planeringstid: 2.486 ms
Utförandetid: 1223680.326 ms
Så det första resultatet.
Var: 6 985 431.575 ms (nästan 2 timmar).
Det blev: 1 223 680.326 ms (drygt 20 minuter).
Bra resultat. I princip skulle vi återigen kunna sluta där. Men det är så ointressant att man inte kan sluta.
FÖR
Steg två - bli av med den korrelerade underfrågan
Ändrad text för begäran:
Utan korrelerad underfrågaVÄLJA
s. "PARAMETER_ID" som parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" SOM LRM,
w. "LOTID" SOM lotid,
w."RTD_VALUE" AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS övre_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s.“SPENT_DATE” SOM spend_date,
extrahera(år från "SPENT_DATE") AS år,
extrahera (månad från "SPENT_DATE") som månad,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.”CUSTOMERPARAM_NAME” AS customerparam_name
FRÅN wdata w INNER JOIN spenderade s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (VÄLJ w2.“LRM”, MAX(s2.“SPENT_DATE”)
FRÅN spenderat s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP BY w2.“LRM”
) md på w.“LRM” = md.“LRM”
VAR
s."SPENT_DATE" >= '2018-07-01' OCH s."SPENT_DATE" <= '2018-09-30';
Planeringstid: 2.291 ms
Utförandetid: 165021.870 ms
Var: 1 223 680.326 ms (drygt 20 minuter).
Det blev: 165 021.870 ms (drygt 2 minuter).
Det här är redan ganska bra.
Men som britterna säger "Men det finns alltid ett men" Ett för bra resultat bör automatiskt väcka misstankar. Något är fel här.
Hypotesen om att korrigera frågan för att bli av med den korrelerade underfrågan är korrekt. Men du måste justera det lite för att slutresultatet ska bli korrekt.
Som ett resultat, det första mellanresultatet:
Redigerad fråga utan korrelerad underfrågaVÄLJA
s. "PARAMETER_ID" som parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" SOM LRM,
w. "LOTID" SOM lotid,
w."RTD_VALUE" AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS övre_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s.“SPENT_DATE” SOM spend_date,
extrahera(år från s.“SPENT_DATE”) AS år,
extrahera (månad från s. "SPENT_DATE") som månad,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.”CUSTOMERPARAM_NAME” AS customerparam_name
FRÅN wdata w INNER JOIN spenderade s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (VÄLJ w2.“LRM”, MAX(s2.“SPENT_DATE”) SOM “SPENT_DATE”
FRÅN spenderat s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP BY w2.“LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” OCH md.“LRM” = w.“LRM”
VAR
s."SPENT_DATE" >= '2018-07-01' OCH s."SPENT_DATE" <= '2018-09-30';
Planeringstid: 3.192 ms
Utförandetid: 208014.134 ms
Så det vi slutar med är det första acceptabla resultatet, vilket inte är synd att visa för kunden:
Började med: 8 222 351.640 ms (mer än 2 timmar)
Vi lyckades uppnå: 1 223 680.326 ms (lite mer än 20 minuter).
Resultat (interim): 208 014.134 ms (drygt 3 minuter).
Utmärkt resultat.
Totalt
Vi kunde ha stannat där.
MEN…
Aptit kommer med att äta. Den som går kommer att bemästra vägen. Alla resultat är mellanliggande. Stannade och dog. Etc.
Låt oss fortsätta optimeringen.
Bra ide. Speciellt med tanke på att kunden inte ens hade något emot det. Och till och med starkt för det.
Så det är dags för en omstrukturering av databasen. Själva frågestrukturen kan inte längre optimeras (även om det, som det visade sig senare, finns ett alternativ för att säkerställa att allt faktiskt misslyckas). Men att börja optimera och utveckla databasdesignen är redan en mycket lovande idé. Och viktigast av allt intressant. Återigen, kom ihåg din ungdom. Jag blev trots allt inte direkt DBA, jag växte upp som programmerare (BASIC, assembler, C, double-plus C, Oracle, plsql). Ett intressant ämne förstås för en separat memoarbok ;-).
Men låt oss inte bli distraherade.
Så,
Eller kanske partitionering hjälper oss?
Spoiler - "Ja, det hjälpte, inklusive att optimera prestanda."
Men det är en helt annan historia...
Fortsättning följer…
Källa: will.com