Uyakhumbula ukuthi konke kwaqala kanjani. Konke kwakungokokuqala futhi futhi

Mayelana nokuthi bekufanele siwusebenzise kanjani umbuzo we-PostgreSQL nokuthi yini eyaphuma kukho konke.
Kungani kufanele? Yebo, ngoba eminyakeni engu-4 eyedlule yonke into yasebenza ngokuthula, ngokuzola, njengokumaka kwewashi.
Njenge-epigraph.

Ngokusekelwe ezenzakalweni zangempela.
Wonke amagama ashintshiwe, ukuqondana kwenzeka ngokungahleliwe.

Uma ufinyelela umphumela othile, kuhlale kuthakazelisa ukukhumbula ukuthi wawuyini umfutho ekuqaleni, lapho kwaqala khona.

Ngakho-ke, okwenzeka ngenxa yalokho kuchazwe kafushane esihlokweni esithi “I-synthesis njengenye yezindlela zokuthuthukisa ukusebenza kwe-PostgreSQL".

Cishe kuzothakazelisa ukudala kabusha uchungechunge lwemicimbi yangaphambilini.
Umlando ulondoloze usuku oluqondile lokuqala - 2018-09-10 18:02:48.
Futhi, endabeni kunesicelo lapho konke kwaqala khona:
Isicelo senkingaKHETHA
p.“PARAMETER_ID” njengepharamitha_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" NJENGOBA inombolo_yengxenye yekhasimende,
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" NJENGOBA igama_elichithiwe,
s.“SPENT_DATE” NJENGOBA idethi yokuchitha,
khipha(unyaka kusuka ku-"SPENT_DATE") NJENGOnyaka,
khipha(inyanga kusukela ngomhla ka-"SPENT_DATE") njengenyanga,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” NJENGOBA i-customerparam_name
KUSUKA ku-wdata w,
chitha s,
pmtr p,
spend_pd sp,
pd pd
LAPHO s.“SPENT_ID” = w.“SPENT_ID”
KANYE p."PARAMETER_ID" = w."PARAMETER_ID"
KANYE s.“SPENT_ID” = sp.“SPENT_ID”
KANYE ne-pd."PD_ID" = sp."PD_ID"
KANYE s.“SPENT_DATE” >= '2018-07-01' KANYE s.“SPENT_DATE” <= '2018-09-30'
kanye no-s.“SPENT_DATE” = (KHETHA MAX(s2.“SPENT_DATE”)
KUSUKA ku-s2 esetshenzisiwe,
idatha w2
LAPHO s2.“SPENT_ID” = w2.“SPENT_ID”
KANYE ne-w2.“LRM” = w.“LRM”);

Incazelo yenkinga isezingeni elibikezelwayo - “Konke kubi. Ngitshele ukuthi yini inkinga.”
Ngokushesha ngakhumbula i-anecdote evela ezikhathini zokushayela okuyi-intshi engu-3 nengxenye:

Isilima sifika ku-hacker.
-Akukho okungisebenzelayo, ngitshele ukuthi inkinga ikuphi.
- Ku-DNA ...

Kodwa-ke, lena akuyona indlela yokuxazulula izigameko zokusebenza. “Bangase bangasiqondi"(Nge). Kudingeka sikuqonde.
Awu, ake simbe. Mhlawumbe okuthile kuzonqwabelana ngenxa yalokho.

Uphenyo luqalile

Ngakho-ke, yini engabonwa ngokushesha ngeso lenyama, ngaphandle kokuphendukela ku-CHAZA.
1) UKUJOYINA akusetshenziswa. Lokhu kubi, ikakhulukazi uma inombolo yokuxhumana ingaphezu kokukodwa.
2) Kodwa okubi nakakhulu ukuhambisana okuhlobene, ngaphezu kwalokho, ngokuhlanganisa. Kubi kakhulu lokhu.
Lokhu kubi impela. Kodwa lokhu kuphela ohlangothini olulodwa. Ngakolunye uhlangothi, lokhu kuhle kakhulu, ngoba inkinga ngokucacile inesixazululo kanye nesicelo esingathuthukiswa.
Ungayi kumbhuli (C).
Uhlelo lombuzo aluyona inkimbinkimbi kangako, kodwa luyinkomba:
Uhlelo Lokwenza

Okuthakazelisa kakhulu futhi okuwusizo, njengokujwayelekile, kusekuqaleni nasekupheleni.
I-Nested Loop (izindleko=935.84..479763226.18 imigqa=3322 ububanzi=135) (isikhathi sangempela=31.536..8220420.295 imigqa=8111656 amaluphu=1)
Isikhathi sokuhlela: 3.807 ms
Isikhathi sokwenza: 8222351.640 ms
Isikhathi sokuqeda singaphezu kwamahora ama-2.

Imibono engamanga eyathatha isikhathi

I-hypothesis 1 - I-optimizer yenza iphutha futhi yakha uhlelo olungalungile.

