Uyakhumbula ukuba yaqala njani yonke loo nto. Yonke into yayiqala kwaye kwakhona

Malunga nendlela ekufuneka siwusebenzise ngayo umbuzo wePostgreSQL kunye nokuba yintoni ephume kuyo yonke.
Kwakutheni ukuze ube? Ewe, kuba kwiminyaka emi-4 edlulileyo yonke into yayisebenza ngokuzolileyo, ngokuzolileyo, njengewotshi ephawulayo.
Njenge-epigraph.

Ngokusekelwe kwiziganeko zangempela.
Onke amagama atshintshiwe, izinto ezenzekayo ziyenzeka ngokungacwangciswanga.

Xa ufezekisa umphumo othile, kuhlala kunomdla ukukhumbula ukuba yintoni impembelelo yokuqala, apho yaqala khona.

Ngoko ke, okwenzekayo ngenxa yoko kuchazwa ngokufutshane kwinqaku elithi “I-Synthesis njengenye yeendlela zokuphucula ukusebenza kwe-PostgreSQL».

Kuya kuba nomdla ukuphinda wenze ikhonkco leziganeko zangaphambili.
Imbali igcine owona mhla wokuqala - 2018-09-10 18:02:48.
Kwakhona, kwibali kukho isicelo apho yonke into yaqala khona:
Isicelo sengxakiKHETHA
p.“PARAMETER_ID” njenge parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS inombolo yomthengi,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS low_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS lichithiwe_igama,
s.“SPENT_DATE” NJENGOKO_Umhla ochithiweyo,
isicatshulwa(unyaka ukusuka "SPENT_DATE") AS unyaka,
isicatshulwa(inyanga ukusuka "SPENT_DATE") njengenyanga,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
UKUSUKA kwi-wdata,
ichithe s,
pmtr p,
ichithe_pd sp,
pd pd
PHI s.“SPENT_ID” = w.“SPENT_ID”
KUNYE p."PARAMETER_ID" = w."PARAMETER_ID"
KUNYE s.“SPENT_ID” = sp.“SPENT_ID”
KUNYE nepd."PD_ID" = sp."PD_ID"
KUNYE s.“SPENT_DATE” >= '2018-07-01' KUNYE s.“SPENT_DATE” <= '2018-09-30'
kunye no-“SPENT_DATE” = (KHETHA MAX(s2.“SPENT_DATE”)
UKUSUKA kwinkcitho s2,
idatha w2
PHI s2.“SPENT_ID” = w2.“SPENT_ID”
KUNYE W2.“LRM” = w.“LRM”);

Inkcazo yengxaki isemgangathweni oqikelelwayo - "Yonke into imbi. Ndixelele ukuba yintoni ingxaki.”
Kwangoko ndakhumbula i-anecdote ukusuka kumaxesha e-3 kunye nesiqingatha intshi yokuqhuba:

Isiqhwala siza kwi-hacker.
-Akukho nto isebenzayo kum, ndixelele apho ingxaki.
-kwiDNA...

Kodwa ke, oku akusiyo indlela yokusombulula iziganeko zokusebenza. “Basenokungasiqondi"(kunye). Kufuneka siyiqonde.
Kulungile, masigrumbe. Mhlawumbi into iya kuqokelelana ngenxa yoko.

Uphando luqalile

Ke, yintoni enokubonwa kwangoko ngeliso lenyama, ngaphandle kokubhenela kwi-CACISA.
1) UKUJOYINA akusetyenziswanga. Oku kubi, ngakumbi ukuba inani loqhagamshelo lingaphezulu kwesinye.
2) Kodwa eyona nto imbi ngakumbi kukuhambelana okuhambelanayo, ngaphezu koko, kunye nokuhlanganiswa. Oku kubi kakhulu.
Oku kubi kunjalo. Kodwa oku kuphela kwicala elinye. Ngakolunye uhlangothi, oku kuhle kakhulu, kuba ingxaki ngokucacileyo inesisombululo kunye nesicelo esinokuphuculwa.
Musa ukuya kumntu oxela ithamsanqa (C).
Isicwangciso sombuzo asintsokothanga kangako, kodwa siyabonisa:
Isicwangciso sokuphumeza

Eyona nto inomdla kwaye iluncedo, njengesiqhelo, isekuqaleni nasekupheleni.
I-Nested Loop (ixabiso=935.84..479763226.18 imiqolo=3322 ububanzi=135) (elona xesha=31.536..8220420.295 imiqolo=8111656 iilophu=1)
Ixesha lokucwangcisa: 3.807 ms
Ixesha lokwenziwa: 8222351.640 ms
Ixesha lokugqiba lingaphezulu kweeyure ezi-2.

Ubuxoki obuthatha ixesha

I-hypothesis 1 - Isilungisi senza impazamo kwaye sakha isicwangciso esingalunganga.

