Naaalala mo ba kung paano nagsimula ang lahat. Ang lahat ay sa unang pagkakataon at muli

Tungkol sa kung paano ko kinailangan na i-optimize ang PostgreSQL query at kung ano ang lumabas sa lahat ng ito.
Bakit kailangan mo? Oo, dahil sa nakaraang 4 na taon ang lahat ay nagtrabaho nang tahimik, mahinahon, tulad ng isang orasan.
Bilang isang epigraph.

Batay sa mga totoong pangyayari.
Ang lahat ng mga pangalan ay binago, ang mga pagkakataon ay random.

Kapag nakamit mo ang isang tiyak na resulta, palaging kawili-wiling alalahanin kung ano ang naging impetus para sa simula, kung saan nagsimula ang lahat.

Kaya, kung ano ang nangyari bilang isang resulta ay maikling inilarawan sa artikulong "Synthesis bilang isa sa mga pamamaraan para sa pagpapabuti ng pagganap ng PostgreSQL'.

Marahil ay magiging kawili-wiling muling likhain ang hanay ng mga nakaraang kaganapan.
Nai-save ng kasaysayan ang eksaktong petsa ng pagsisimula - 2018-09-10 18:02:48.
Gayundin, sa kuwento mayroong isang kahilingan kung saan nagsimula ang lahat:
Kahilingan ng problemaPiliin
p.“PARAMETER_ID” bilang parameter_id,
pd."PD_NAME" BILANG pd_name,
pd."CUSTOMER_PARTNUMBER" BILANG customer_partnumber,
w. "LRM" BILANG LRM,
w. "LOTID" BILANG lotid,
w.“RTD_VALUE” BILANG RTD_value,
w.“LOWER_SPEC_LIMIT” BILANG lower_spec_limit,
w.“UPPER_SPEC_LIMIT” BILANG upper_spec_limit,
p."TYPE_CALCUL" BILANG type_calcul,
s."SPENT_NAME" BILANG spent_name,
s.“SPENT_DATE” BILANG spent_date,
extract(taon mula sa "SPENT_DATE") BILANG taon,
extract(buwan mula sa "SPENT_DATE") bilang buwan,
s."REPORT_NAME" BILANG report_name,
p."STPM_NAME" BILANG stpm_name,
p.“CUSTOMERPARAM_NAME” BILANG customerparam_name
MULA sa wdata w,
nagastos s,
pmtr p,
nagastos_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
AT p."PARAMETER_ID" = w."PARAMETER_ID"
AT s.“SPENT_ID” = sp.“SPENT_ID”
AT pd."PD_ID" = sp."PD_ID"
AT s.“SPENT_DATE” >= '2018-07-01' AT s.“SPENT_DATE” <= '2018-09-30'
at s.“SPENT_DATE” = (PUMILI NG MAX(s2.“SPENT_DATE”)
MULA sa nagastos s2,
wdata w2
SAAN s2.“SPENT_ID” = w2.“SPENT_ID”
AT w2. “LRM” = w. “LRM”);

Ang paglalarawan ng problema ay predictably standard - "Lahat ay masama. Sabihin mo sa akin kung ano ang problema."
Naalala ko kaagad ang isang anekdota mula sa mga oras ng 3 at kalahating pulgadang drive:

Dumating ang lamer sa hacker.
-Walang gumagana para sa akin, sabihin sa akin kung saan ang problema.
-Sa DNA...

Ngunit siyempre, hindi ito ang paraan upang malutas ang mga insidente sa pagganap. “Baka hindi nila tayo maintindihan"(Kasama). Kailangan nating malaman ito.
Well, maghukay tayo. Baka may maiipon bilang resulta.

Nagsimula ang imbestigasyon

