Èske w sonje ki jan tout bagay te kòmanse? Tout bagay te pou premye fwa e ankò

Konsènan ki jan nou te dwe optimize rechèch PostgreSQL la ak sa ki soti nan li tout.
Poukisa ou te oblije? Wi, paske pou 4 ane anvan yo tout bagay te travay trankil, avèk kalm, tankou yon revèy k ap kouri.
Kòm yon epigraf.

Ki baze sou evènman reyèl.
Tout non yo te chanje, konyensidans yo o aza.

Lè ou reyalize yon rezilta sèten, li toujou enteresan sonje sa ki te UN pou kòmansman an, kote tout bagay te kòmanse.

Se konsa, sa ki te pase kòm rezilta yo dekri yon ti tan nan atik la "Sentèz kòm youn nan metòd pou amelyore pèfòmans PostgreSQL'.

Li pral pwobableman enteresan yo rkree chèn nan evènman anvan yo.
Istwa a te sove dat egzak kòmansman an - 2018-09-10 18:02:48.
Epitou, nan istwa a gen yon demann ki soti nan ki tout te kòmanse:
Pwoblèm demannChwazi
p.“PARAMETER_ID” kòm 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 limit_spec_pi ba,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s.“SPENT_DATE” AS spent_date,
ekstrè (ane soti nan "SPENT_DATE") AS ane,
ekstrè (mwa soti nan "SPENT_DATE") kòm mwa,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
SOTI nan wdata w,
depanse s,
pmtr p,
spent_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
AK p."PARAMETER_ID" = w."PARAMETER_ID"
AK s.“SPENT_ID” = sp.“SPENT_ID”
AND pd."PD_ID" = sp."PD_ID"
AK "DAT_DEPANSE" >= '2018-07-01' AK "DAT_DEPPE" <= '2018-09-30'
ak s.“SPENT_DATE” = (CHWAZI MAX(s2.“SPENT_DATE”)
SOTI nan depanse s2,
wdata w2
KOTE s2.“SPENT_ID” = w2.“SPENT_ID”
AK w2.“LRM” = w.“LRM”);

Deskripsyon nan pwoblèm nan se previzib estanda - "Tout se move. Di m ki pwoblèm nan ye."
Mwen imedyatman sonje yon anekdot ki soti nan tan kondui 3 pous ak mwatye:

Lamer a vin jwenn pirate a.
-Pa gen anyen ki mache pou mwen, dim kote pwoblèm nan.
- Nan ADN...

Men, nan kou, sa a se pa fason pou rezoud ensidan pèfòmans. "Yo ka pa konprann nou" (Ak). Nou bezwen kalkile li.
Oke, ann fouye. Petèt yon bagay pral akimile kòm yon rezilta.

Envestigasyon te kòmanse

Se konsa, sa ki ka wè imedyatman ak je a toutouni, san yo pa menm recourir EXPLICATION.
1) JOIN yo pa itilize. Sa a se move, sitou si kantite koneksyon an plis pase yon sèl.
2) Men, sa ki vin pi mal la se subqueries korelasyon, anplis, ak agrégation. Sa a se trè move.
Sa a se move nan kou. Men, sa a se sèlman sou yon bò. Nan lòt men an, sa a se trè bon, paske pwoblèm nan klèman gen yon solisyon ak yon demann ki ka amelyore.
Pa ale nan yon divinò (C).
Plan rechèch la se pa sa ki konplike, men li se byen indicatif:
Plan Egzekisyon

Ki pi enteresan ak itil, kòm dabitid, se nan kòmansman an ak nan fen.
Loop anbrike (pri = 935.84..479763226.18 ranje = 3322 lajè = 135) (tan aktyèl = 31.536..8220420.295 ranje = 8111656 bouk = 1)
Tan planifikasyon: 3.807 ms
Tan ekzekisyon: 8222351.640 ms
Tan fini se plis pase 2 èdtan.

Fo ipotèz ki te pran tan

Ipotèz 1 - Optimiseur a fè yon erè epi li bati move plan an.

Pou visualize plan ekzekisyon an, nou pral sèvi ak sit la https://explain.depesz.com/. Sepandan, sit la pa t montre anyen ki enteresan oswa itil. Nan premye ak dezyèm gade, pa gen anyen ki ta ka vrèman ede. Èske li posib ke Full Scan se minim. Ale pi devan.

Ipotèz 2-Enpak sou baz la soti nan bò autovacuum, ou bezwen debarase m de fren yo.

Men, demon otovakyòm yo konpòte yo byen, pa gen okenn pwosesis ki long. Pa gen chaj grav. Nou bezwen chèche yon lòt bagay.

Ipotèz 3 - Estatistik yo demode, tout bagay bezwen yo dwe rekalkile

Ankò, pa sa. Statistik yo ajou. Ki, bay mank de pwoblèm ak autovacuum, se pa etone.

Ann kòmanse optimize

Tablo prensipal 'wdata' se sètènman pa piti, prèske 3 milyon dosye.
Epi li se tablo sa a ki Full Scan swiv.

Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") AK ((SubPlan 1) = s."SPENT_DATE"))
-> Seq Scan sou wdata w (pri = 0.00..574151.49 ranje = 26886249 lajè = 46) (tan aktyèl = 0.005..8153.565 ranje = 26873950 bouk = 1)
Nou fè bagay nòmal la: "Vini non, ann fè yon endèks ak tout bagay pral vole."
Kreye yon endèks sou jaden "SPENT_ID".
Kòm yon rezilta:
Plan ekzekisyon demann lè l sèvi avèk endèks