Ukubona ngeso lengqondo uhlelo lokubulawa, sizosebenzisa isiza https://explain.depesz.com/. Nokho, isayithi alizange libonise lutho olujabulisayo noma oluwusizo. Uma uthi nhlá nje, akukho lutho olungasiza ngempela. Kungenzeka yini ukuthi i-Full Scan incane. Qhubeka.

I-Hypothesis 2-Impact esisekelweni esivela ohlangothini lwe-autovacuum, udinga ukususa amabhuleki.

Kepha ama-autovacuum daemons aziphatha kahle, azikho izinqubo ezilenga isikhathi eside. Awukho umthwalo onzima. Kudingeka sibheke okunye.

I-hypothesis 3 - Izibalo ziphelelwe yisikhathi, yonke into idinga ukubalwa kabusha

Futhi, hhayi lokho. Izibalo zisesikhathini samanje. Okungukuthi, kunikezwe ukuntuleka kwezinkinga nge-autovacuum, akumangazi.

Ake siqale ukuthuthukisa

Ithebula elikhulu elithi 'wdata' alilona elincane, cishe amarekhodi ayizigidi ezi-3.
Futhi yileli thebula elilandelwa yi-Full Scan.

I-Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") KANYE ((I-SubPlan 1) = s."SPENT_DATE"))
-> Seq Scan ku-wdata w (izindleko=0.00..574151.49 imigqa=26886249 ububanzi=46) (isikhathi sangempela=0.005..8153.565 imigqa=26873950 amaluphu=1)
Senza into evamile: "woza, ake senze inkomba futhi yonke into izondiza."
Udale inkomba kunkambu ethi “SPENT_ID”
Ngenxa yalokho:
Uhlelo lokusebenza usebenzisa inkomba

Kwasiza yini?
Bekuyi: 8 222 351.640ms (ngaphezudlwana kwamahora ama-2)
Kwaba: 6 985 431.575 ms (cishe amahora angu-2)
Ngokuvamile, ama-apula afanayo, umbono ohlangothini.
Masikhumbule ezakudala:
“Ingabe unayo efanayo, kodwa engenamaphiko? Uzofuna".

Empeleni, lokhu kungabizwa ngokuthi umphumela omuhle, awuhle, kodwa owamukelekayo. Okungenani, hlinzeka ngombiko omkhulu ekhasimendeni ochaza ukuthi kungakanani osekwenziwe nokuthi kungani okwenziwe kwaba kuhle.
Kodwa noma kunjalo, isinqumo sokugcina sisekude. Kude kakhulu.

Futhi manje into ethakazelisa kakhulu - siyaqhubeka nokwandisa, sizopholisha isicelo

Isinyathelo sokuqala - Sebenzisa JOIN

Isicelo esibhalwe kabusha manje sesibukeka kanje (kahle okungenani enhle):
Umbuzo usebenzisa JOINKHETHA
p.“PARAMETER_ID” njengepharamitha_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" NJENGOBA inombolo_yengxenye yekhasimende,
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" NJENGOBA igama_elichithiwe,
s.“SPENT_DATE” NJENGOBA idethi yokuchitha,
khipha(unyaka kusuka ku-"SPENT_DATE") NJENGOnyaka,
khipha(inyanga kusukela ngomhla ka-"SPENT_DATE") njengenyanga,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” NJENGOBA i-customerparam_name
KUSUKA kudatha w INNER JOIN echithwe ku-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 KU-pd.“PD_ID” = sp.“PD_ID”
LAPHO
s.“SPENT_DATE” >= '2018-07-01' KANYE s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (KHETHA MAX(s2.“SPENT_DATE”)
KUSUKA ku-wdata w2 INNER JOIN ichithe u-s2 KU-w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
KU-w2.“LRM” = w.“LRM” );
Isikhathi sokuhlela: 2.486 ms
Isikhathi sokwenza: 1223680.326 ms

Ngakho, umphumela wokuqala.
Bekuyi: 6 ms (cishe amahora angu-985).
Kwaba: 1 223 680.326 ms (ngaphezulu nje kwemizuzu engama-20).
Umphumela omuhle. Empeleni, futhi, singama lapho. Kodwa akuthakazelisi kakhulu, awukwazi ukuyeka.
FOR

Isinyathelo sesibili - susa umbuzo omncane ohlobene

