E pili ana i kaʻu hana ʻana me PostgreSQL query optimization a me ka mea i puka mai i kēia mau mea āpau.
No ke aha ʻoe i hana ai? ʻAe, no ka mea, no nā makahiki 4 i hala aku nei ua hana nā mea a pau me ka mālie, me ka mālie, e like me ke kani ʻana o ka uaki.
Me he epigraph.
Ma muli o nā hanana maoli.
Ua hoʻololi ʻia nā inoa a pau, ʻokoʻa nā coincidences.
Ke hoʻokō ʻia kekahi hopena, he mea hoihoi mau ka hoʻomanaʻo ʻana i ke kumu o ka hoʻomaka ʻana, pehea i hoʻomaka ai.
No laila, ua wehewehe pōkole ʻia ka mea i hana ʻia ma ka ʻatikala "".
He mea hoihoi paha ka hana hou ʻana i ke kaulahao o nā hanana i hala.
Ua mālama ka mōʻaukala i ka lā hoʻomaka pololei - 2018-09-10 18:02:48.
Eia kekahi, aia ma ka moʻolelo kahi noi i hoʻomaka ai:
Noi pilikiakoho i
p. "PARAMETER_ID" ma ke ʻano he 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 lower_spec_limit,
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,
unuhi (makahiki mai "SPENT_DATE") AS makahiki,
unuhi (mahina mai "SPENT_DATE") ma ke ʻano he mahina,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
MAI wdataw,
lilo s,
pmtrp,
lilo_pdsp,
pd pd
WHERE s."SPENT_ID" = w."SPENT_ID"
A me p."PARAMETER_ID" = w."PARAMETER_ID"
A me s."SPENT_ID" = sp."SPENT_ID"
A me pd. "PD_ID" = sp. "PD_ID"
A me s."SPENT_DATE" >= '2018-07-01' A me s."SPENT_DATE" <= '2018-09-30'
a me s."SPENT_DATE" = (KOHO MAX(s2."SPENT_DATE")
Mai ka lilo s2,
wdata w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
A me w2."LRM" = w."LRM");
ʻO ka wehewehe ʻana i ka pilikia, maʻamau maʻamau - “He ʻino nā mea a pau. E haʻi mai iaʻu i ka pilikia."
Hoʻomanaʻo koke wau i kahi hoʻohenehene mai nā manawa o 3 a me ka hapa iniha kaʻa kaʻa:
Hele mai ka lamer i ka hacker.
- ʻAʻohe mea e hana iaʻu, e haʻi mai iaʻu i hea ka pilikia.
- Ma ka DNA ...
Akā, ʻoiaʻiʻo, ʻaʻole kēia ke ala e hoʻoponopono ai i nā hanana hana. “ʻAʻole paha mākou e hoʻomaopopo ʻia"(Me). Pono e noʻonoʻo.
ʻAe, e ʻeli kākou. Malia paha e hōʻuluʻulu ʻia kēlā hopena.
hoʻomaka ka hoʻokomo kālā
No laila, ʻo ka mea hiki ke ʻike koke ʻia me ka maka ʻōlohelohe, me ka ʻole o ka hoʻohana ʻana i ke kōkua o EXPLAIN.
1) ʻAʻole hoʻohana ʻia nā JOIN. He ʻino kēia, ʻoiai inā ʻoi aku ka nui o nā pilina ma mua o hoʻokahi.
2) Akā ʻo ka mea ʻoi aku ka maikaʻi - kahi subquery i hoʻopili ʻia, ʻoi aku hoʻi, me ka hoʻohui. He ino loa keia.
He ino keia. Akā aia kēia ma ka lima hoʻokahi. Ma ka ʻaoʻao ʻē aʻe, maikaʻi loa kēia, no ka mea, aia ka pilikia i ka hopena a me kahi noi e hiki ke hoʻomaikaʻi.
Mai hele i ka mea kilokilo (C).
ʻAʻole paʻakikī ka hoʻolālā noiʻi, akā hōʻike maoli:
Hoʻolālā hoʻokō
ʻO ka mea hoihoi a me ka pono, e like me ka mea maʻamau, i ka hoʻomaka a me ka hopena.
Nested Loop (koina=935.84..479763226.18 lālani=3322 laula=135) (manawa maoli=31.536..8220420.295 lālani=8111656 puka lou=1)
Ka manawa hoʻolālā: 3.807ms
Ka manawa hana: 8222351.640ms
ʻOi aku ka manawa hana ma mua o 2 mau hola.
Kuhiakau wahahee i lawe i ka manawa
Kuhiakau 1- Ua hewa ka mea hoʻoponopono, kūkulu i ka hoʻolālā hewa.
No ka nānā ʻana i ka hoʻolālā hoʻokō, e hoʻohana mākou i ka pūnaewele . Akā naʻe, ʻaʻole i hōʻike ka pūnaewele i kekahi mea hoihoi a pono paha. I ka nānā mua a me ka lua, ʻaʻohe mea hiki ke kōkua maoli. He mea liʻiliʻi paha ka Full Scan. Hele i mua.
Kuhiakau 2-Ka hopena ma ke kumu mai ka ʻaoʻao o ka autovacuum, pono ʻoe e kāpae i nā kaʻa.
Akā, hana maikaʻi nā daemon autovacuum, ʻaʻohe hana lōʻihi. Kekahi ukana koʻikoʻi - ʻaʻole. Pono e ʻimi i kahi mea ʻē aʻe.
ʻO ke kuhiakau 3-Ka helu ʻana i ka wā kahiko, pono ʻoe e helu hou i nā lele āpau
Eia hou, ʻaʻole kēlā. Loaʻa nā helu helu. ʻO ka mea, no ka nele o nā pilikia me ka autovacuum, ʻaʻole ia he mea kupanaha.
E hoʻomaka ana e hoʻonui
ʻAʻole liʻiliʻi ka papa nui 'wdata', kokoke i 3 miliona mau moʻolelo.
A aia ma kēia pākaukau e hele ai ʻo Full Scan.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") A ME ((SubPlan 1) = s."SPENT_DATE"))
-> Seq Scan ma wdata w (koina=0.00..574151.49 lālani=26886249 laula=46) (manawa maoli=0.005..8153.565 lālani=26873950 puka lou=1)
Hana mākou ma ke ʻano maʻamau: "e hana mākou i kahi kuhikuhi a lele nā mea āpau".
Ua hana ʻia kahi papa kuhikuhi ma ke kahua "SPENT_ID".
Ma ka hopena:
Hoʻolālā hoʻokō nīnau me ka hoʻohana ʻana i kahi index
ʻAe, ua kōkua?
ʻO ia: 8 222 351.640 ms (ʻoi aku ma mua o 2 mau hola)
Ua lilo: 6 985 431.575 ms (kokoke 2 hola)
Ma keʻano laulā, nā'ōleʻa like, nānāʻaoʻao.
E hoomanao kakou i na mea kahiko:
“He mea like anei kāu, ʻaʻohe ʻēheu? E ʻimi".
Ma ke kumu, hiki ke kapa ʻia kēia he hopena maikaʻi, maikaʻi, ʻaʻole maikaʻi, akā ʻae ʻia. Ma ka liʻiliʻi loa, e hāʻawi i kahi hōʻike nui i ka mea kūʻai aku e wehewehe ana i ka nui o nā mea i hana ʻia a no ke kumu i maikaʻi ai ka mea i hana ʻia.
Eia naʻe, mamao loa ka hoʻoholo hope. mamao loa.
A i kēia manawa ʻo ka mea hoihoi loa - hoʻomau mākou i ka hoʻonui ʻana, e poli mākou i ka nīnau
KaʻAnuʻu mua - e hoʻohana i ka JOIN
Nīnau kākau hou ʻia, ua like me kēia (ʻoi aku ka maikaʻi):
Nīnau me ka hoʻohana ʻana iā JOINkoho i
p. "PARAMETER_ID" ma ke ʻano he 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 lower_spec_limit,
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,
unuhi (makahiki mai "SPENT_DATE") AS makahiki,
unuhi (mahina mai "SPENT_DATE") ma ke ʻano he mahina,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN i hoʻohana s ON w."SPENT_ID"=s."SPENT_ID"
HUI LOA pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
HOʻOPIʻI I loko i hoʻopau_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
HUI LOA pd pd ON pd."PD_ID" = sp."PD_ID"
kahi
s.“SPENT_DATE” >= '2018-07-01' A me s. “SPENT_DATE” <= '2018-09-30'AND
s."SPENT_DATE" = (KOHO MAX(s2."SPENT_DATE")
FROM wdata w2 INNER JOIN i hoʻohana s2 MA w2."SPENT_ID"=s2."SPENT_ID"
HUI LOOKO wdata w
ON w2."LRM" = w."LRM" );
Ka manawa hoʻolālā: 2.486ms
Ka manawa hana: 1223680.326ms
No laila eia ka hopena mua.
ʻO ia: 6 ms (kokoke 985 hola).
Ua lilo: 1 223 680.326 ms (ma luna o 20 minuke wale nō).
He hopena maikaʻi. I ka manaʻo, hou, e hiki ke kū ma laila. Akā no ka hoihoi ʻole, ʻaʻole hiki iā ʻoe ke hoʻōki.
NO KA
Kaʻanuʻu ʻelua - e kāpae i ka subquery pili
Ua hoʻololi ʻia ke kikokikona noi:
ʻAʻohe subquery pilikoho i
p. "PARAMETER_ID" ma ke ʻano he 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 lower_spec_limit,
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,
unuhi (makahiki mai "SPENT_DATE") AS makahiki,
unuhi (mahina mai "SPENT_DATE") ma ke ʻano he mahina,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN i hoʻohana s ON s."SPENT_ID" = w."SPENT_ID"
HUI LOA pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
HOʻOPIʻI I loko i hoʻopau_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
HUI LOA pd pd ON pd."PD_ID" = sp."PD_ID"
HUI I loko (KOHO w2."LRM", MAX(s2."SPENT_DATE")
FROM i hoʻohana i ka s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
HUI NA w2.LRM
) md ma w. "LRM" = md. "LRM"
kahi
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Ka manawa hoʻolālā: 2.291ms
Ka manawa hana: 165021.870ms
ʻO ia: 1 223 680.326 ms (ma luna o 20 minuke wale nō).
Ua lilo: 165 021.870 ms (ma luna o 2 mau minuke wale nō).
Ua maikaʻi loa kēia.
Eia naʻe, e like me ka ʻōlelo a ka ʻōlelo Pelekane,Akā, aia nō he akā". ʻO ka hopena maikaʻi loa e hoʻāla ʻakomi i ka kānalua. Ua hewa kekahi mea maanei.
ʻO ke kuhiakau o ka hoʻoponopono ʻana i ka nīnau e hoʻopau i ka subquery i hoʻopili ʻia ua pololei. Akā, pono ia i kahi tweaking liʻiliʻi e kiʻi i ka hopena hope loa.
ʻO ka hopena, ʻo ka hopena waena waena:
Huli i hoʻoponopono ʻia me ka ʻole o ka subquery pilikoho i
p. "PARAMETER_ID" ma ke ʻano he 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 lower_spec_limit,
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,
extract(makahiki mai s. "SPENT_DATE") AS makahiki,
unuhi (mahina mai s. "SPENT_DATE") ma ke ʻano he mahina,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN i hoʻohana s ON s."SPENT_ID" = w."SPENT_ID"
HUI LOA pmtr p ON p."PARAMETER_ID" = w."PARAMETER_ID"
HOʻOPIʻI I loko i hoʻopau_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
HUI LOA pd pd ON pd."PD_ID" = sp."PD_ID"
HUI I loko ( KOHO w2."LRM", MAX(s2."SPENT_DATE") AS "SPENT_DATE"
FROM i hoʻohana i ka s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
HUI NA w2.LRM
) md ON md."SPENT_DATE" = s."SPENT_DATE" AND md."LRM" = w."LRM"
kahi
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Ka manawa hoʻolālā: 3.192ms
Ka manawa hana: 208014.134ms
No laila, ʻo ka mea i loaʻa iā mākou ka hopena mua i ʻae ʻia, ʻaʻole mākou hilahila e hōʻike i ka mea kūʻai aku:
Hoʻomaka me: 8 222 351.640 ms (ʻoi aku ma mua o 2 mau hola)
Loaʻa: 1 ms (ma luna o 223 mau minuke).
Ka hopena (waena): 208 014.134 ms (ma luna o 3 mau minuke wale nō).
He hopena maikaʻi loa.
ʻO ka hopena
Ua hiki ke oki.
AKA…
Hele mai ka ʻai me ka ʻai. E alakaʻi ʻia ke ala ma ka hele wāwae. ʻO ka hopena waena. Paʻa make. Etc.
E hoʻomau kākou me ka hoʻonui ʻana.
Nui manaʻo. ʻOi aku ka noʻonoʻo ʻana ʻaʻole kūʻē ka mea kūʻai aku. A me ka ikaika loa - no.
No laila, ʻo ia ka manawa no ka hoʻolālā hou ʻana i ka waihona. ʻAʻole hiki ke hoʻoponopono hou ʻia ka ʻōnaehana hulina ponoʻī (ʻoiai, e like me ka mea i ʻike ʻia ma hope, aia kahi koho e hōʻoia i ka pau ʻole o nā mea āpau). Akā ʻo ka hoʻomaka ʻana i ka hoʻolālā ʻana a me ka hoʻomohala ʻana i ka hoʻolālā ʻikepili he manaʻo hoʻohiki loa. A ʻo ka mea nui ka hoihoi. Eia hou, e hoʻomanaʻo i kou wā ʻōpiopio. ʻAʻole wau i lilo koke i DBA, ua ulu au ma ke ʻano he polokalamu (BASIC, assembler, C, double-plus C, Oracle, plsql). He kumuhana hoihoi, ʻoiaʻiʻo, no kahi memo kaʻawale ;-).
Eia naʻe, mai haʻalele kākou.
A pēlā,
A e kōkua paha ka ʻāpana iā mākou?
Spoiler - "ʻAe, ua kōkua ia, a i ka hoʻonui ʻana i ka hana, me."
Akā, he moʻolelo ʻokoʻa loa kēlā...
E hoʻomau ʻia…
Source: www.habr.com