A bheil cuimhne agad mar a thòisich e uile. Bha a h-uile dad airson a’ chiad uair agus a-rithist

Mu mar a bha agam ri dèiligeadh ri optimization ceist PostgreSQL agus dè a thàinig a-mach à seo uile.
Carson a dh'fheumadh tu? Bha, oir anns na 4 bliadhna roimhe sin dh’ obraich a h-uile càil gu sàmhach, gu socair, mar a bha gleoc a’ tic.
mar epigraph.

Stèidhichte air tachartasan fìor.
Tha na h-ainmean uile air an atharrachadh, tha co-thursan air thuaiream.

Nuair a thèid toradh sònraichte a choileanadh, tha e an-còmhnaidh inntinneach cuimhneachadh dè a bha na bhrosnachadh airson an toiseach, mar a thòisich e uile.

Mar sin, tha na thachair mar thoradh air a mhìneachadh gu h-aithghearr san artaigil “Synthesis mar aon de na dòighean gus coileanadh PostgreSQL a leasachadh".

Is dòcha gum bi e inntinneach an sreath de thachartasan roimhe ath-chruthachadh.
Chùm an eachdraidh an dearbh cheann-latha tòiseachaidh - 2018-09-10 18:02:48.
Cuideachd, anns an sgeulachd tha iarrtas bhon a thòisich e uile:
Iarrtas duilgheadasSELECT
p. "PARAMETER_ID" mar pharameter_id,
d. "PD_NAME" AS pd_name,
d. "CUSTOMER_PARTNUMBER" AS neach-ceannach_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 caithte_ceann-latha,
earrann (bliadhna bho "SPENT_DATE") AS bliadhna,
earrann (mìos bho "SPENT_DATE") mar mhìos,
s."REPORT_NAME" AS aithisg_ainm,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
Bho wdataw,
chaith s,
pmtrp,
air a chosg_pdsp,
p dd
Càit a bheil."SPENT_ID" = w."SPENT_ID"
AGUS p."PARAMETER_ID" = w. "PARAMETER_ID"
AND s."SPENT_ID" = sp. "SPENT_ID"
AGUS dd." PD_ID" = sp. "PD_ID"
AND s."SPENT_DATE" > = '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30'
agus s."SPENT_DATE" = (SELECT MAX(s2. "SPENT_DATE")
BHO chosg s2,
dàta w2
Càit a bheil s2."SPENT_ID" = w2."SPENT_ID"
AGUS w2."LRM" = w."LRM");

Tuairisgeul air an duilgheadas, àbhaisteach àbhaisteach - “Tha a h-uile dad dona. Inns dhomh dè an duilgheadas a th’ ann."
Chuimhnich mi sa bhad air fealla-dhà bho amannan sgrìoban 3 gu leth òirleach:

Thig an lamer chun an neach-tarraing.
- Chan eil dad ag obair dhòmhsa, innis dhomh far a bheil an duilgheadas.
- Ann an DNA...

Ach, gu dearbh, chan e seo an dòigh air fuasgladh fhaighinn air tachartasan coileanaidh. “Is dòcha nach eil sinn air ar tuigsinn" (le). Feum air faighinn a-mach.
Uill, leig dhuinn cladhach. Is dòcha gun cruinnich sin mar thoradh air.

thòisich tasgadh

Mar sin, dè a chìthear sa bhad leis an t-sùil rùisgte, gun eadhon a dhol gu cuideachadh bho EXPLAIN.
1) Chan eil JOINs air an cleachdadh. Tha seo dona, gu sònraichte ma tha an àireamh de cheanglaichean nas motha na aon.
2) Ach dè tha eadhon nas miosa - subquery co-cheangailte, a bharrachd, le cruinneachadh. Tha seo gu math dona.
Tha seo dona, gu dearbh. Ach chan eil seo ach air an aon làimh. Air an làimh eile, tha seo fìor mhath, oir tha e soilleir gu bheil fuasgladh aig an duilgheadas agus faodar an t-iarrtas a leasachadh.
Na tèid chun an fhortain (C).
Chan eil am plana ceist cho iom-fhillte, ach gu math taisbeanach:
Plana cur gu bàs

