Hais txog yuav ua li cas peb yuav tsum optimize cov lus nug PostgreSQL thiab dab tsi tawm ntawm nws tag nrho.
Vim li cas koj yuav tsum tau? Yog lawm, vim tias 4 xyoo dhau los txhua yam ua haujlwm ntsiag to, ntsiag to, zoo li lub moos ticking.
Raws li ib tug epigraph.
Raws li cov xwm txheej tiag tiag.
Tag nrho cov npe tau raug hloov, coincidences yog random.
Thaum koj ua tiav ib qho txiaj ntsig, nws yog ib qho tseem ceeb kom nco ntsoov tias dab tsi yog lub zog rau qhov pib, qhov twg txhua yam pib.
Yog li, dab tsi tshwm sim los ntawm qhov tshwm sim tau piav qhia luv luv hauv kab lus "".
Nws yuav yog qhov nthuav kom rov tsim cov saw hlau ntawm cov xwm txheej dhau los.
Cov keeb kwm khaws cia hnub pib - 2018-09-10 18:02:48.
Tsis tas li ntawd, nyob rau hauv zaj dab neeg muaj ib qho kev thov los ntawm nws txhua tus pib:
Teeb meem thovXaiv
p.“PARAMETER_ID” as 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 qis_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS siv_name,
s.“SPENT_DATE” AS siv_date,
rho tawm (xyoo los ntawm "SPENT_DATE") AS xyoo,
rho tawm (hli los ntawm "SPENT_DATE") raws li lub hli,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
Los ntawm wdata w,
siv s,
pmtr p,
siv_pd sp,
pd ua pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
THIAB p. "PARAMETER_ID" = w."PARAMETER_ID"
THIAB s.“SPENT_ID” = sp.“SPENT_ID”
THIAB pd."PD_ID" = sp."PD_ID"
THIAB s.“SPENT_DATE” >= '2018-07-01' THIAB s.“SPENT_DATE” <= '2018-09-30'
thiab s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
Los ntawm siv s2,
ua w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
THIAB w2.“LRM” = w.“LRM”);
Kev piav qhia ntawm qhov teeb meem yog tus qauv twv ua ntej - "Txhua yam tsis zoo. Qhia rau kuv tias qhov teeb meem yog dab tsi. "
Kuv tam sim ntawd nco ntsoov ib qho anecote los ntawm lub sij hawm ntawm 3 thiab ib nrab nti drives:
Lub lamer tuaj rau tus hacker.
-Tsis muaj dab tsi ua haujlwm rau kuv, qhia kuv qhov teeb meem nyob qhov twg.
- Hauv DNA ...
Tab sis tau kawg, qhov no tsis yog txoj hauv kev los daws cov teeb meem kev ua tau zoo. “Tej zaum lawv yuav tsis to taub peb"(Nrog). Peb yuav tsum xav txog nws.
Zoo, cia peb khawb. Tej zaum ib yam dab tsi yuav sib sau ua ke.
Kev tshawb nrhiav pib
Yog li ntawd, dab tsi tuaj yeem pom tam sim ntawm qhov muag liab qab, tsis tas yuav siv los piav qhia.
1) JOINs tsis siv. Qhov no yog qhov phem, tshwj xeeb tshaj yog tias tus lej ntawm kev sib txuas yog ntau tshaj ib qho.
2) Tab sis qhov phem dua yog kev sib txheeb subqueries, ntxiv rau, nrog kev sib sau. Qhov no phem heev.
Qhov no yog qhov phem. Tab sis qhov no tsuas yog nyob ntawm ib sab tes xwb. Ntawm qhov tod tes, qhov no yog qhov zoo heev, vim tias qhov teeb meem kom meej meej muaj kev daws teeb meem thiab kev thov uas tuaj yeem txhim kho.
Tsis txhob mus ntsib kws kho mob (C).
Txoj kev npaj nug tsis yog qhov nyuaj, tab sis nws yog qhov qhia tau zoo:
Kev Npaj Ua Haujlwm
Qhov nthuav thiab muaj txiaj ntsig tshaj plaws, raws li niaj zaus, yog thaum pib thiab xaus.
Nested Loop (tus nqi = 935.84..479763226.18 rows = 3322 dav = 135) (lub sijhawm tiag tiag = 31.536..8220420.295 rows=8111656 loops=1)
Lub sijhawm npaj: 3.807 ms
Lub Sijhawm Ua Haujlwm: 8222351.640 ms
Lub sijhawm ua tiav yog ntau tshaj 2 teev.
Tsis tseeb hypotheses uas siv sij hawm
Hypothesis 1 - Tus optimizer ua yuam kev thiab tsim cov phiaj xwm tsis raug.
Txhawm rau pom qhov kev npaj ua tiav, peb yuav siv lub xaib . Txawm li cas los xij, lub xaib tsis tau qhia dab tsi nthuav lossis muaj txiaj ntsig. Thaum xub thawj thiab zaum ob, tsis muaj dab tsi uas yuav pab tau tiag tiag. Puas yog nws ua tau tias Full Scan tsawg tsawg. Ua ntej.
Hypothesis 2-Kev cuam tshuam ntawm lub hauv paus los ntawm autovacuum sab, koj yuav tsum tau tshem ntawm cov nres.
Tab sis autovacuum daemons coj zoo, tsis muaj cov txheej txheem ntev ntev. Tsis muaj load hnyav. Peb yuav tsum nrhiav lwm yam.
Hypothesis 3 - Statistics yog outdated, txhua yam yuav tsum tau recalculated
Ntxiv dua thiab, tsis yog li ntawd. Cov ntaub ntawv txheeb cais tau ua tiav. Uas, muab qhov tsis muaj teeb meem nrog autovacuum, tsis xav tsis thoob.
Cia peb pib optimizing
Lub ntsiab lus 'wdata' yeej tsis yog me me, yuav luag 3 lab cov ntaub ntawv.
Thiab nws yog lub rooj no uas Full Scan ua raws.
Hash Cond: ((w.SPENT_ID" = s.SPENT_ID") THIAB ((SubPlan 1) = s.SPENT_DATE"))
-> Sib Scan ntawm wdata w (tus nqi = 0.00..574151.49 rows=26886249 dav=46) (lub sijhawm tiag tiag = 0.005..8153.565 rows=26873950 loops=1)
Peb ua tus qauv: "Los ntawm, cia peb ua qhov ntsuas thiab txhua yam yuav ya mus."
Tsim qhov ntsuas ntawm "SPENT_ID" teb
Raws li qhov tshwm sim:
Cov lus nug ua tiav txoj kev npaj siv index
Zoo, nws puas pab tau?
Yog: 8 222 351.640 ms (me ntsis ntau tshaj 2 teev)
Ua: 6 985 431.575 ms (yuav luag 2 teev)
Feem ntau, tib apples, sab saib.
Cia peb nco ntsoov cov classics:
“Koj puas muaj tib tug, tab sis tsis muaj tis? Yuav nrhiav".
Hauv txoj ntsiab cai, qhov no tuaj yeem raug hu ua qhov txiaj ntsig zoo, zoo, tsis zoo, tab sis lees txais. Yam tsawg kawg nkaus, muab ib daim ntawv qhia loj rau cov neeg siv khoom piav qhia txog ntau npaum li cas tau ua tiav thiab vim li cas qhov ua tau zoo.
Tab sis tseem, qhov kev txiav txim zaum kawg tseem nyob deb. deb heev.
Thiab tam sim no qhov nthuav tshaj plaws - peb txuas ntxiv optimize, peb yuav polish qhov kev thov
Kauj Ruam Ib - Siv JOIN
Qhov kev thov rov sau dua tam sim no zoo li qhov no (zoo yam tsawg kawg zoo nkauj dua):
Lus nug siv JOINXaiv
p.“PARAMETER_ID” as 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 qis_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS siv_name,
s.“SPENT_DATE” AS siv_date,
rho tawm (xyoo los ntawm "SPENT_DATE") AS xyoo,
rho tawm (hli los ntawm "SPENT_DATE") raws li lub hli,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
Los ntawm wdata w Inner JOIN siv s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN siv_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
Qhov twg
s.“SPENT_DATE” >= '2018-07-01' THIAB s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
Los ntawm wdata w2 INner JOIN siv s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
PIB HAIV NEEG wdata w
ON w2.“LRM” = w.“LRM” );
Lub sijhawm npaj: 2.486 ms
Lub Sijhawm Ua Haujlwm: 1223680.326 ms
Yog li, thawj qhov tshwm sim.
Yog: 6 ms (yuav luag 985 teev).
Ua: 1 223 680.326 ms (tsuas yog dhau 20 feeb).
Cov txiaj ntsig zoo. Hauv txoj ntsiab cai, dua, peb tuaj yeem nres qhov ntawd. Tab sis nws yog heev uninteresting, koj yuav tsum tsis txhob.
RAU
Kauj Ruam XNUMX - tshem tawm cov kev sib txheeb subquery
Hloov cov ntawv thov:
Tsis muaj kev sib tham subqueryXaiv
p.“PARAMETER_ID” as 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 qis_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS siv_name,
s.“SPENT_DATE” AS siv_date,
rho tawm (xyoo los ntawm "SPENT_DATE") AS xyoo,
rho tawm (hli los ntawm "SPENT_DATE") raws li lub hli,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
Los ntawm wdata w Inner JOIN siv s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN siv_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
COV PHOOJ YWG HAUJ LWM (xaiv w2.“LRM”, MAX(s2.“SPENT_DATE”)
Los ntawm siv s2 sab hauv koom wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP BY w2.“LRM”
) md on w.“LRM” = md.“LRM”
Qhov twg
s."SPENT_DATE" >= '2018-07-01' THIAB s.SPENT_DATE" <= '2018-09-30';
Lub sijhawm npaj: 2.291 ms
Lub Sijhawm Ua Haujlwm: 165021.870 ms
Yog: 1 223 680.326 ms (tsuas yog dhau 20 feeb).
Ua: 165 021.870 ms (tsuas yog dhau 2 feeb).
Qhov no twb zoo heev.
Txawm li cas los xij, raws li British hais tias "Tab sis, muaj ib txwm muaj tab sis" Qhov tshwm sim uas zoo dhau lawm yuav tsum tau ua rau muaj kev xav tsis thoob. Ib yam dab tsi tsis ncaj ncees lawm ntawm no.
Lub hypothesis txog kev kho cov lus nug kom tshem tawm cov kev sib raug zoo subquery yog qhov tseeb. Tab sis koj yuav tsum tweak nws me ntsis rau qhov kawg tshwm sim kom raug.
Raws li qhov tshwm sim, thawj qhov tshwm sim nruab nrab:
Edited query tsis muaj correlated subqueryXaiv
p.“PARAMETER_ID” as 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 qis_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS siv_name,
s.“SPENT_DATE” AS siv_date,
rho tawm (xyoo los ntawm s.“SPENT_DATE”) AS xyoo,
rho tawm (hli los ntawm s.“SPENT_DATE”) raws li lub hli,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
Los ntawm wdata w Inner JOIN siv s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN siv_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN ( xaiv w2.“LRM”, MAX(s2.“SPENT_DATE”) AS “SPENT_DATE”
Los ntawm siv s2 sab hauv koom wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP BY w2.“LRM”
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” THIAB md.“LRM” = w.“LRM”
Qhov twg
s."SPENT_DATE" >= '2018-07-01' THIAB s.SPENT_DATE" <= '2018-09-30';
Lub sijhawm npaj: 3.192 ms
Lub Sijhawm Ua Haujlwm: 208014.134 ms
Yog li, dab tsi peb xaus nrog yog thawj qhov pom zoo, uas tsis yog qhov txaj muag los qhia rau cov neeg siv khoom:
Pib nrog: 8 222 351.640 ms (ntau dua 2 teev)
Peb tswj kom ua tiav: 1 ms (ntau tshaj 223 feeb).
Cov txiaj ntsig (interim): 208 014.134 ms (tsuas yog dhau 3 feeb).
Cov txiaj ntsig zoo heev.
Qhov no
Peb tuaj yeem nres qhov ntawd.
TAB SIS…
Qab los noj mov. Tus uas taug kev yuav txawj taug txoj kev. Txhua yam tshwm sim yog nruab nrab. Nres thiab tuag. Lwm yam
Wb txuas ntxiv optimization.
tswv yim zoo. Tshwj xeeb tshaj yog xav tias tus neeg siv khoom tsis txawm xav. Thiab txawm muaj zog heev rau nws.
Yog li ntawd, nws yog lub sij hawm rau ib tug database redesign. Cov qauv lus nug nws tus kheej tsis tuaj yeem ua kom zoo dua (txawm hais tias, raws li nws muab tawm tom qab, muaj kev xaiv los xyuas kom meej tias txhua yam ua tsis tiav). Tab sis pib optimizing thiab tsim cov database tsim yog twb yog ib lub tswv yim zoo heev. Thiab tseem ceeb tshaj plaws nthuav. Ntxiv dua thiab, nco ntsoov koj cov hluas. Kuv tsis tau dhau los ua DBA tam sim ntawd, Kuv loj hlob los ua tus programmer (BASIC, assembler, C, double-plus C, Oracle, plsql). Ib lub ntsiab lus nthuav, ntawm chav kawm, rau ib qho memoir cais ;-).
Txawm li cas los xij, cia peb tsis txhob cuam tshuam.
Thiab yog li ntawd,
Los yog tej zaum kev faib yuav pab tau peb?
Spoiler - "Yog, nws tau pab, suav nrog hauv kev ua kom zoo dua."
Tab sis qhov ntawd yog ib zaj dab neeg sib txawv kiag li ...
Yuav tsum tau txuas ntxiv…
Tau qhov twg los: www.hab.com