Me pehea taku whakatika i te patai PostgreSQL me nga mea i puta mai i enei mea katoa.
He aha koe i whai ai? Ae, na te mea mo nga tau e 4 o mua i mahi marie, marino, ano he karaka karaka.
Hei epigraph.
I runga i nga kaupapa tuturu.
Kua hurihia nga ingoa katoa, he tupono noa.
Ka tutuki i a koe tetahi hua, he mea pai ki te mahara he aha te mea i akiaki i te timatanga, i timata ai te katoa.
Na, he aha te mea i puta hei hua ka whakaahuahia i roto i te tuhinga "".
He pai pea ki te hanga ano i te mekameka o nga huihuinga o mua.
I tiakina e te hitori te ra tiimata tika - 2018-09-10 18:02:48.
Ano, kei roto i te korero he tono i timata ai:
Tono raruraruTīpakohia
p.“PARAMETER_ID” hei parameter_id,
pd."PD_NAME" AS pd_ingoa,
pd."CUSTOMER_PARTNUMBER" AS kaihoko_wahi nama,
w. "LRM" AS LRM,
w. "LOTID" AS rota,
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 pau_ingoa,
s."SPENT_DATE" AS pau_ra,
tangohanga(tau mai i "SPENT_DATE") AS tau,
tangohanga(marama mai i "SPENT_DATE") hei marama,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_ingoa,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
MAI wdata w,
pau s,
pmtr p,
pau_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
ME p."PARAMETER_ID" = w."PARAMETER_ID"
ME s.“SPENT_ID” = sp.“SPENT_ID”
ME pd."PD_ID" = sp."PD_ID"
ME s.“SPENT_DATE” >= '2018-07-01' ME s.“SPENT_DATE” <= '2018-09-30'
me te s.“SPENT_DATE” = (KIRIHIA MAX(s2.“SPENT_DATE”)
MAI pau s2,
wraraunga w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
ME w2.“LRM” = w.“LRM”);
Ko te whakamaaramatanga o te raru he paerewa noa - “He kino nga mea katoa. Korerotia mai he aha te raruraru.”
I mahara tonu ahau ki tetahi korero mai i nga wa o te 3 me te hawhe inihi puku:
Ka tae mai te lamer ki te kaiwhaiwhai.
-Kare he mea e pai ana mo au, korero mai kei hea te raru.
- Kei roto i te DNA ...
Engari ko te tikanga, ehara tenei i te huarahi hei whakaoti i nga maiki mahi. “Kaore pea ratou e marama ki a tatou"(Me). Me mohio tatou.
Ana, me keri tatou. Tera pea ka kohia tetahi mea hei hua.
I timata te tirotiro
Na, he aha te mea ka kitea tonu e te kanohi tahanga, me te kore e huri ki te WHAKAMAHI.
1) Kaore e whakamahia nga hono. He kino tenei, ina koa ka nui ake te maha o nga hononga i te kotahi.
2) Engari ko te mea kino rawa atu ko nga paatai e hono ana, me te whakahiato. He tino kino tenei.
He kino tenei o te akoranga. Engari ko tenei anake i te taha kotahi. I tetahi atu taha, he tino pai tenei, na te mea he maarama te raru me te tono ka taea te whakapai ake.
Kaua e haere ki te tangata matakite (C).
Ko te mahere uiui ehara i te mea uaua, engari he tino tohu:
Mahere Whakatutuki
Ko te mea tino pai me te whai hua, penei i mua, kei te timatanga me te mutunga.
Kohanga Kohanga (utu=935.84..479763226.18 rarangi=3322 whanui=135) (wa mooni=31.536..8220420.295 rarangi=8111656 koropiko=1)
Te wa whakamahere: 3.807 ms
Te wa mahi: 8222351.640 ms
Neke atu i te 2 haora te wa whakaoti.
Nga whakapae teka i pau te wa
Te whakapae 1 - Ka pohehe te kaihoroi me te hanga i te mahere he.
Hei whakaata i te mahere mahi, ka whakamahia e matou te waahi . Heoi, kaore i whakaatuhia e te paetukutuku tetahi mea whakamere, whai hua ranei. I te titiro tuatahi me te tuarua, kaore he mea hei awhina. He iti noa te Matawai Katoa. Haere i mua.
Whakaaro 2-Panga ki runga i te turanga mai i te taha autovacuum, me whakakorehia e koe nga parai.
Engari he pai te mahi a nga daemons autovacuum, kaore he mahinga roa. Kaore he kawenga nui. Me rapu tatou mo tetahi atu mea.
Whakaaro 3 - Kua tawhito nga tatauranga, me tatau ano nga mea katoa
Ano, ehara i tera. Ko nga tatauranga kei te hou tonu. Ko te mea, na te kore o nga raru me te autovacuum, ehara i te mea miharo.
Kia timata tatou ki te arotau
Ko te tepu matua 'wdata' ehara i te mea iti, tata ki te 3 miriona rekoata.
A ko tenei tepu e whai ana te Full Scan.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") ME ((Mahereroto 1) = s."SPENT_DATE"))
-> Seq Matawai i runga i te wdata w (utu=0.00..574151.49 rarangi=26886249 whanui=46) (wa tuturu=0.005..8153.565 rarangi=26873950 koropiko=1)
Ka mahia e matou te mea paerewa: "haere mai, me hanga he taurangi ka rere nga mea katoa."
I hangaia he taurangi i runga i te mara "SPENT_ID".
Ko te mutunga:
Mahere mahi uiui ma te whakamahi i te taupū
Ana, i awhina ranei?
Ko: 8 222 351.640 ms (he iti ake i te 2 haora)
Kua riro: 6 985 431.575 ms (tata ki te 2 haora)
I roto i te whānui, te taua aporo, tirohanga taha.
Kia maumahara tatou ki nga korero tawhito:
“Kei a koe ano te mea kotahi, engari kahore he parirau? Ka rapu".
Ko te tikanga, ka kiia tenei he hua pai, he pai, ehara i te pai, engari he pai. I te iti rawa, hoatu he ripoata nui ki te kaihoko e whakaatu ana i te nui o nga mea kua mahia me te aha i pai ai te mahi.
Heoi ano, kei tawhiti tonu te whakatau whakamutunga. Tino tawhiti.
Na inaianei ko te mea tino pai - kei te haere tonu taatau ki te arotau, ka whakakorikoria te tono
Hipanga Tuatahi - Whakamahia te JOIN
Inaianei te ahua o te tono tuhi ano (pai i te iti rawa atu ataahua):
Uiui ma te whakamahi JOINTīpakohia
p.“PARAMETER_ID” hei parameter_id,
pd."PD_NAME" AS pd_ingoa,
pd."CUSTOMER_PARTNUMBER" AS kaihoko_wahi nama,
w. "LRM" AS LRM,
w. "LOTID" AS rota,
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 pau_ingoa,
s."SPENT_DATE" AS pau_ra,
tangohanga(tau mai i "SPENT_DATE") AS tau,
tangohanga(marama mai i "SPENT_DATE") hei marama,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_ingoa,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
MAI i wdata w ROTO TOHUTOHU i pau s ON w.“SPENT_ID”=s.”“SPENT_ID”
KAUPAPA KAUPAPA pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
KAUPAPA KAUPAPA whakapau_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
KAUPAPA KAUPAPA Pd pd KI te pd.“PD_ID” = sp.“PD_ID”
HEA
s.“SPENT_DATE” >= '2018-07-01' ME s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (KIRIHIA MAX(s2.“SPENT_DATE”)
MAI i te wdata w2 WHAKATOKANGA o roto i pau s2 I w2.“SPENT_ID”=s2.“SPENT_ID”
KAUPAPA ROTO wdata w
ON w2.“LRM” = w.“LRM” );
Te wa whakamahere: 2.486 ms
Te wa mahi: 1223680.326 ms
Na, ko te hua tuatahi.
Ko: 6 ms (tata ki te 985 haora).
Kua riro: 1 223 680.326 ms (neke atu i te 20 meneti).
Hua pai. I roto i te kaupapa, ano, ka taea e taatau ki reira. Engari he mea tino pai, kaore e taea e koe te mutu.
MO NGA
Hipanga tuarua - peia atu te urunga korero
Kua hurihia te kupu tono:
Karekau he patai taapiriTīpakohia
p.“PARAMETER_ID” hei parameter_id,
pd."PD_NAME" AS pd_ingoa,
pd."CUSTOMER_PARTNUMBER" AS kaihoko_wahi nama,
w. "LRM" AS LRM,
w. "LOTID" AS rota,
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 pau_ingoa,
s."SPENT_DATE" AS pau_ra,
tangohanga(tau mai i "SPENT_DATE") AS tau,
tangohanga(marama mai i "SPENT_DATE") hei marama,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_ingoa,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
MAI i te wdata w WHAKATOKANGA ROTO i pau i te s.“SPENT_ID” = w.“SPENT_ID”
KAUPAPA KAUPAPA pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
KAUPAPA KAUPAPA whakapau_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
KAUPAPA KAUPAPA Pd pd KI te pd.“PD_ID” = sp.“PD_ID”
KAUPAPA KAUPAPA (KORERO w2.“LRM”, MAX(s2.“SPENT_DATE”)
MAI i pau s2 ROTO Hono wdata w2 KI s2.“SPENT_ID” = w2.“SPENT_ID”
Rōpū NA w2.“LRM”
) md runga w.“LRM” = md.“LRM”
HEA
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Te wa whakamahere: 2.291 ms
Te wa mahi: 165021.870 ms
Ko: 1 223 680.326 ms (neke atu i te 20 meneti).
Kua riro: 165 021.870 ms (neke atu i te 2 meneti).
Kua tino pai tenei.
Heoi ano, e kii ana te Ingarangi "Engari, he engari i nga wa katoa" Ko te hua he pai rawa atu me whakaoho aunoa i te whakapae. He he kei konei.
He tika te whakapae mo te whakatika i te patai ki te whakakore i te patai taapiri. Engari me huri iti kia tika te hua whakamutunga.
Ko te mutunga, ko te hua takawaenga tuatahi:
Uiui kua whakatikahia me te kore he patai taapiriTīpakohia
p.“PARAMETER_ID” hei parameter_id,
pd."PD_NAME" AS pd_ingoa,
pd."CUSTOMER_PARTNUMBER" AS kaihoko_wahi nama,
w. "LRM" AS LRM,
w. "LOTID" AS rota,
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 pau_ingoa,
s."SPENT_DATE" AS pau_ra,
tangohanga(tau mai i te s.“SPENT_DATE”) AS tau,
tangohanga(marama mai i te s.“SPENT_DATE”) hei marama,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_ingoa,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
MAI i te wdata w WHAKATOKANGA ROTO i pau i te s.“SPENT_ID” = w.“SPENT_ID”
KAUPAPA KAUPAPA pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
KAUPAPA KAUPAPA whakapau_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
KAUPAPA KAUPAPA Pd pd KI te pd.“PD_ID” = sp.“PD_ID”
KAUPAPA I ROTO ( KIA w2. “LRM”, MAX(s2.“SPENT_DATE”) AS “SPENT_DATE”
MAI i pau s2 ROTO Hono wdata w2 KI s2.“SPENT_ID” = w2.“SPENT_ID”
Rōpū NA w2.“LRM”
) md I te md.“SPENT_DATE” = s.“SPENT_DATE” ME md. “LRM” = w.“LRM”
HEA
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Te wa whakamahere: 3.192 ms
Te wa mahi: 208014.134 ms
Na, ko te mea ka mutu ko te hua tuatahi e manakohia ana, ehara i te mea whakama ki te whakaatu ki te kaihoko:
I timata me: 8 222 351.640 ms (neke atu i te 2 haora)
I taea e matou te whakatutuki: 1 ms (he iti ake i te 223 meneti).
Hua (waahanga): 208 014.134 ms (neke atu i te 3 meneti).
Hua pai.
Ko te hua
Ka mutu pea i reira.
ENGARI…
Ka tae mai te hiahia ki te kai. Ko te tangata e hikoi ana ka rangatira te huarahi. Ko nga hua katoa he takawaenga. Ka tu ka mate. He aha atu.
Me haere tonu te arotautanga.
He whakaaro nui. Ina koa kaore te kaihoko i whakaaro. A ahakoa kaha mo taua mea.
Na, kua tae ki te wa mo te hoahoa ano i te papaunga raraunga. Ko te hanganga uiui ake ka kore e taea te arotau (ahakoa, i te mea i puta i muri mai, he whiringa hei whakarite ka taka nga mea katoa). Engari ki te timata ki te arotau me te whakawhanake i te hoahoa papaunga raraunga he whakaaro tino pai. A ko te mea tino whakamere. Kia mahara ano ki to tamarikitanga. Kaore au i noho tonu hei DBA, i tipu ake ahau hei kaiwhakatakoto kaupapa (BASIC, assembler, C, double-plus C, Oracle, plsql). He kaupapa whakamere, o te akoranga, mo te memoir motuhake ;-).
Heoi, kaua tatou e whakararu.
Na
Ma te wehewehe ranei ka awhina i a tatou?
Spoiler - "Ae, i awhina, tae atu ki te whakapai ake i nga mahi."
Engari he korero rereke tera...
Ka haere tonu…
Source: will.com