An fheadhainn as inntinniche agus as fheumaile, mar as àbhaist, aig toiseach is deireadh.
Lùb Neadaichte (cosgais = 935.84..479763226.18 sreathan = 3322 leud = 135) (an fhìor ùine = 31.536..8220420.295 sreathan = 8111656 lùban = 1)
Ùine dealbhaidh: 3.807ms
Ùine cur gu bàs: 8222351.640ms
Tha an ùine cur gu bàs nas fhaide na 2 uair a thìde.

Beachdan meallta a thug ùine

Beachd-bheachd 1- Tha an optimizer ceàrr, togaidh e am plana ceàrr.

Gus am plana cur gu bàs fhaicinn, cleachdaidh sinn an làrach https://explain.depesz.com/. Ach, cha do sheall an làrach rud sam bith inntinneach no feumail. Aig a 'chiad agus an dàrna sealladh - chan eil dad a dh' fhaodadh cuideachadh dha-rìribh. Mura h-eil - chan eil an làn sgan ach glè bheag. Siuthad.

Hypothesis 2-Buaidh air a 'bhunait bho thaobh an autovacuum, feumaidh tu faighinn cuidhteas na breicichean.

Ach, bidh na daemons autovacuum gan giùlan fhèin gu math, chan eil pròiseasan crochte fada ann. eallach trom sam bith - chan eil. Feum air rudeigin eile a lorg.

Tha beachd-bharail 3-Staitistig seann-fhasanta, feumaidh tu a h-uile càil ath-àireamhachadh

A-rithist, chan e sin. Tha na staitistigean ùraichte. Chan eil sin, leis an dìth dhuilgheadasan le autovacuum, na iongnadh.

A 'tòiseachadh a' dèanamh an fheum as fheàrr

Chan eil am prìomh chlàr ‘wdata’ gu cinnteach beag, faisg air 3 millean clàr.
Agus is ann air a’ bhòrd seo a thèid Full Scan.

Hash Cond: ((w."SPENT_ID" = s. "SPENT_ID") AGUS ((SubPlan 1) = s. "SPENT_DATE"))
-> Seq Scan air wdata w (cosgais = 0.00..574151.49 sreathan = 26886249 leud = 46) (an fhìor ùine = 0.005..8153.565 sreathan = 26873950 lùban = 1)
Bidh sinn ag obair mar ìre àbhaisteach: “dèanamaid clàr-amais agus bidh a h-uile càil ag itealaich”.
Rinn sinn clàr-amais air an raon "SPENT_ID"
Mar thoradh air:
Ceist plana cur an gnìomh a’ cleachdadh clàr-amais

Uill, an do chuidich e?
Bha: 8 222 351.640 ms (beagan a bharrachd air 2 uair a thìde)
Thàinig e gu bhith: 6 985 431.575 ms (faisg air 2 uair)
San fharsaingeachd, na h-aon ùbhlan, sealladh taobh.
Cuimhnichidh sinn na clasaichean clasaigeach:
“A bheil an aon fhear agad, ach gun sgiathan? Bidh ga shireadh".

Ann am prionnsabal, dh'fhaodadh seo a bhith air ainmeachadh mar thoradh math, uill, chan eil e math, ach iomchaidh. Aig a’ char as lugha, thoir seachad aithisg mhòr don neach-ceannach ag innse na chaidh a dhèanamh agus carson a tha na chaidh a dhèanamh math.
Ach, tha an co-dhùnadh deireannach fhathast fada air falbh. Gu math fada.

Agus a-nis an rud as inntinniche - bidh sinn a’ leantainn air adhart a’ dèanamh an fheum as fheàrr, nì sinn snas air a’ cheist

