Oer hoe't ik moast omgean mei PostgreSQL query optimalisaasje en wat kaam út dit alles.
Wêrom moasten jo? Ja, want de foargeande 4 jier wurke alles stil, kalm, as in klok tikte.
as epigraaf.
Basearre op echte eveneminten.
Alle nammen binne feroare, tafallichheden binne willekeurich.
As in bepaald resultaat wurdt berikt, is it altyd nijsgjirrich om te ûnthâlden wat de ympuls wie foar it begjin, hoe't it allegear begon.
Dat, wat barde as gefolch, wurdt koart beskreaun yn it artikel "".
It sil wierskynlik ynteressant wêze om de keatling fan eardere eveneminten opnij te meitsjen.
De skiednis hold de krekte startdatum - 2018-09-10 18:02:48.
Ek yn it ferhaal is d'r in fersyk wêrfan it allegear begon:
Probleem fersykÚTKIEZE
p. "PARAMETER_ID" as 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 upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s."SPENT_DATE" AS spend_date,
extract(jier fan "SPENT_DATE") AS jier,
extract(moanne fan "SPENT_DATE") as moanne,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdataw,
bestege s,
pmtrp,
bestege_pdsp,
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."SPENT_DATE" >= '2018-07-01' EN s."SPENT_DATE" <= '2018-09-30'
en s."SPENT_DATE" = (SELECT MAX(s2."SPENT_DATE")
FROM bestege s2,
wdat w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
EN w2."LRM" = w"LRM");
Beskriuwing fan it probleem, foarsisber standert - "Alles is min. Fertel my wat it probleem is."
Ik herinnerde my daliks in grap út 'e tiden fan 3 en in heale inch drives:
De lammer komt by de hacker.
- Neat wurket foar my, fertel my wêr't it probleem is.
-In DNA...
Mar dit is fansels net de manier om prestaasjesynsidinten op te lossen. "Wy meie net begrepen wurde"(mei). Moatte it útfine.
No, lit ús grave. Miskien sil dat dêrtroch ophope.
ynvestearring begûn
Dus, wat kin fuortendaliks sjoen wurde mei it bleate each, sûnder sels taflecht te nimmen oan 'e help fan EXPLAIN.
1) JOINs wurde net brûkt. Dit is min, benammen as it oantal ferbiningen mear as ien is.
2) Mar wat is noch slimmer - in korrelearre subquery, boppedat, mei aggregaasje. Dit is hiel slim.
Dit is min, fansels. Mar dit is mar oan 'e iene kant. Oan de oare kant is dit hiel goed, want it probleem hat dúdlik in oplossing en it fersyk kin ferbettere wurde.
Gean net nei de waarsizzer (C).
It queryplan is net sa yngewikkeld, mar frij yndikatyf:
Utfieringsplan
De meast nijsgjirrige en brûkbere, lykas gewoanlik, oan it begjin en ein.
Nested Loop (kosten=935.84..479763226.18 rigen=3322 breedte=135) (werklike tiid=31.536..8220420.295 rigen=8111656 loops=1)
Planning tiid: 3.807ms
Utfiertiid: 8222351.640ms
De útfiering tiid is mear as 2 oeren.
Falske hypotezen dy't tiid naam
Hypoteze 1- De optimizer is ferkeard, bout it ferkearde plan.
Om it útfieringsplan te visualisearjen, sille wy de side brûke . De side liet lykwols neat ynteressant of nuttich sjen. Op it earste en twadde each - neat dat koe echt helpe. Behalve - Folsleine scan is minimaal. Gean dyn gong.
Hypothese 2-Ynslach op 'e basis fan' e kant fan 'e autovacuum, jo moatte de remmen kwytreitsje.
Mar, de autovacuum-daemons gedrage har goed, d'r binne gjin lange hingjende prosessen. Elke serieuze lading - nee. Moatte sykje nei wat oars.
Hypothese 3-Statistyk is ferâldere, jo moatte opnij berekkenje alles fleane
Nochris, net dat. De statistiken binne aktueel. Wat, sjoen it gebrek oan problemen mei autovacuum, is net ferrassend.
Litte wy begjinne te optimalisearjen
De haadtabel 'wdata' is wis net lyts, hast 3 miljoen records.
En it is op dizze tafel dat Full Scan giet.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") EN ((SubPlan 1) = s."SPENT_DATE"))
-> Seq Scan op wdata w (kosten=0.00..574151.49 rigen=26886249 breedte=46) (werklike tiid=0.005..8153.565 rigen=26873950 loops=1)
Wy dogge as standert: “lite we meitsje in yndeks en alles fljocht”.
In yndeks makke op it fjild "SPENT_ID"
Dêrtroch:
Query útfiering plan mei help fan in yndeks
No, hat it holpen?
wie: 8 222 351.640 ms (krekt mear as 2 oeren)
It waard: 6 985 431.575 ms (hast 2 oeren)
Yn it algemien, deselde apels, side view.
Lit ús de klassikers ûnthâlde:
"Hawwe jo deselde, mar sûnder wjukken? Sil sykje".
Yn prinsipe kin it in goed resultaat neamd wurde, no, net goed, mar akseptabel. Jou op syn minst in grut rapport oan de klant dy't beskriuwt hoefolle is dien en wêrom wat dien is goed is.
It definitive beslút is lykwols noch fier fuort. Hiel fier.
En no is it meast nijsgjirrige ding - wy trochgean mei optimalisearjen, wy sille de query polearje
Stap ien - brûk JOIN
Herskreaune query, sjocht der no sa út (goed teminsten moaier):
Query mei help fan JOINÚTKIEZE
p. "PARAMETER_ID" as 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 upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s."SPENT_DATE" AS spend_date,
extract(jier fan "SPENT_DATE") AS jier,
extract(moanne fan "SPENT_DATE") as moanne,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN bestege 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"
WÊR
s."SPENT_DATE" >= '2018-07-01' EN s."SPENT_DATE" <= '2018-09-30'EN
s."SPENT_DATE" = (SELECT MAX(s2."SPENT_DATE")
FROM wdata w2 INNER JOIN bestege s2 ON w2."SPENT_ID"=s2."SPENT_ID"
INNER JOIN wdata w
ON w2."LRM" = w"LRM" );
Planning tiid: 2.486ms
Utfiertiid: 1223680.326ms
Dus hjir is it earste resultaat.
wie: 6 985 431.575 ms (hast 2 oeren).
It waard: 1 223 680.326 ms (krekt mear as 20 minuten).
Goed resultaat. Yn prinsipe soe der wer ophâlde wurde kinne. Mar sa oninteressant, jo kinne net stopje.
FOAR
Stap twa - Krij de korrelearre subquery kwyt
Feroare fersyktekst:
Gjin korrelearre subqueryÚTKIEZE
p. "PARAMETER_ID" as 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 upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s."SPENT_DATE" AS spend_date,
extract(jier fan "SPENT_DATE") AS jier,
extract(moanne fan "SPENT_DATE") as moanne,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN bestege 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 (SELECT w2."LRM", MAX(s2."SPENT_DATE")
FROM bestege s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROUP BY w2.LRM
) md on w. "LRM" = md. "LRM"
WÊR
s."SPENT_DATE" >= '2018-07-01' EN s."SPENT_DATE" <= '2018-09-30';
Planning tiid: 2.291ms
Utfiertiid: 165021.870ms
wie: 1 223 680.326 ms (krekt mear as 20 minuten).
It waard: 165 021.870 ms (krekt mear as 2 minuten).
Dit is al aardich goed.
Lykwols, lykas de Ingelsken sizze,Mar, der is altyd in mar". In te goed resultaat soe automatysk fertinking oproppe. Der is wat mis hjir.
De hypoteze oer it korrigearjen fan de query om de korrelearre subquery kwyt te reitsjen is korrekt. Mar it moat in bytsje tweaken om it definitive resultaat rjocht te krijen.
As gefolch, it earste tuskenresultaat:
Bewurke query sûnder korrelearre subqueryÚTKIEZE
p. "PARAMETER_ID" as 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 upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s."SPENT_DATE" AS spend_date,
extract(jier fan s. "SPENT_DATE") AS jier,
extract(moanne fan s. "SPENT_DATE") as moanne,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN bestege 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 ( SELECT w2."LRM", MAX(s2."SPENT_DATE") AS "SPENT_DATE"
FROM bestege s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROUP BY w2.LRM
) md OP md."SPENT_DATE" = s."SPENT_DATE" EN md."LRM" = w"LRM"
WÊR
s."SPENT_DATE" >= '2018-07-01' EN s."SPENT_DATE" <= '2018-09-30';
Planning tiid: 3.192ms
Utfiertiid: 208014.134ms
Dat, wat wy as resultaat hawwe, is it earste akseptabele resultaat, dat wy ús net skamje om oan 'e klant te sjen:
Begûn mei: 8 222 351.640 ms (mear as 2 oeren)
Berikke: 1 ms (krekt mear as 223 minuten).
Resultaat (tusken): 208 014.134 ms (krekt mear as 3 minuten).
Geweldich resultaat.
It resultaat
Dit koe ophâlde.
MAR…
Appetite komt mei iten. De dyk wurdt behearske troch kuierjen. Elk resultaat is tuskenbeiden. Stopte dea. ensfh.
Litte wy trochgean mei de optimalisaasje.
Geweldich idee. Benammen yn betinken nommen dat de klant der net iens op tsjin wie. En sels sterk - foar.
Dat, it is tiid om de databank opnij te ûntwerpen. De querystruktuer sels kin net mear wurde optimalisearre (hoewol't, sa't letter bliken die, d'r in opsje is foar alles om echt te fleanen). Mar no om it ûntwerp fan 'e database te optimalisearjen en te ûntwikkeljen, is dit al in heul kânsryk idee. En vooral nijsgjirrich. Nochris, tink de jeugd. Ik bin ommers net fuortendaliks in DBA wurden, ik groeide út programmeurs (basis, assembler, si, si twa kear plused, oracle, plsql). In nijsgjirrich ûnderwerp fansels foar aparte memoires ;-).
Litte wy lykwols net ôfwike.
En sa,
En miskien sil seksje ús helpe?
Spoiler - "Ja, it holp, en yn it optimalisearjen fan prestaasjes, ynklusyf."
Mar dat is in folslein oar ferhaal...
Wurdt ferfolge…
Boarne: www.habr.com