Mukukumbukira momwe zonse zidayambira. Zonse zinali kwa nthawi yoyamba ndi mobwerezabwereza

Za momwe ndimayenera kukwaniritsira funso la PostgreSQL ndi zomwe zidatulukamo.
Chifukwa chiyani munayenera kutero? Inde, chifukwa kwa zaka 4 zapitazi zonse zinkagwira ntchito mwakachetechete, modekha, ngati wotchi ikugunda.
Monga epigraph.

Kutengera zochitika zenizeni.
Mayina onse asinthidwa, zochitika zimangochitika mwachisawawa.

Mukakwaniritsa zotsatira zina, zimakhala zosangalatsa kukumbukira zomwe zidayambitsa chiyambi, pomwe zonse zidayambira.

Chifukwa chake, zomwe zidachitikazi zafotokozedwa mwachidule m'nkhani yakuti "Kaphatikizidwe ngati njira imodzi yopititsira patsogolo magwiridwe antchito a PostgreSQL".

Zingakhale zosangalatsa kukonzanso mndandanda wa zochitika zam'mbuyo.
Mbiri idasunga tsiku lenileni loyambira - 2018-09-10 18:02:48.
Komanso, m'nkhaniyi pali pempho limene zonse zinayambira:
Pempho lavutoSANKHANI
p.“PARAMETER_ID” monga parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnamber,
w. "LRM" AS LRM,
w. "LOTID" AS zambiri,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS zochepa_spec_malire,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS dzina lomaliza,
s.“SPENT_DATE” AS tsiku lothera,
kuchotsa(chaka kuchokera pa "SPENT_DATE") AS chaka,
kuchotsa(mwezi kuchokera pa "SPENT_DATE") monga mwezi,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
KUCHOKERA pa wdata w,
anathera s,
pmtr p,
spend_pd sp,
pd pd
KULI s.“SPENT_ID” = w.“SPENT_ID”
NDI p."PARAMETER_ID" = w."PARAMETER_ID"
NDI s.“SPENT_ID” = sp.“SPENT_ID”
NDI pd."PD_ID" = sp."PD_ID"
NDI s.“SPENT_DATE” >= '2018-07-01' NDI s.“SPENT_DATE” <= '2018-09-30'
ndi s.“SPENT_DATE” = (SANKHANI MAX(s2.“SPENT_DATE”)
KUCHOKERA mu s2,
mawu w2
KUTI s2.“SPENT_ID” = w2.“SPENT_ID”
NDI w2.“LRM” = w.“LRM”);

Kufotokozera kwavutoli ndikoyenera - "Chilichonse ndichabwino. Ndiuzeni vuto ndi chiyani.”
Nthawi yomweyo ndidakumbukira nthano yanthawi ya 3 ndi theka inchi:

Wopunduka amabwera kwa wowononga.
-Palibe chomwe chimandiyendera, tandiuze komwe kuli vuto.
- Mu DNA ...

Koma, ndithudi, iyi si njira yothetsera zochitika zogwirira ntchito. “Mwina sangatimvetse"(Ndi). Tiyenera kuzilingalira.
Chabwino, tiyeni tikumbe. Mwinamwake chinachake chidzaunjikana monga chotsatira.

Kufufuza kunayambika

Kotero, zomwe zingawoneke nthawi yomweyo ndi maso amaliseche, popanda ngakhale kufotokoza.
1) JOIN sagwiritsidwa ntchito. Izi ndizoyipa, makamaka ngati kuchuluka kwa maulumikizidwe kuli kopitilira imodzi.
2) Koma choyipa kwambiri ndikuphatikizana, kuphatikizanso, ndi kuphatikiza. Izi ndi zoipa kwambiri.
Izi ndi zoipa ndithu. Koma izi ndi mbali imodzi yokha. Kumbali inayi, izi ndi zabwino kwambiri, chifukwa vutoli liri ndi yankho komanso pempho lomwe lingathe kuwongolera.
Osapita kwa wobwebweta (C).
Dongosolo lamafunso silili lovuta, koma likuwonetsa:
Mapulani a Ntchito

Chosangalatsa komanso chothandiza, monga mwachizolowezi, chiri pachiyambi ndi kumapeto.
Nested Loop (mtengo=935.84..479763226.18 mizere=3322 wide=135) (nthawi yeniyeni=31.536..8220420.295 mizere=8111656 malupu=1)
Nthawi yokonzekera: 3.807 ms
Nthawi yochitira: 8222351.640 ms
Nthawi yomaliza ndi yopitilira maola awiri.

Zolingalira zabodza zomwe zinatenga nthawi

Hypothesis 1 - Wowongolera amalakwitsa ndikupanga pulani yolakwika.