Ukubona isicwangciso sokwenza, siya kusebenzisa isayithi https://explain.depesz.com/. Nangona kunjalo, isayithi ayizange ibonise nantoni na enomdla okanye eluncedo. Xa uthi krwaqu nje okokuqala, akukho nto inokunceda ngokwenene. Ngaba kunokwenzeka ukuba iSkena esiPheleleyo sincinci. Qhubeka.

I-Hypothesis 2-Impembelelo kwisiseko esivela kwicala le-autovacuum, kufuneka ulahle iibhuleki.

Kodwa ii-autovacuum daemons ziziphatha kakuhle, akukho nkqubo zixhonywe ixesha elide. Akukho mthwalo unzima. Kufuneka sikhangele enye into.

I-Hypothesis 3 - Izibalo ziphelelwe lixesha, yonke into ifuna ukuphinda ibalwe kwakhona

Kwakhona, akunjalo. Izibalo zihlaziyiwe. Yiyiphi, enikwe ukungabikho kweengxaki nge-autovacuum, ayimangalisi.

Masiqale ukulungelelanisa

Itheyibhile ephambili 'wdata' ngokuqinisekileyo ayincinci, phantse iirekhodi ezizigidi ezi-3.
Kwaye yile tafile elandelwa yiFull Scan.