Ceum a h-aon - cleachd JOIN

Ceist ath-sgrìobhaidh, tha coltas mar seo a-nis (gu math co-dhiù nas bòidhche):
Ceist a’ cleachdadh JOINSELECT
p. "PARAMETER_ID" mar pharameter_id,
d. "PD_NAME" AS pd_name,
d. "CUSTOMER_PARTNUMBER" AS neach-ceannach_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 caithte_ceann-latha,
earrann (bliadhna bho "SPENT_DATE") AS bliadhna,
earrann (mìos bho "SPENT_DATE") mar mhìos,
s."REPORT_NAME" AS aithisg_ainm,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
BHO wdata w INNER JOIN air a chosg s ON w."SPENT_ID" = s. "SPENT_ID"
Thig còmhla ri INNER pmtr p AIR p."PARAMETER_ID" = w. "PARAMETER_ID"
Thig còmhla ri INNER spent_pd sp ON s."SPENT_ID" = sp. "SPENT_ID"
Thig còmhla ri INNER pd pd AIR Pd."PD_ID" = sp. "PD_ID"
FAR
s."SPENT_DATE" > = '2018-07-01' AGUS s."SPENT_DATE" <= '2018-09-30'AND
s."SPENT_DATE" = (SELECT MAX(s2. "SPENT_DATE")
BHO wdata w2 INNER JOIN air a chosg s2 AIR w2."SPENT_ID" = s2. "SPENT_ID"
INNER JOIN wdata w
ON w2."LRM" = w."LRM" );
Ùine dealbhaidh: 2.486ms
Ùine cur gu bàs: 1223680.326ms

Mar sin seo a’ chiad toradh.
Bha: 6 985 431.575 ms (faisg air 2 uair).
Thàinig e gu bhith: 1 223 680.326 ms (beagan a bharrachd air 20 mionaid).
Toradh math. Ann am prionnsabal, a-rithist, bhiodh e comasach stad an sin. Ach cho neo-inntinneach, chan urrainn dhut stad.
AIRSON

Ceum a Dhà - Faigh cuidhteas an co-cheangailte subquery

Teacs an iarrtais air atharrachadh:
Gun fho-sgrìobhadh co-cheangailteSELECT
p. "PARAMETER_ID" mar pharameter_id,
d. "PD_NAME" AS pd_name,
d. "CUSTOMER_PARTNUMBER" AS neach-ceannach_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 caithte_ceann-latha,
earrann (bliadhna bho "SPENT_DATE") AS bliadhna,
earrann (mìos bho "SPENT_DATE") mar mhìos,
s."REPORT_NAME" AS aithisg_ainm,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
BHO wdata w INNER JOIN air a chosg s ON s."SPENT_ID" = w."SPENT_ID"
Thig còmhla ri INNER pmtr p AIR p."PARAMETER_ID" = w. "PARAMETER_ID"
Thig còmhla ri INNER spent_pd sp ON s."SPENT_ID" = sp. "SPENT_ID"
Thig còmhla ri INNER pd pd AIR Pd."PD_ID" = sp. "PD_ID"
JOIN INNER (SELECT w2."LRM", MAX(s2."SPENT_DATE")
BHO air a chosg s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2. "SPENT_ID"
GRÙP LE w2.LRM
) md on w." LRM " = md. "LRM"
FAR
s."SPENT_DATE" > = '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Ùine dealbhaidh: 2.291ms
Ùine cur gu bàs: 165021.870ms

Bha: 1 223 680.326 ms (beagan a bharrachd air 20 mionaid).
Thàinig e gu bhith: 165 021.870 ms (beagan a bharrachd air 2 mhionaid).
Tha seo gu math mar-thà.
Ach, mar a tha na Sasannaich ag ràdh,Ach, tha an-còmhnaidh ach". Bu chòir toradh ro mhath amharas a thogail gu fèin-ghluasadach. Tha rudeigin ceàrr an seo.