Kuti tiwone mwatsatanetsatane dongosolo la kuphedwa, tidzagwiritsa ntchito tsambalo https://explain.depesz.com/. Komabe, tsambalo silinawonetse chilichonse chosangalatsa kapena chothandiza. Poyang'ana koyamba ndi kachiwiri, palibe chomwe chingathandize kwenikweni. Kodi ndizotheka kuti Full Scan ndiyochepa. Chitani zomwezo.

Hypothesis 2-Impact pamunsi kuchokera kumbali ya autovacuum, muyenera kuchotsa mabuleki.

Koma ma daemoni a autovacuum amachita bwino, palibe njira zotalikirapo. Palibe katundu wovuta. Tiyenera kuyang'ana chinthu china.

Hypothesis 3 - Ziwerengero ndi zachikale, zonse ziyenera kuwerengedwanso

Kachiwiri, osati izo. Ziwerengero ndi zaposachedwa. Zomwe, chifukwa chosowa mavuto ndi autovacuum, sizosadabwitsa.

Tiyeni tiyambe optimizing

Gome lalikulu 'wdata' siliri laling'ono, pafupifupi 3 miliyoni zolemba.
Ndipo ndi tebulo ili lomwe Full Scan limatsatira.

Hashi Cond: ((w."SPENT_ID" = s."SPENT_ID") NDI ((SubPlan 1) = s."SPENT_DATE")
-> Seq Scan pa wdata w (mtengo=0.00..574151.49 mizere=26886249 wide=46) (nthawi yeniyeni=0.005..8153.565 mizere=26873950 malupu=1)
Timachita zomwezo: "bwerani, tiyeni tipange index ndipo zonse ziziwuluka."
Adapanga cholozera pagawo la "SPENT_ID".
Zotsatira zake:
Funso lokonzekera pogwiritsa ntchito index

Chabwino, zidathandiza?
Anali: 8 222 351.640 ms (kupitirira pang'ono maola awiri)
Zinakhala: 6 985 431.575 ms (pafupifupi maola 2)
Ambiri, yemweyo maapulo, mbali view.
Tiyeni tikumbukire ma classics:
“Kodi uli naye yemweyo, koma wopanda mapiko? Adzafufuza".

Kwenikweni, izi zitha kutchedwa zotsatira zabwino, osati zabwino, koma zovomerezeka. Pang'ono ndi pang'ono, perekani lipoti lalikulu kwa kasitomala lofotokoza kuchuluka kwa zomwe zachitika komanso chifukwa chake zomwe zidachitika zinali zabwino.
Komabe, chisankho chomaliza chidakali kutali. Patali kwambiri.

Ndipo tsopano chinthu chosangalatsa kwambiri - tikupitiliza kukhathamiritsa, tidzapukuta pempholo

Khwerero XNUMX - Gwiritsani ntchito JOIN

Pempho lolembedwanso tsopano likuwoneka motere (chabwino osachepera kukongola):
Funsani pogwiritsa ntchito JOINSANKHANI
p.“PARAMETER_ID” monga parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnamber,
w. "LRM" AS LRM,
w. "LOTID" AS zambiri,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS zochepa_spec_malire,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS dzina lomaliza,
s.“SPENT_DATE” AS tsiku lothera,
kuchotsa(chaka kuchokera pa "SPENT_DATE") AS chaka,
kuchotsa(mwezi kuchokera pa "SPENT_DATE") monga mwezi,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
KUCHOKERA pa wdata w INNER JOIN adathera pa w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p PA p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spend_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd PA pd.“PD_ID” = sp.“PD_ID”
KUMENE
s.“SPENT_DATE” >= '2018-07-01' NDI s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (SANKHANI MAX(s2.“SPENT_DATE”)
KUCHOKERA pa wdata w2 INNER JOIN inathera s2 PA w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
PA w2.“LRM” = w.“LRM” );
Nthawi yokonzekera: 2.486 ms
Nthawi yochitira: 1223680.326 ms

Kotero, chotsatira choyamba.
Anali: 6 ms (pafupifupi maola awiri).
Zinakhala: 1 223 680.326 ms (kungopitirira mphindi 20).
Zotsatira zabwino. M'malo mwake, titha kuyimiranso pamenepo. Koma ndizosasangalatsa, simungathe kuyimitsa.
ZA

Khwerero XNUMX - chotsani kubwereza kogwirizana

Mawu opempha osinthidwa:
Popanda ma subquery ogwirizanaSANKHANI
p.“PARAMETER_ID” monga parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnamber,
w. "LRM" AS LRM,
w. "LOTID" AS zambiri,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS zochepa_spec_malire,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS dzina lomaliza,
s.“SPENT_DATE” AS tsiku lothera,
kuchotsa(chaka kuchokera pa "SPENT_DATE") AS chaka,
kuchotsa(mwezi kuchokera pa "SPENT_DATE") monga mwezi,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
KUCHOKERA pa wdata w INNER JOIN wathera s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p PA p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spend_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd PA pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (SAKANI w2.“LRM”, MAX(s2.“SPENT_DATE”)
KUCHOKERA mu s2 INNER JOIN wdata w2 PA s2.“SPENT_ID” = w2.“SPENT_ID”
GULU LA W2.“LRM”
) md pa w.“LRM” = md.“LRM”
KUMENE
s."SPENT_DATE" >= '2018-07-01' NDI s."SPENT_DATE" <= '2018-09-30';
Nthawi yokonzekera: 2.291 ms
Nthawi yochitira: 165021.870 ms

Anali: 1 223 680.326 ms (kungopitirira mphindi 20).
Zinakhala: 165 021.870 ms (kungopitirira mphindi 2).
Izi ndizabwino kale.
Komabe, monga a British amati "Koma, pali nthawi zonse koma" Chotsatira chomwe chili chabwino kwambiri chiyenera kuyambitsa kukayikira. China chake chalakwika apa.

Lingaliro la kukonza funso kuti lichotse gawo lolumikizana ndilolondola. Koma muyenera kusintha pang'ono kuti zotsatira zomaliza zikhale zolondola.
Zotsatira zake, chotsatira choyamba chapakati:
Funso losinthidwa popanda mafunso ogwirizanaSANKHANI
p.“PARAMETER_ID” monga parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnamber,
w. "LRM" AS LRM,
w. "LOTID" AS zambiri,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS zochepa_spec_malire,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS dzina lomaliza,
s.“SPENT_DATE” AS tsiku lothera,
kuchotsa(chaka kuchokera s.“SPENT_DATE”) AS chaka,
kuchotsa(mwezi kuchokera s.“SPENT_DATE”) monga mwezi,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
KUCHOKERA pa wdata w INNER JOIN wathera s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p PA p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spend_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd PA pd.“PD_ID” = sp.“PD_ID”
INNER JOIN ( SANKHANI w2.“LRM”, MAX(s2.“SPENT_DATE”) MONGA “SPENT_DATE”
KUCHOKERA mu s2 INNER JOIN wdata w2 PA s2.“SPENT_ID” = w2.“SPENT_ID”
GULU LA W2.“LRM”
) md PA md.“SPENT_DATE” = s.“SPENT_DATE” NDI md.“LRM” = w.“LRM”
KUMENE
s."SPENT_DATE" >= '2018-07-01' NDI s."SPENT_DATE" <= '2018-09-30';
Nthawi yokonzekera: 3.192 ms
Nthawi yochitira: 208014.134 ms

Chifukwa chake, zomwe timathera ndi zotsatira zovomerezeka zoyamba, zomwe sizochititsa manyazi kuwonetsa kasitomala:
Zinayamba ndi: 8 222 351.640 ms (kuposa maola awiri)
Tidakwanitsa kukwaniritsa: 1 ms (pang'ono kuposa mphindi 223).
Zotsatira (zakanthawi): 208 014.134 ms (kungopitirira mphindi 3).

Zotsatira zabwino kwambiri.

Zotsatira

Ife tikanaima pamenepo.
KOMA…
Kulakalaka kumabwera ndi kudya. Woyenda adzadziwa njira. Chotsatira chilichonse ndi chapakatikati. Anaima ndi kufa. Ndi zina zotero.
Tiyeni tipitilize kukhathamiritsa.
Lingaliro lalikulu. Makamaka poganizira kuti kasitomala sanasamale. Ndipo ngakhale mwamphamvu kwa izo.

Chifukwa chake, ndi nthawi yokonzanso database. Mafunso akewo sangathenso kukonzedwanso (ngakhale, monga zinachitikira pambuyo pake, pali njira yowonetsetsa kuti zonse zikulephera). Koma kuyamba kukhathamiritsa ndi kupanga mapangidwe a database kale ndi lingaliro lolimbikitsa kwambiri. Ndipo chofunika kwambiri chidwi. Apanso, kumbukirani ubwana wanu. Kupatula apo, sindinakhale DBA nthawi yomweyo, ndinakulira ngati wopanga mapulogalamu (BASIC, assembler, C, double-plus C, Oracle, plsql). Nkhani yosangalatsa, inde, ya memoir yosiyana ;-).
Komabe, tisasokonezedwe.

Ndipo kotero,

Kapena mwina kugawa kungatithandize?
Spoiler - "Inde, zathandiza, kuphatikiza pakuwongolera magwiridwe antchito."

Koma ndi nkhani yosiyana kotheratu...

Zipitilizidwa…

Source: www.habr.com