Kaya, kung ano ang makikita kaagad sa mata, nang hindi man lang nag-EXPLAIN.
1) Ang mga JOIN ay hindi ginagamit. Ito ay masama, lalo na kung ang bilang ng mga koneksyon ay higit sa isa.
2) Ngunit ang mas masahol pa ay ang mga nauugnay na subquery, bukod pa rito, sa pagsasama-sama. Ito ay napakasama.
Ito ay masama siyempre. Ngunit ito ay sa isang banda lamang. Sa kabilang banda, ito ay napakahusay, dahil ang problema ay malinaw na may solusyon at isang kahilingan na maaaring mapabuti.
Huwag pumunta sa isang manghuhula (C).
Ang query plan ay hindi ganoon kakomplikado, ngunit ito ay lubos na nagpapahiwatig:
Plano ng Pagpapatupad

Ang pinaka-kawili-wili at kapaki-pakinabang, gaya ng dati, ay nasa simula at wakas.
Nested Loop (gastos=935.84..479763226.18 row=3322 width=135) (aktwal na oras=31.536..8220420.295 row=8111656 loops=1)
Oras ng pagpaplano: 3.807 ms
Oras ng pagpapatupad: 8222351.640 ms
Ang oras ng pagkumpleto ay higit sa 2 oras.

Mga maling hypotheses na nagtagal

Hypothesis 1 - Ang optimizer ay nagkakamali at gumagawa ng maling plano.

Upang mailarawan ang plano sa pagpapatupad, gagamitin namin ang site https://explain.depesz.com/. Gayunpaman, ang site ay hindi nagpakita ng anumang kawili-wili o kapaki-pakinabang. Sa una at pangalawang sulyap, wala talagang makakatulong. Posible bang ang Full Scan ay minimal. Sige lang.

Hypothesis 2-Epekto sa base mula sa autovacuum side, kailangan mong alisin ang mga preno.

Ngunit ang mga daemon ng autovacuum ay kumikilos nang maayos, walang mga prosesong pangmatagalan. Walang seryosong load. Kailangan nating maghanap ng iba.

Hypothesis 3 - Ang mga istatistika ay hindi napapanahon, ang lahat ay kailangang muling kalkulahin

Muli, hindi iyon. Ang mga istatistika ay napapanahon. Alin, dahil sa kakulangan ng mga problema sa autovacuum, ay hindi nakakagulat.

Simulan natin ang pag-optimize

Ang pangunahing talahanayan na 'wdata' ay tiyak na hindi maliit, halos 3 milyong mga talaan.
At ang talahanayang ito ang sinusundan ng Full Scan.

Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") AT ((SubPlan 1) = s."SPENT_DATE"))
-> Seq Scan sa wdata w (cost=0.00..574151.49 row=26886249 width=46) (aktwal na oras=0.005..8153.565 row=26873950 loops=1)
Ginagawa namin ang karaniwang bagay: "halika, gumawa tayo ng index at lilipad ang lahat."
Gumawa ng index sa field na “SPENT_ID”.
Ang resulta:
Query execution plan gamit ang index

Well, nakatulong ba ito?
Ito ay: 8 222 351.640 ms (mahigit sa 2 oras)
Ito ay naging: 6 985 431.575 ms (halos 2 oras)
Sa pangkalahatan, ang parehong mga mansanas, side view.
Tandaan natin ang mga klasiko:
"Mayroon ka bang pareho, ngunit walang pakpak? Hahanapin".

Sa prinsipyo, ito ay matatawag na isang magandang resulta, mabuti, hindi maganda, ngunit katanggap-tanggap. Hindi bababa sa, magbigay ng malaking ulat sa customer na naglalarawan kung gaano karami ang nagawa at kung bakit mabuti ang ginawa.
Ngunit gayon pa man, ang huling desisyon ay malayo pa rin. Napakalayo.

At ngayon ang pinaka-kagiliw-giliw na bagay - patuloy kaming nag-o-optimize, papakinin namin ang kahilingan

Unang Hakbang - Gamitin ang JOIN