Oke, li te ede?
Te: 8 222 351.640 ms (yon ti kras plis pase 2 èdtan)
Te vin: 6 985 431.575 ms (prèske 2 èdtan)
An jeneral, pòm yo menm, View bò.
Ann sonje klasik yo:
"Èske ou gen menm bagay la, men san zèl? Pral chèche".

Nan prensip, sa a ta ka rele yon bon rezilta, byen, pa bon, men akseptab. Omwen a, bay kliyan an yon gwo rapò ki dekri konbyen yo te fè ak poukisa sa ki te fè a te bon.
Men, toujou, desizyon final la toujou byen lwen. Byen lwen.

Epi, koulye a bagay ki pi enteresan - nou kontinye optimize, nou pral poli demann lan

Premye etap - Sèvi ak JOIN

Demann reekri kounye a sanble sa a (byen omwen pi bèl):
Rekèt lè l sèvi avèk JOINChwazi
p.“PARAMETER_ID” kòm 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 limit_spec_pi ba,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s.“SPENT_DATE” AS spent_date,
ekstrè (ane soti nan "SPENT_DATE") AS ane,
ekstrè (mwa soti nan "SPENT_DATE") kòm mwa,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN depanse 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”
KI KOTE
s.“PENT_DATE” >= '2018-07-01' AK s.“PENT_DATE” <= '2018-09-30'AK
s.“SPENT_DATE” = (CHWAZI MAX(s2.“SPENT_DATE”)
FROM wdata w2 INNER JOIN te pase s2 sou w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
Tan planifikasyon: 2.486 ms
Tan ekzekisyon: 1223680.326 ms

Se konsa, premye rezilta a.
Te: 6 ms (prèske 985 èdtan).
Te vin: 1 223 680.326 ms (yon ti kras plis pase 20 minit).
Bon rezilta. An prensip, ankò, nou ta ka sispann la. Men, li tèlman san enterè, ou pa ka sispann.
POU

Etap de - debarase m de subquery ki gen rapò

Chanje tèks demann:
San yo pa korelasyon subqueryChwazi
p.“PARAMETER_ID” kòm 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 limit_spec_pi ba,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s.“SPENT_DATE” AS spent_date,
ekstrè (ane soti nan "SPENT_DATE") AS ane,
ekstrè (mwa soti nan "SPENT_DATE") kòm mwa,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN depanse 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 (CHWAZI w2.“LRM”, MAX(s2.“PENT_DATE”)
FROM spent s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP BY w2.“LRM”
) md sou w.“LRM” = md.“LRM”
KI KOTE
s."PENT_DATE" >= '2018-07-01' AK s."PENT_DATE" <= '2018-09-30';
Tan planifikasyon: 2.291 ms
Tan ekzekisyon: 165021.870 ms

Te: 1 223 680.326 ms (yon ti kras plis pase 20 minit).
Te vin: 165 021.870 ms (yon ti kras plis pase 2 minit).
Sa a deja byen bon.
Sepandan, jan Britanik yo di "Men, toujou gen yon men" Yon rezilta ki twò bon ta dwe otomatikman leve sispèk. Gen yon bagay ki mal isit la.

Ipotèz sou korije rechèch la pou debarase m de subquery ki gen rapò a kòrèk. Men, ou bezwen ajiste li yon ti kras pou rezilta final la kòrèk.
Kòm yon rezilta, premye rezilta entèmedyè a:
Rekèt modifye san yo pa korelasyon subqueryChwazi
p.“PARAMETER_ID” kòm 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 limit_spec_pi ba,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s.“SPENT_DATE” AS spent_date,
ekstrè(ane soti nan s.“SPENT_DATE”) AS ane,
extrait(mwa apati "SPENT_DATE") kòm mwa,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN depanse 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 (CHWAZI w2.“LRM”, MAX(s2.“PENT_DATE”) AS “SPENT_DATE”
FROM spent s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP BY w2.“LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” AK md.“LRM” = w.“LRM”
KI KOTE
s."PENT_DATE" >= '2018-07-01' AK s."PENT_DATE" <= '2018-09-30';
Tan planifikasyon: 3.192 ms
Tan ekzekisyon: 208014.134 ms

Se konsa, sa nou fini ak premye rezilta akseptab, ki pa yon wont montre kliyan an:
Te kòmanse avèk: 8 222 351.640 ms (plis pase 2 èdtan)
Nou te rive reyalize: 1 ms (yon ti kras plis pase 223 minit).
Rezilta (pwovizwa): 208 014.134 ms (yon ti kras plis pase 3 minit).

Ekselan rezilta.

Total

Nou te ka kanpe la.
MEN…
Apeti vini ak manje. Moun k'ap mache ap mèt wout la. Nenpòt rezilta se entèmedyè. Sispann epi mouri. elatriye.
Ann kontinye optimize.
Gwo lide. Espesyalman konsidere ke kliyan an pa t 'menm lide. E menm fòtman pou li.

Se konsa, li lè pou yon redesign baz done. Estrikti rechèch la tèt li pa ka optimize ankò (byenke, jan li te tounen soti pita, gen yon opsyon asire ke tout bagay aktyèlman echwe). Men, yo kòmanse optimize ak devlope konsepsyon baz done a se deja yon lide trè pwomèt. Ak sa ki pi enpòtan enteresan. Ankò, sonje jèn ou. Apre yo tout, mwen pa t 'imedyatman vin yon DBA, mwen te grandi kòm yon pwogramè (BASIC, assembler, C, doub-plus C, Oracle, plsql). Yon sijè enteresan, nan kou, pou yon memwa separe ;-).
Sepandan, annou pa distrè.

Se konsa,

Oswa petèt patisyon ap ede nou?
Spoiler - "Wi, li te ede, enkli nan optimize pèfòmans."

Men, sa se yon istwa konplètman diferan ...

A kontinye…

Sous: www.habr.com