Umqathango weHash: ((w."SPENT_ID" = s."SPENT_ID") KUNYE ((Isicwangciso esiphantsi 1) = s."SPENT_DATE")
-> Seq Scan kwi-wdata w (ixabiso=0.00..574151.49 imiqolo=26886249 ububanzi=46) (elona xesha=0.005..8153.565 imiqolo=26873950 iilophu=1)
Senza into eqhelekileyo: "yiza, masenze isalathiso kwaye yonke into iya kubhabha."
Yenza isalathisi kwindawo ethi "SPENT_ID".
Nje ngeziphumo:
Isicwangciso sokwenziwa kombuzo usebenzisa isalathiso

Ewe, ngaba yanceda?
Yayiyi: 8 222 351.640ms (Ngapha kweeyure ezi-2)
Yaba: 6 985 431.575 ms (phantse iiyure ezi-2)
Ngokubanzi, ii-apula ezifanayo, umbono wecala.
Masikhumbule ezakudala:
Ngaba nawe unayo enye kodwa engenamaphiko? Uya kufuna".

Ngokomgaqo, oku kunokubizwa ngokuba sisiphumo esihle, kakuhle, kungekhona esilungileyo, kodwa esamkelekileyo. Ubuncinci, nikezela ngengxelo enkulu kumthengi echaza ukuba kungakanani okwenziweyo kwaye kutheni le nto yenziweyo ilungile.
Kodwa kunjalo, isigqibo sokugqibela sisekude. Kude kakhulu.

Kwaye ngoku eyona nto inomdla kakhulu - siyaqhubeka nokwandisa, siya kupholisha isicelo

Inyathelo lokuQala - Sebenzisa u-JOIN

Isicelo esibhalwe ngokutsha ngoku sijongeka ngolu hlobo (kakuhle noko intle ngakumbi):
Umbuzo usebenzisa JOINKHETHA
p.“PARAMETER_ID” njenge parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS inombolo yomthengi,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS low_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS lichithiwe_igama,
s.“SPENT_DATE” NJENGOKO_Umhla ochithiweyo,
isicatshulwa(unyaka ukusuka "SPENT_DATE") AS unyaka,
isicatshulwa(inyanga ukusuka "SPENT_DATE") njengenyanga,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
UKUSUKA kwidatha w INNER JOIN echithwe nge-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”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
KUPHI
s.“SPENT_DATE” >= '2018-07-01' KUNYE s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (KHETHA MAX(s2.“SPENT_DATE”)
UKUSUKA kwi-wdata w2 INNER JOIN ichithwe s2 KU-w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
KWI-w2.“LRM” = w.“LRM” );
Ixesha lokucwangcisa: 2.486 ms
Ixesha lokwenziwa: 1223680.326 ms

Ngoko, umphumo wokuqala.
Yayiyi: 6 ms (phantse iiyure ezi-985).
Yaba: 1 223 680.326 ms (ngaphezu kwemizuzu engama-20).
Isiphumo esihle. Ngokomgaqo, kwakhona, sinokuma apho. Kodwa ayinomdla kakhulu, awukwazi ukuyeka.
FOR

Inyathelo lesibini - susa i-subquery ehambelanayo

Isicelo esitshintshiweyo:
Ngaphandle kombuzo ohambelanayoKHETHA
p.“PARAMETER_ID” njenge parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS inombolo yomthengi,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS low_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS lichithiwe_igama,
s.“SPENT_DATE” NJENGOKO_Umhla ochithiweyo,
isicatshulwa(unyaka ukusuka "SPENT_DATE") AS unyaka,
isicatshulwa(inyanga ukusuka "SPENT_DATE") njengenyanga,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
UKUSUKA kwidatha w INNER JOIN echithwe 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”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
JOIN NGAPHAKATHI (KHETHA w2.“LRM”, MAX(s2.“SPENT_DATE”)
UKUSUKA kwinkcitho s2 INNER JOIN wdata w2 KU-s2.“SPENT_ID” = w2.“SPENT_ID”
IQELA NGE-w2.“LRM”
) md ku-w.“LRM” = md.“LRM”
KUPHI
s."SPENT_DATE" >= '2018-07-01' KUNYE s."SPENT_DATE" <= '2018-09-30';
Ixesha lokucwangcisa: 2.291 ms
Ixesha lokwenziwa: 165021.870 ms

Yayiyi: 1 223 680.326 ms (ngaphezu kwemizuzu engama-20).
Yaba: 165 021.870 ms (ngaphezu kwemizuzu emi-2).
Oku sele kulungile.
Nangona kunjalo, njengoko amaBritane athi "Kodwa, kuhlala kukho kodwa" Isiphumo esihle kakhulu kufuneka sivuse ukukrokra ngokuzenzekelayo. Kukho into engalunganga apha.

Ingqikelelo malunga nokulungisa umbuzo ukususa i-subquery ehambelanayo ichanekile. Kodwa kufuneka uyilungise kancinci ukuze isiphumo sokugqibela sichaneke.
Ngenxa yoko, isiphumo sokuqala esiphakathi:
Umbuzo ohleliweyo ngaphandle kombuzo onganeno onxibeleleneyoKHETHA
p.“PARAMETER_ID” njenge parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS inombolo yomthengi,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS low_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS lichithiwe_igama,
s.“SPENT_DATE” NJENGOKO_Umhla ochithiweyo,
isicatshulwa(unyaka ukusuka kwi-s.“SPENT_DATE”) AS unyaka,
isicatshulwa(inyanga ukusuka kwi-s.“SPENT_DATE”) njengenyanga,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
UKUSUKA kwidatha w INNER JOIN echithwe 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”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN ( KHETHA w2.“LRM”, MAX(s2.“SPENT_DATE”) AS “SPENT_DATE”
UKUSUKA kwinkcitho s2 INNER JOIN wdata w2 KU-s2.“SPENT_ID” = w2.“SPENT_ID”
IQELA NGE-w2.“LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” KUNYE md.“LRM” = w.“LRM”
KUPHI
s."SPENT_DATE" >= '2018-07-01' KUNYE s."SPENT_DATE" <= '2018-09-30';
Ixesha lokucwangcisa: 3.192 ms
Ixesha lokwenziwa: 208014.134 ms

Ke, into esigqiba ngayo sisiphumo sokuqala esamkelekileyo, esingelohlazo ukubonisa umthengi:
Iqalwe nge: 8 222 351.640 ms (ngaphezu kweeyure ezi-2)
Sikwazile ukufikelela: 1 ms (ngaphezu kwemizuzu engama-223).
Isiphumo (sethutyana): 208 014.134 ms (ngaphezu nje kwemizuzu emi-3).

Isiphumo esihle kakhulu.

Isiphumo

Ngesime apho.
KODWA...
Ukuthanda ukutya kuza nokutya. Ohambahambayo uya kuyilawula indlela. Nasiphi na isiphumo siphakathi. Wema waza wafa. njl.
Masiqhubeke nokuphucula.
Ingcinga entle. Ngokukodwa ukuqwalasela ukuba umthengi akazange acinge. Kwaye ngamandla kuyo.

Ke, lixesha lokuyilwa ngokutsha kwedathabheyisi. Ulwakhiwo lombuzo ngokwalo alunakuphinda luphuculwe (nangona, njengoko kwavela kamva, kukho ukhetho lokuqinisekisa ukuba yonke into iyasilela). Kodwa ukuqalisa ukukhulisa nokuphuhlisa uyilo lwesiseko sedatha sele ingumbono othembisayo. Kwaye okubaluleke kakhulu umdla. Kwakhona, khumbula ubutsha bakho. Andizange ndibe yi-DBA ngokukhawuleza, ndakhula njengomdwelisi (BASIC, assembler, C, double-plus C, Oracle, plsql). Isihloko esinomdla, ngokuqinisekileyo, sememori eyahlukileyo ;-).
Noko ke, masingaphazanyiswa.

Kwaye ke,

Okanye ukwahlukana kuya kusinceda?
I-Spoiler - "Ewe, incede, kubandakanya nokwenza ngcono ukusebenza."

Kodwa elo libali elahluke ngokupheleleyo...

Iza kuqhubeka…

umthombo: www.habr.com