Ang muling isinulat na kahilingan ay ganito na ngayon (well at least mas maganda):
Query gamit ang JOINPiliin
p.“PARAMETER_ID” bilang parameter_id,
pd."PD_NAME" BILANG pd_name,
pd."CUSTOMER_PARTNUMBER" BILANG customer_partnumber,
w. "LRM" BILANG LRM,
w. "LOTID" BILANG lotid,
w.“RTD_VALUE” BILANG RTD_value,
w.“LOWER_SPEC_LIMIT” BILANG lower_spec_limit,
w.“UPPER_SPEC_LIMIT” BILANG upper_spec_limit,
p."TYPE_CALCUL" BILANG type_calcul,
s."SPENT_NAME" BILANG spent_name,
s.“SPENT_DATE” BILANG spent_date,
extract(taon mula sa "SPENT_DATE") BILANG taon,
extract(buwan mula sa "SPENT_DATE") bilang buwan,
s."REPORT_NAME" BILANG report_name,
p."STPM_NAME" BILANG stpm_name,
p.“CUSTOMERPARAM_NAME” BILANG customerparam_name
MULA sa wdata w INNER JOIN na ginugol s SA w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p SA p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd SA pd.“PD_ID” = sp.“PD_ID”
SAAN
s.“SPENT_DATE” >= '2018-07-01' AT s.“SPENT_DATE” <= '2018-09-30'AT
s.“SPENT_DATE” = (PUMILI NG MAX(s2.“SPENT_DATE”)
MULA sa wdata w2 INNER JOIN nagastos ng s2 SA w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
SA w2.“LRM” = w.“LRM” );
Oras ng pagpaplano: 2.486 ms
Oras ng pagpapatupad: 1223680.326 ms

Kaya, ang unang resulta.
Ito ay: 6 ms (halos 985 oras).
Ito ay naging: 1 223 680.326 ms (mahigit 20 minuto lang).
Magandang resulta. Sa prinsipyo, muli, maaari tayong tumigil doon. Ngunit ito ay hindi kawili-wili, hindi ka maaaring tumigil.
PARA SA

Pangalawang hakbang - alisin ang nauugnay na subquery

Binago ang text ng kahilingan:
Nang walang kaugnay na subqueryPiliin
p.“PARAMETER_ID” bilang parameter_id,
pd."PD_NAME" BILANG pd_name,
pd."CUSTOMER_PARTNUMBER" BILANG customer_partnumber,
w. "LRM" BILANG LRM,
w. "LOTID" BILANG lotid,
w.“RTD_VALUE” BILANG RTD_value,
w.“LOWER_SPEC_LIMIT” BILANG lower_spec_limit,
w.“UPPER_SPEC_LIMIT” BILANG upper_spec_limit,
p."TYPE_CALCUL" BILANG type_calcul,
s."SPENT_NAME" BILANG spent_name,
s.“SPENT_DATE” BILANG spent_date,
extract(taon mula sa "SPENT_DATE") BILANG taon,
extract(buwan mula sa "SPENT_DATE") bilang buwan,
s."REPORT_NAME" BILANG report_name,
p."STPM_NAME" BILANG stpm_name,
p.“CUSTOMERPARAM_NAME” BILANG customerparam_name
MULA sa wdata w INNER JOIN na ginugol s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p SA p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd SA pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (PILIIN w2.“LRM”, MAX(s2.“SPENT_DATE”)
MULA sa nagastos s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP NI w2.“LRM”
) md sa w.“LRM” = md.“LRM”
SAAN
s."SPENT_DATE" >= '2018-07-01' AT s."SPENT_DATE" <= '2018-09-30';
Oras ng pagpaplano: 2.291 ms
Oras ng pagpapatupad: 165021.870 ms

Ito ay: 1 223 680.326 ms (mahigit 20 minuto lang).
Ito ay naging: 165 021.870 ms (mahigit 2 minuto lang).
Ito ay medyo mabuti na.
Gayunpaman, tulad ng sinasabi ng British "Pero, laging may pero" Ang isang resulta na masyadong maganda ay dapat awtomatikong pumukaw ng hinala. May mali dito.