Tha am beachd mu bhith a’ ceartachadh na ceiste gus faighinn cuidhteas an fho-cheist co-cheangailte ceart. Ach feumaidh e beagan tweaking gus an toradh deireannach fhaighinn ceart.
Mar thoradh air an sin, a 'chiad toradh eadar-mheadhanach:
Ceist deasaichte às aonais fo-iarrtas co-cheangailteSELECT
p. "PARAMETER_ID" mar pharameter_id,
d. "PD_NAME" AS pd_name,
d. "CUSTOMER_PARTNUMBER" AS neach-ceannach_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 caithte_ceann-latha,
earrann (bliadhna bho s. "SPENT_DATE") AS bliadhna,
earrann (mìos bho s. "SPENT_DATE") mar mhìos,
s."REPORT_NAME" AS aithisg_ainm,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
BHO wdata w INNER JOIN air a chosg s ON s."SPENT_ID" = w."SPENT_ID"
Thig còmhla ri INNER pmtr p AIR p."PARAMETER_ID" = w. "PARAMETER_ID"
Thig còmhla ri INNER spent_pd sp ON s."SPENT_ID" = sp. "SPENT_ID"
Thig còmhla ri INNER pd pd AIR Pd."PD_ID" = sp. "PD_ID"
JOIN INNER (SELECT w2."LRM", MAX(s2."SPENT_DATE") MAR "SPENT_DATE"
BHO air a chosg s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2. "SPENT_ID"
GRÙP LE w2.LRM
) md AIR md."SPENT_DATE" = s."SPENT_DATE" AGUS md."LRM" = w."LRM"
FAR
s."SPENT_DATE" > = '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Ùine dealbhaidh: 3.192ms
Ùine cur gu bàs: 208014.134ms

Mar sin, is e an rud a th’ againn mar thoradh air a’ chiad toradh iomchaidh, nach eil nàire oirnn a shealltainn don neach-ceannach:
Thòisich le: 8 222 351.640 ms (barrachd air 2 uair)
Air a choileanadh: 1 ms (beagan a bharrachd air 223 mionaid).
Toradh (meadhanach): 208 014.134 ms (beagan a bharrachd air 3 mionaidean).

Toradh sàr-mhath.

An toradh

Dh’ fhaodadh seo a bhith air stad.
ACH…
Bidh blasad a’ tighinn le ithe. Thèid an rathad a mhaighstir le coiseachd. Tha toradh sam bith eadar-mheadhanach. Stad marbh. Etc.
Leanaidh sinn oirnn leis an optimization.
Deagh bheachd. Gu sònraichte leis nach robh an neach-ceannach eadhon na aghaidh. Agus eadhon gu làidir - airson.

Mar sin, tha an t-àm ann an stòr-dàta ath-dhealbhadh. Chan urrainnear structar na ceiste fhèin a mheudachadh tuilleadh (ged, mar a thàinig e a-mach nas fhaide air adhart, tha roghainn ann airson a h-uile dad itealaich). Ach a-nis gus dealbhadh an stòr-dàta a bharrachadh agus a leasachadh, tha seo mar-thà na bheachd gealltanach. Agus as cudromaiche buileach inntinneach. A-rithist, cuimhnich air òigridh. Às deidh na h-uile, cha deach mi gu bhith na DBA sa bhad, dh'fhàs mi a-mach à prògramadairean (bunaiteach, assembler, si, si dà uair plused, oracle, plsql). Cuspair inntinneach, gu dearbh, airson cuimhneachain air leth ;-).
Ge-tà, na leig leinn digress.

Agus mar sin,

Agus is dòcha gun cuidich roinneadh sinn?
Spoiler - "Tha, chuidich e, agus ann a bhith ag àrdachadh coileanadh, a 'gabhail a-steach."

Ach is e sgeulachd gu tur eadar-dhealaichte a tha sin ...

Ri leantainn…

Source: www.habr.com