Umbhalo wesicelo oshintshiwe:
Ngaphandle kombuzo omncane ohlobeneKHETHA
p.“PARAMETER_ID” njengepharamitha_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" NJENGOBA inombolo_yengxenye yekhasimende,
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" NJENGOBA igama_elichithiwe,
s.“SPENT_DATE” NJENGOBA idethi yokuchitha,
khipha(unyaka kusuka ku-"SPENT_DATE") NJENGOnyaka,
khipha(inyanga kusukela ngomhla ka-"SPENT_DATE") njengenyanga,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” NJENGOBA i-customerparam_name
KUSUKA kudatha w INNER JOIN echithwe ON 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 KU-pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (KHETHA w2.“LRM”, MAX(s2.“SPENT_DATE”)
KUSUKA ku-s2 INNER echithiwe JOYINA i-wdata w2 KU-s2.“SPENT_ID” = w2.“SPENT_ID”
IQEMBU NGE-w2.“LRM”
) md ku-w.“LRM” = md.“LRM”
LAPHO
s."SPENT_DATE" >= '2018-07-01' KANYE s."SPENT_DATE" <= '2018-09-30';
Isikhathi sokuhlela: 2.291 ms
Isikhathi sokwenza: 165021.870 ms

Bekuyi: 1 223 680.326 ms (ngaphezulu nje kwemizuzu engama-20).
Kwaba: 165 021.870 ms (ngaphezulu nje kwemizuzu emi-2).
Lokhu sekuvele kuhle impela.
Nokho, njengoba amaBrithani esho "Kodwa, kukhona njalo kodwa" Umphumela omuhle kakhulu kufanele uvuse izinsolo ngokuzenzekelayo. Kukhona okungahambi kahle lapha.

I-hypothesis emayelana nokulungisa umbuzo ukuze kukhishwe umbuzo omncane ohlobene ulungile. Kodwa udinga ukuyilungisa kancane ukuze umphumela wokugcina ube olungile.
Ngenxa yalokho, umphumela wokuqala omaphakathi:
Umbuzo ohleliwe ngaphandle kombuzo omncane ohlobeneKHETHA
p.“PARAMETER_ID” njengepharamitha_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" NJENGOBA inombolo_yengxenye yekhasimende,
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" NJENGOBA igama_elichithiwe,
s.“SPENT_DATE” NJENGOBA idethi yokuchitha,
khipha(unyaka kusukela ku-s.“SPENT_DATE”) NJENGONYAKA,
khipha(inyanga kusukela ku-s.“SPENT_DATE”) njengenyanga,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” NJENGOBA i-customerparam_name
KUSUKA kudatha w INNER JOIN echithwe ON 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 KU-pd.“PD_ID” = sp.“PD_ID”
INNER JOIN ( KHETHA w2.“LRM”, MAX(s2.“SPENT_DATE”) NJENGO “SPENT_DATE”
KUSUKA ku-s2 INNER echithiwe JOYINA i-wdata w2 KU-s2.“SPENT_ID” = w2.“SPENT_ID”
IQEMBU NGE-w2.“LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” KANYE md.“LRM” = w.“LRM”
LAPHO
s."SPENT_DATE" >= '2018-07-01' KANYE s."SPENT_DATE" <= '2018-09-30';
Isikhathi sokuhlela: 3.192 ms
Isikhathi sokwenza: 208014.134 ms

Ngakho-ke, esigcina sinakho umphumela wokuqala owamukelekayo, okungelona ihlazo ukukhombisa ikhasimende:
Iqale ngokuthi: 8 222 351.640 ms (ngaphezu kwamahora angu-2)
Sikwazile ukuzuza: 1 ms (imizuzu engaphezudlwana kwengama-223).
Umphumela (okwesikhashana): 208 014.134 ms (ngaphezulu nje kwemizuzu emi-3).

Umphumela omuhle kakhulu.

Umphumela

Besingama lapho.
KODWA...
Isifiso sokudla siza nokudla. Ohambayo uyoba yinkosi yomgwaqo. Noma yimuphi umphumela uphakathi. Wama wafa. njll.
Masiqhubeke nokwenza kahle.
Umqondo omuhle. Ikakhulukazi uma kucatshangelwa ukuthi ikhasimende alinandaba. Futhi ngokunamandla ngakho.

Ngakho-ke, yisikhathi sokuklama kabusha isizindalwazi. Isakhiwo sombuzo ngokwaso asisakwazi ukuthuthukiswa (yize, njengoba kwavela kamuva, kukhona inketho yokuqinisekisa ukuthi yonke into iyehluleka ngempela). Kepha ukuqala ukwenza ngcono nokuthuthukisa ukwakheka kwedathabheyisi sekuvele kuwumbono othembisayo kakhulu. Futhi okubaluleke kakhulu okuthakazelisayo. Futhi, khumbula ubusha bakho. Angizange ngibe yi-DBA ngokushesha, ngakhula njengomhleli (BASIC, assembler, C, double-plus C, Oracle, plsql). Isihloko esithakazelisayo, yebo, sememori ehlukile ;-).
Nokho, masingaphazanyiswa.

Ngakho-ke,

Noma mhlawumbe ukwahlukanisa kuzosisiza?
I-Spoiler - "Yebo, isizile, okuhlanganisa nokuthuthukisa ukusebenza kahle."

Kodwa leyo indaba ehluke ngokuphelele...

Kuzoqhutshwa…

Source: www.habr.com