Game da yadda zan yi mu'amala da ingantawa ta PostgreSQL da abin da ya fito daga duk wannan.
Me yasa dole? Haka ne, saboda shekaru 4 da suka gabata komai ya yi aiki a hankali, cikin nutsuwa, kamar agogo ya yi.
a matsayin epigraph.
Bisa ga hakikanin abubuwan da suka faru.
An canza duk sunaye, daidaituwa ba zato ba tsammani.
Lokacin da aka sami wani sakamako, koyaushe yana da ban sha'awa don tunawa da abin da ya motsa farkon, yadda duk ya fara.
Don haka, an kwatanta abin da ya faru a sakamakon a taƙaice a cikin labarin “".
Wataƙila zai zama mai ban sha'awa don sake ƙirƙirar jerin abubuwan da suka faru a baya.
Tarihin ya kiyaye ainihin ranar farawa - 2018-09-10 18:02:48.
Har ila yau, a cikin labarin akwai buƙatu daga abin da ya fara:
Neman matsalaSelect
p. "PARAMETER_ID" a matsayin parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS abokin ciniki_part number,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_darajar,
w. "LOWER_SPEC_LIMIT" AS ƙananan_spec_limit,
w. "UPPER_SPEC_LIMIT" AS babba_spec_limit,
p."TYPE_CALCUL" AS irin lissafi,
s."SPENT_NAME" AS kashe_name,
s. "SPENT_DATE" AS da aka kashe,
cire (shekara daga "SPENT_DATE") AS shekara,
cire (wata daga "SPENT_DATE") kamar wata,
s"REPORT_NAME" AS rahoton_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS abokin cinikiparam_name
DAGA wdata,
ciyar s,
pmtrp,
kashe_pdsp,
pd pd
INA s"SPENT_ID" = w"SPENT_ID"
DA p."PARAMETER_ID" = w."PARAMETER_ID"
DA s"SPENT_ID" = sp."SPENT_ID"
DA pd. "PD_ID" = sp. "PD_ID"
DA s." SPENT_DATE" > = '2018-07-01' DA s." SHEKARAR" <= '2018-09-30'
da s."SPENT_DATE" = (Zabi MAX(s2."SPENT_DATE")
DAGA ciyar s2,
wata w2
INA s2."SPENT_ID" = w2."SPENT_ID"
DA w2"LRM" = w"LRM");
Bayanin matsalar, daidaitattun tsinkaya - “Komai mara kyau ne. Fada min matsalar."
Nan da nan na tuna da wata wargi daga lokacin tuƙi 3 da rabi:
Lamer ya zo wurin hacker.
- Ba abin da ke aiki a gare ni, gaya mani inda matsalar take.
-In DNA...
Amma, ba shakka, wannan ba shine hanyar magance abubuwan da suka faru ba. "Wataƙila ba a gane mu ba"(Da). Bukatar gane shi.
To, bari mu tono. Wataƙila hakan zai taru a sakamakon haka.
zuba jari ya fara
Don haka, abin da za a iya gani nan da nan da ido tsirara, ba tare da yin amfani da taimakon BAYANI ba.
1) Ba a amfani da JOINs. Wannan mummunan abu ne, musamman idan adadin haɗin kai ya fi ɗaya.
2) Amma abin da ya fi muni - haɗin haɗin gwiwa, haka kuma, tare da tarawa. Wannan yayi muni sosai.
Wannan ba shi da kyau, ba shakka. Amma wannan a gefe guda ne kawai. A gefe guda, wannan yana da kyau sosai, saboda matsalar a fili tana da mafita kuma ana iya inganta buƙatar.
Kar a je wurin mai duba (C).
Shirin tambayar ba haka yake da wahala ba, amma yana nuni da cewa:
Shirin aiwatarwa
Mafi ban sha'awa da amfani, kamar yadda aka saba, a farkon da ƙarshe.
Nested Loop (farashin = 935.84..479763226.18 layuka = 3322 nisa = 135) (ainihin lokacin = 31.536..8220420.295 layuka = 8111656 madaukai = 1)
Lokacin tsarawa: 3.807ms
Lokacin aiwatarwa: 8222351.640ms
Lokacin aiwatarwa ya fi awanni 2.
Hasashen ƙarya da suka ɗauki lokaci
Hasashe 1- Mai ingantawa ba daidai ba ne, yana gina tsari mara kyau.
Don ganin tsarin aiwatarwa, za mu yi amfani da shafin . Duk da haka, shafin bai nuna wani abu mai ban sha'awa ko mai amfani ba. A kallo na farko da na biyu - babu abin da zai iya taimakawa sosai. Sai dai idan - Cikakken Scan yana da ƙanƙanta. Ci gaba.
Hasashe 2-Tasiri akan tushe daga gefen autovacuum, kuna buƙatar kawar da birki.
Amma, autovacuum daemons suna da kyau, babu matakai masu tsayi. Duk wani nauyi mai tsanani - a'a. Bukatar neman wani abu dabam.
Hasashe 3-Kididdiga ya ƙare, kuna buƙatar sake ƙididdige duk abin da ya tashi
Bugu da ƙari, ba haka ba. Ƙididdiga na zamani. Wanne, idan aka ba da rashin matsaloli tare da autovacuum, ba abin mamaki bane.
Fara ingantawa
Babban tebur na 'wdata' tabbas ba karami bane, kusan rikodin miliyan 3.
Kuma akan wannan tebur ne Cikakken Scan ke tafiya.
Hash Cond: ((w"SPENT_ID" = s."SPENT_ID") DA ((SubPlan 1) = s."SPENT_DATE").
-> Seq Scan akan wdata w (farashin = 0.00..574151.49 layuka = 26886249 nisa = 46) (ainihin lokacin = 0.005..8153.565 layuka = 26873950 madaukai = 1)
Muna aiki a matsayin ma'auni: "bari mu yi index kuma komai ya tashi".
An yi fihirisa akan filin "SPENT_ID"
Saboda:
Shirin aiwatar da tambaya ta amfani da fihirisa
To, ya taimaka?
Ya kasance: 8 222ms (fiye da awanni 2 kawai)
Ya zama: 6 985 431.575 ms (kusan awanni 2)
Gabaɗaya, apples iri ɗaya, hangen nesa.
Bari mu tuna da classic:
"Kuna da daya, amma ba fuka-fuki ba? Zan nema".
A ka'ida, ana iya kiran wannan sakamako mai kyau, da kyau, ba mai kyau ba, amma yarda. Aƙalla, bayar da babban rahoto ga abokin ciniki yana kwatanta nawa aka yi da kuma dalilin da yasa abin da aka yi yake da kyau.
Duk da haka, yanke shawara na ƙarshe har yanzu yana da nisa. Nisa sosai.
Kuma yanzu abu mafi ban sha'awa - muna ci gaba da ingantawa, za mu goge tambayar
Mataki na daya - yi amfani da JOIN
Tambayar da aka sake rubuta, yanzu tayi kama da wannan (da kyau a kalla mafi kyau):
Tambaya ta amfani da JOINSelect
p. "PARAMETER_ID" a matsayin parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS abokin ciniki_part number,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_darajar,
w. "LOWER_SPEC_LIMIT" AS ƙananan_spec_limit,
w. "UPPER_SPEC_LIMIT" AS babba_spec_limit,
p."TYPE_CALCUL" AS irin lissafi,
s."SPENT_NAME" AS kashe_name,
s. "SPENT_DATE" AS da aka kashe,
cire (shekara daga "SPENT_DATE") AS shekara,
cire (wata daga "SPENT_DATE") kamar wata,
s"REPORT_NAME" AS rahoton_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS abokin cinikiparam_name
DAGA wdata w CIN JOIN da aka kashe s ON w"SPENT_ID"=s."SPENT_ID"
SHIGA CIKI pmtr p ON p."PARAMETER_ID" = w"PARAMETER_ID"
SHIGA CIKI An kashe_pd ON s."SPENT_ID" = sp."SPENT_ID"
SHIGA CIKI pd pd ON pd."PD_ID" = sp."PD_ID"
INA
s." SPENT_DATE" > = '2018-07-01' DA s." SPENT_DATE" <= '2018-09-30'AND
s"SPENT_DATE" = (Zabi MAX(s2."SPENT_DATE")
DAGA wdata w2 CIN JOIN da aka kashe s2 ON w2."SPENT_ID"=s2."SPENT_ID"
SHIGA CIKI wdata w
ON w2"LRM" = w"LRM");
Lokacin tsarawa: 2.486ms
Lokacin aiwatarwa: 1223680.326ms
To ga sakamakon farko.
Ya kasance: 6 985 431.575 ms (kusan awanni 2).
Ya zama: 1 223 680.326 ms (fiye da mintuna 20 kawai).
sakamako mai kyau. A ka'ida, kuma, zai yiwu a tsaya a can. Amma don haka m, ba za ka iya daina.
DON
Mataki na Biyu - Cire abubuwan da ke da alaƙa
Canza rubutun buƙatu:
Babu abin tambaya mai alaƙaSelect
p. "PARAMETER_ID" a matsayin parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS abokin ciniki_part number,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_darajar,
w. "LOWER_SPEC_LIMIT" AS ƙananan_spec_limit,
w. "UPPER_SPEC_LIMIT" AS babba_spec_limit,
p."TYPE_CALCUL" AS irin lissafi,
s."SPENT_NAME" AS kashe_name,
s. "SPENT_DATE" AS da aka kashe,
cire (shekara daga "SPENT_DATE") AS shekara,
cire (wata daga "SPENT_DATE") kamar wata,
s"REPORT_NAME" AS rahoton_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS abokin cinikiparam_name
DAGA wdata w CIN JOIN da aka kashe s A s"SPENT_ID" = w"SPENT_ID"
SHIGA CIKI pmtr p ON p."PARAMETER_ID" = w"PARAMETER_ID"
SHIGA CIKI An kashe_pd ON s."SPENT_ID" = sp."SPENT_ID"
SHIGA CIKI pd pd ON pd."PD_ID" = sp."PD_ID"
SHIGA CIKI (Zabi w2."LRM", MAX(s2."SPENT_DATE")
DAGA s2 Ciki Haɗa wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
KUNGIYAR W2.LRM
md a kan w. "LRM" = md. "LRM"
INA
s." SPENT_DATE" > = '2018-07-01' DA s." KWANA_DAYA" <= '2018-09-30';
Lokacin tsarawa: 2.291ms
Lokacin aiwatarwa: 165021.870ms
Ya kasance: 1 223 680.326 ms (fiye da mintuna 20 kawai).
Ya zama: 165 021.870 ms (fiye da mintuna 2 kawai).
Wannan ya riga ya yi kyau sosai.
Sai dai kamar yadda turawan Ingila ke cewa.Amma, akwai ko da yaushe amma". Kyakkyawan sakamako yakamata ya haifar da zato ta atomatik. Akwai damuwa a nan.
Maganar gyara tambaya don kawar da abin da ke da alaƙa daidai ne. Amma yana buƙatar tweaking kaɗan don samun sakamako na ƙarshe daidai.
Sakamakon haka, sakamako na farko na matsakaici:
Tambayoyi da aka gyara ba tare da haɗin kai baSelect
p. "PARAMETER_ID" a matsayin parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS abokin ciniki_part number,
w"LRM" AS LRM,
w. "LOTID" AS lotid,
w. "RTD_VALUE" AS RTD_darajar,
w. "LOWER_SPEC_LIMIT" AS ƙananan_spec_limit,
w. "UPPER_SPEC_LIMIT" AS babba_spec_limit,
p."TYPE_CALCUL" AS irin lissafi,
s."SPENT_NAME" AS kashe_name,
s. "SPENT_DATE" AS da aka kashe,
cire (shekara daga s. "SPENT_DATE") AS shekara,
cire (wata daga s. "SPENT_DATE") kamar wata,
s"REPORT_NAME" AS rahoton_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS abokin cinikiparam_name
DAGA wdata w CIN JOIN da aka kashe s A s"SPENT_ID" = w"SPENT_ID"
SHIGA CIKI pmtr p ON p."PARAMETER_ID" = w"PARAMETER_ID"
SHIGA CIKI An kashe_pd ON s."SPENT_ID" = sp."SPENT_ID"
SHIGA CIKI pd pd ON pd."PD_ID" = sp."PD_ID"
SHIGA CIKI ( SELECT w2."LRM", MAX(s2."SPENT_DATE) AS "SPENT_DATE"
DAGA s2 Ciki Haɗa wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
KUNGIYAR W2.LRM
) md ON md."SPENT_DATE" = s"SPENT_DATE"DA md"LRM" = w"LRM"
INA
s." SPENT_DATE" > = '2018-07-01' DA s." KWANA_DAYA" <= '2018-09-30';
Lokacin tsarawa: 3.192ms
Lokacin aiwatarwa: 208014.134ms
Don haka, abin da muke da shi a sakamakon shine sakamako na farko da aka karɓa, wanda ba mu jin kunyar nuna wa abokin ciniki:
An fara da: 8 222 351.640 ms (fiye da awanni 2)
An Samu: 1 ms (fiye da mintuna 223 kawai).
Sakamakon (matsakaici): 208 014.134 ms (fiye da mintuna 3 kawai).
Kyakkyawan sakamako.
Sakamakon
Wannan zai iya tsayawa.
AMMA…
Ci abinci yana zuwa tare da ci. Za a sarrafa hanyar ta hanyar tafiya. Duk wani sakamako matsakaici ne. Ya tsaya matacce. Da dai sauransu.
Bari mu ci gaba da ingantawa.
Babban ra'ayi. Musamman ganin cewa abokin ciniki bai ma adawa da shi ba. Kuma har ma da karfi - don.
Don haka, lokaci ya yi da za a sake fasalin tsarin bayanai. Ba za a iya inganta tsarin tambayar kanta ba (ko da yake, kamar yadda ya juya daga baya, akwai zaɓi don komai don tashi da gaske). Amma yanzu don haɓakawa da haɓaka ƙirar bayanan bayanai, wannan ya riga ya zama ra'ayi mai ban sha'awa. Kuma mafi mahimmanci mai ban sha'awa. Bugu da ƙari, tuna matasa. Bayan haka, ba nan da nan na zama DBA ba, na girma daga masu shirya shirye-shirye (basic, assembler, si, si double plused, oracle, plsql). Batu mai ban sha'awa, ba shakka, don abubuwan tunawa daban-daban ;-).
Duk da haka, kada mu digress.
Sabili da haka,
Kuma watakila sashe zai taimake mu?
Spoiler - "Ee, ya taimaka, kuma a cikin inganta aikin, ciki har da."
Amma wannan labari ne kwata-kwata...
A ci gaba…
source: www.habr.com