Ang hypothesis tungkol sa pagwawasto sa query upang maalis ang nauugnay na subquery ay tama. Ngunit kailangan mong i-tweak ito ng kaunti para maging tama ang huling resulta.
Bilang resulta, ang unang intermediate na resulta:
Na-edit na query nang walang kaugnay na subqueryPiliin
p.“PARAMETER_ID” bilang parameter_id,
pd."PD_NAME" BILANG pd_name,
pd."CUSTOMER_PARTNUMBER" BILANG customer_partnumber,
w. "LRM" BILANG LRM,
w. "LOTID" BILANG lotid,
w.“RTD_VALUE” BILANG RTD_value,
w.“LOWER_SPEC_LIMIT” BILANG lower_spec_limit,
w.“UPPER_SPEC_LIMIT” BILANG upper_spec_limit,
p."TYPE_CALCUL" BILANG type_calcul,
s."SPENT_NAME" BILANG spent_name,
s.“SPENT_DATE” BILANG spent_date,
extract(taon mula sa s.“SPENT_DATE”) BILANG taon,
extract(buwan mula s.“SPENT_DATE”) bilang buwan,
s."REPORT_NAME" BILANG report_name,
p."STPM_NAME" BILANG stpm_name,
p.“CUSTOMERPARAM_NAME” BILANG customerparam_name
MULA sa wdata w INNER JOIN na ginugol s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p SA p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd SA pd.“PD_ID” = sp.“PD_ID”
INNER JOIN ( PILIIN w2.“LRM”, MAX(s2.“SPENT_DATE”) BILANG “SPENT_DATE”
MULA sa nagastos s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP NI w2.“LRM”
) md SA md.“SPENT_DATE” = s.“SPENT_DATE” AT md.“LRM” = w.“LRM”
SAAN
s."SPENT_DATE" >= '2018-07-01' AT s."SPENT_DATE" <= '2018-09-30';
Oras ng pagpaplano: 3.192 ms
Oras ng pagpapatupad: 208014.134 ms

Kaya, kung ano ang natapos namin ay ang unang katanggap-tanggap na resulta, na hindi nakakahiyang ipakita sa customer:
Nagsimula sa: 8 222 351.640 ms (higit sa 2 oras)
Nagawa naming makamit: 1 ms (mahigit 223 minuto).
Resulta (pansamantala): 208 014.134 ms (mahigit 3 minuto lang).

Napakahusay na resulta.

Kabuuan

Tumigil sana kami doon.
PERO…
Ang gana ay kasama ng pagkain. Ang maglalakad ay makakabisado sa daan. Ang anumang resulta ay intermediate. Huminto at namatay. atbp.
Ipagpatuloy natin ang pag-optimize.
Mahusay na ideya. Lalo na kung isasaalang-alang na ang customer ay hindi nag-iisip. At kahit na malakas para dito.

Kaya, oras na para sa muling pagdidisenyo ng database. Ang istraktura ng query mismo ay hindi na ma-optimize (bagaman, tulad ng nangyari sa ibang pagkakataon, mayroong isang pagpipilian upang matiyak na ang lahat ay talagang nabigo). Ngunit upang simulan ang pag-optimize at pagbuo ng disenyo ng database ay isang napaka-promising na ideya. At higit sa lahat ay kawili-wili. Muli, alalahanin ang iyong kabataan. Hindi ako agad naging DBA, lumaki ako bilang programmer (BASIC, assembler, C, double-plus C, Oracle, plsql). Isang kawili-wiling paksa, siyempre, para sa isang hiwalay na memoir ;-).
Gayunpaman, huwag tayong magambala.

Kaya,

O baka ang paghati ay makakatulong sa atin?
Spoiler - "Oo, nakatulong ito, kasama ang pag-optimize ng pagganap."

Ngunit iyon ay isang ganap na naiibang kuwento ...

Itutuloy…

Pinagmulan: www.habr.com