Mahitungod sa kung giunsa nako pag-atubang ang PostgreSQL nga pag-optimize sa pangutana ug kung unsa ang migawas sa tanan niini.
Nganong kinahanglan nimo? Oo, tungod kay sa miaging 4 ka tuig ang tanan nagtrabaho sa hilom, kalmado, sama sa usa ka orasan.
isip usa ka epigraph.
Base sa tinuod nga mga panghitabo.
Ang tanan nga mga ngalan giusab, ang mga sulagma random.
Kung nakab-ot ang usa ka sangputanan, kanunay nga makapaikag nga hinumdoman kung unsa ang hinungdan sa sinugdanan, kung giunsa kini nagsugod.
Mao nga, kung unsa ang nahitabo ingon usa ka sangputanan gihulagway sa mubo sa artikulo nga "".
Mahimong makapaikag nga himuon pag-usab ang kadena sa nangaging mga panghitabo.
Gitago sa kasaysayan ang eksaktong petsa sa pagsugod β 2018-09-10 18:02:48.
Usab, sa istorya adunay usa ka hangyo diin nagsugod ang tanan:
Pangayo sa problemaPinili nga
p. "PARAMETER_ID" isip 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,
kinuha(tuig gikan sa "SPENT_DATE") AS tuig,
kinuha(bulan gikan sa "SPENT_DATE") isip bulan,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
GIKAN sa wdataw,
gigasto s,
pmtrp,
nagasto_pdsp,
pd pd
WHERE s."SPENT_ID" = w."SPENT_ID"
UG p."PARAMETER_ID" = w."PARAMETER_ID"
UG s."SPENT_ID" = sp."SPENT_ID"
UG pd. "PD_ID" = sp. "PD_ID"
UG s."SPENT_DATE" >= '2018-07-01' UG s."SPENT_DATE" <= '2018-09-30'
ug s."SPENT_DATE" = (PILI MAX(s2."SPENT_DATE")
GIKAN sa gigasto sa s2,
wdata w2
DIIN s2."SPENT_ID" = w2."SPENT_ID"
UG w2."LRM" = w."LRM");
Deskripsyon sa problema, matag-an nga sukaranan - "Ang tanan daotan. Sultihi ko unsay problema."
Diha-diha dayon akong nahinumduman ang usa ka komedya gikan sa mga panahon sa 3 ug tunga ka pulgada nga pagmaneho:
Ang lamer moabut sa hacker.
- Walaβy molihok alang kanako, isulti kanako kung diin ang problema.
- Sa DNA...
Apan, siyempre, dili kini ang paagi aron masulbad ang mga insidente sa pasundayag. βMahimong dili kita masabtan"(Kauban). Kinahanglan nga mahibal-an kini.
Aw, magkalot ta. Basin magpundo kana isip resulta.
nagsugod ang puhunan
Busa, unsa ang makita diha-diha dayon sa hubo nga mata, nga wala gani modangop sa tabang sa PASABOT.
1) Ang mga JOIN wala gigamit. Kini dili maayo, ilabi na kung ang gidaghanon sa mga koneksyon labaw pa sa usa.
2) Apan unsa ang mas grabe pa - usa ka correlated nga subquery, dugang pa, uban sa aggregation. Kini daotan kaayo.
Kini daotan, siyempre. Apan kini anaa lamang sa usa ka bahin. Sa laing bahin, kini maayo kaayo, tungod kay ang problema klaro nga adunay solusyon ug ang hangyo mahimong mapauswag.
Ayaw pag-adto sa manghuhula (C).
Ang plano sa pangutana dili ingon ka komplikado, apan nagpaila:
Plano sa pagpatuman
Ang labing makapaikag ug mapuslanon, sama sa naandan, sa sinugdanan ug katapusan.
Nested Loop (gasto=935.84..479763226.18 row=3322 width=135) (aktwal nga oras=31.536..8220420.295 row=8111656 loops=1)
Panahon sa pagplano: 3.807ms
Panahon sa pagpatuman: 8222351.640ms
Ang oras sa pagpatuman labaw pa sa 2 ka oras.
Mga bakak nga pangagpas nga nagkinahanglag panahon
Hypothesis 1- Ang optimizer sayop, nagtukod sa sayop nga plano.
Aron mahanduraw ang plano sa pagpatuman, among gamiton ang site . Bisan pa, ang site wala magpakita bisan unsang makapaikag o mapuslanon. Sa una ug ikaduha nga pagtan-aw - walay bisan unsa nga makatabang. Gawas kung - Gamay ra ang Full Scan. Sige na nga.
Hypothesis 2-Epekto sa base gikan sa kilid sa autovacuum, kinahanglan nimo nga tangtangon ang mga preno.
Apan, ang mga daemon sa autovacuum maayo ang paggawi, walaβy dugay nga nagbitay nga mga proseso. Bisan unsang seryoso nga karga - dili. Kinahanglan pangitaon ug lain.
Ang Hypothesis 3-Statistics wala na sa panahon, kinahanglan nimo nga kalkulahon ang tanan nga mga langaw
Pag-usab, dili kana. Ang mga estadistika labing bag-o. Nga, tungod sa kakulang sa mga problema sa autovacuum, dili ikatingala.
Magsugod ta sa pag-optimize
Ang panguna nga lamesa nga 'wdata' siguradong dili gamay, hapit 3 milyon nga mga rekord.
Ug sa kini nga lamesa nga ang Full Scan moadto.
Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") UG ((SubPlan 1) = s."SPENT_DATE"))
-> Seq Scan sa wdata w (gasto=0.00..574151.49 row=26886249 width=46) (aktuwal nga oras=0.005..8153.565 row=26873950 loops=1)
Naglihok kami isip sumbanan: "maghimo kita og indeks ug ang tanan molupad".
Naghimo ug index sa field nga "SPENT_ID"
Ingon usa ka sangputanan:
Pangutana nga plano sa pagpatuman gamit ang indeks
Aw, nakatabang ba kini?
Kaniadto: 8 222 351.640ms (sobra lang sa 2 ka oras)
Kini nahimong: 6 985 431.575 ms (hapit 2 ka oras)
Sa kinatibuk-an, ang parehas nga mga mansanas, pagtan-aw sa kilid.
Atong hinumdoman ang mga klasiko:
βNaa ba kay pareha, pero walay pako? Mangita".
Sa prinsipyo, kini matawag nga usa ka maayong resulta, maayo, dili maayo, apan madawat. Sa labing gamay, paghatag usa ka dako nga taho sa kustomer nga naghulagway kung unsa ka daghan ang nahimo ug ngano nga maayo ang nahimo.
Bisan pa, ang katapusan nga desisyon layo pa. Layo kaayo.
Ug karon ang labing makaiikag nga butang - nagpadayon kami sa pag-optimize, among pasinawon ang pangutana
Unang lakang - gamita ang JOIN
Gisulat pag-usab nga pangutana, karon ingon niini (well at least mas gwapa):
Pangutana gamit ang JOINPinili nga
p. "PARAMETER_ID" isip 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,
kinuha(tuig gikan sa "SPENT_DATE") AS tuig,
kinuha(bulan gikan sa "SPENT_DATE") isip bulan,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
GIKAN sa wdata w INNER JOIN nagasto s SA w."SPENT_ID"=s."SPENT_ID"
INNER JOIN pmtr p SA p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd SA pd."PD_ID" = sp."PD_ID"
SAAN
s."SPENT_DATE" >= '2018-07-01' UG s."SPENT_DATE" <= '2018-09-30'UG
s."SPENT_DATE" = (PILI MAX(s2."SPENT_DATE")
GIKAN sa wdata w2 INNER JOIN migasto s2 SA w2."SPENT_ID"=s2."SPENT_ID"
INNER JOIN wdata w
SA w2."LRM" = w."LRM" );
Panahon sa pagplano: 2.486ms
Panahon sa pagpatuman: 1223680.326ms
Busa ania ang unang resulta.
Kaniadto: 6 985 431.575 ms (hapit 2 ka oras).
Kini nahimong: 1 223 680.326 ms (sobra lang sa 20 minutos).
Maayo nga resulta. Sa prinsipyo, pag-usab, posible nga mohunong didto. Apan dili kaayo makapaikag, dili ka makahunong.
PARA
Ikaduhang Lakang - Kuhaa ang may kalabutan nga subquery
Giusab nga teksto sa hangyo:
Walay correlated nga subqueryPinili nga
p. "PARAMETER_ID" isip 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,
kinuha(tuig gikan sa "SPENT_DATE") AS tuig,
kinuha(bulan gikan sa "SPENT_DATE") isip bulan,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
GIKAN sa wdata w INNER JOIN nagasto s ON s."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p SA p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd SA pd."PD_ID" = sp."PD_ID"
AMBAY SA SULOD (PILI w2."LRM", MAX(s2."SPENT_DATE")
GIKAN sa gigasto s2 INNER AMBAY wdata w2 SA s2."SPENT_ID" = w2."SPENT_ID"
GRUPO NI w2.LRM
) md sa w. "LRM" = md. "LRM"
SAAN
s."SPENT_DATE" >= '2018-07-01' UG s."SPENT_DATE" <= '2018-09-30';
Panahon sa pagplano: 2.291ms
Panahon sa pagpatuman: 165021.870ms
Kaniadto: 1 223 680.326 ms (sobra lang sa 20 minutos).
Kini nahimong: 165 021.870 ms (sobra lang sa 2 minutos).
Maayo na kini.
Apan, ingon sa giingon sa Ingles,Apan, kanunay adunay apan". Ang maayo kaayo nga resulta kinahanglang awtomatikong makapukaw sa pagduda. Adunay dili maayo dinhi.
Husto ang pangagpas sa pagtul-id sa pangutana aron mawala ang may kalabotan nga subquery. Apan kini nagkinahanglan og gamay nga pag-tweak aron makuha ang katapusang resulta sa husto.
Ingon usa ka sangputanan, ang una nga intermediate nga resulta:
Gi-edit nga pangutana nga walay correlated nga subqueryPinili nga
p. "PARAMETER_ID" isip 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,
kinuha(tuig gikan sa s. "SPENT_DATE") AS tuig,
kinuha(bulan gikan sa s. "SPENT_DATE") isip bulan,
s."REPORT_NAME" AS report_name,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
GIKAN sa wdata w INNER JOIN nagasto s ON s."SPENT_ID" = w."SPENT_ID"
INNER JOIN pmtr p SA p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd SA pd."PD_ID" = sp."PD_ID"
AMBAY SA SULOD ( PILI w2."LRM", MAX(s2."SPENT_DATE") AS "SPENT_DATE"
GIKAN sa gigasto s2 INNER AMBAY wdata w2 SA s2."SPENT_ID" = w2."SPENT_ID"
GRUPO NI w2.LRM
) md SA md."SPENT_DATE" = s."SPENT_DATE" UG md."LRM" = w."LRM"
SAAN
s."SPENT_DATE" >= '2018-07-01' UG s."SPENT_DATE" <= '2018-09-30';
Panahon sa pagplano: 3.192ms
Panahon sa pagpatuman: 208014.134ms
Mao nga, kung unsa ang naa kanamo ingon usa ka sangputanan mao ang una nga madawat nga sangputanan, nga dili kami maulaw nga ipakita sa kustomer:
Nagsugod sa: 8 222 351.640 ms (sobra sa 2 ka oras)
Nakab-ot: 1 ms (sobra lang sa 223 minutos).
Resulta (intermediate): 208 014.134 ms (sobra lang sa 3 minutos).
Maayo kaayo nga resulta.
Ang resulta
Mahunong na unta ni.
PEROβ¦
Ang gana moabut uban sa pagkaon. Ang dalan ma-master pinaagi sa paglakaw. Ang bisan unsang resulta kay intermediate. Mihunong patay. ug uban pa.
Magpadayon kita sa pag-optimize.
Nindot nga ideya. Ilabi na nga gikonsiderar nga ang kustomer wala gani supak niini. Ug bisan kusog - kay.
Busa, panahon na sa pagdesinyo pag-usab sa database. Ang istruktura sa hangyo mismo dili na ma-optimize (bisan pa, ingon nga kini nahimo sa ulahi, adunay kapilian alang sa tanan nga molupad gyud). Apan karon aron mahimo ang pag-optimize ug pagpalambo sa disenyo sa database, kini usa na ka maayong ideya. Ug labing importante nga makapaikag. Sa makausa pa, hinumdomi ang kabatan-onan. Tuod man, wala dayon ko nahimong DBA, nagdako ko sa mga programmer (basic, assembler, si, si twice plused, oracle, plsql). Usa ka makapaikag nga hilisgutan, siyempre, alang sa lahi nga mga memoir ;-).
Hinoon, dili ta magpalayo.
Ug busa,
Ug basin ang sectioning makatabang nato?
Spoiler - "Oo, nakatabang kini, ug sa pag-optimize sa performance, lakip na."
Apan kana usa ka lahi nga istorya ...
Ipadayonβ¦
Source: www.habr.com