Vai atceries, kā tas viss sākās. Viss bija pirmo reizi un atkal

Par to, kā man bija jāoptimizē PostgreSQL vaicājums un kas no tā visa iznāca.
Kāpēc tev vajadzēja? Jā, jo iepriekšējos 4 gadus viss strādāja klusi, mierīgi, kā pulkstenis tikšķēja.
Kā epigrāfs.

Balstīts uz patiesiem notikumiem.
Visi nosaukumi ir mainīti, sakritības ir nejaušas.

Sasniedzot noteiktu rezultātu, vienmēr ir interesanti atcerēties, kas bija stimuls sākumam, kur tas viss sākās.

Tātad, kas notika rezultātā, ir īsi aprakstīts rakstā "Sintēze kā viena no metodēm PostgreSQL veiktspējas uzlabošanai'.

Droši vien būs interesanti atjaunot iepriekšējo notikumu ķēdi.
Vēsture saglabāja precīzu sākuma datumu - 2018-09-10 18:02:48.
Arī stāstā ir lūgums, no kura viss sākās:
Problēmas pieprasījumsSELECT
p.“PARAMETER_ID” kā parametra_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS klienta_daļas numurs,
ar “LRM” AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_vērtība,
w.“LOWER_SPEC_LIMIT” AS zemāks_spec_limits,
w.“UPPER_SPEC_LIMIT” AS augšējais_spec_limits,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS iztērētais_nosaukums,
s.“SPENT_DATE” AS iztērēts_datums,
izraksts(gads no "SPENT_DATE") AS gads,
izraksts (mēnesis no "SPENT_DATE") kā mēnesis,
s."REPORT_NAME" AS pārskata_nosaukums,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS klienta parametra_nosaukums
NO wdata w,
pavadīja s,
pmtr p,
pavadīja_pd sp,
pd pd
WHERE s.“IZLĒDTAIS_ID” = w.“IZLĒDTS_ID”
UN p."PARAMETER_ID" = w."PARAMETER_ID"
UN s.“IZLĒDTS_ID” = sp.“IZDOTĀ_ID”
UN pd."PD_ID" = sp."PD_ID"
UN s.“SPENT_DATE” >= '2018-07-01' UN s.“SPENT_DATE” <= '2018-09-30'
un s.“SPENT_DATE” = (ATLASĪT MAX(s2.“SPENT_DATE”)
NO iztērētā s2,
wdata w2
WHERE s2.“IZLĒTOTĀS_ID” = w2.“IZLĒTOTAS_ID”
UN w2.“LRM” = w.“LRM”);

Problēmas apraksts ir paredzami standarta - “Viss ir slikti. Pastāsti man, kāda ir problēma."
Uzreiz atcerējos anekdoti no 3 ar pusi collu piedziņas laikiem:

Lamer nāk pie hakera.
-Man nekas nelīdz, pasaki kur ir problēma.
- DNS...

Bet, protams, tas nav veids, kā atrisināt darbības traucējumus. "Viņi var mūs nesaprast" (Ar). Mums tas ir jāizdomā.
Nu raksim. Varbūt rezultātā kaut kas sakrāsies.

Sākta izmeklēšana

Tātad, ko var redzēt uzreiz ar neapbruņotu aci, pat neķeroties pie IZSKAIDROT.
1) JOIN netiek izmantoti. Tas ir slikti, it īpaši, ja savienojumu skaits ir vairāk nekā viens.
2) Bet vēl sliktāk ir saistīti apakšvaicājumi, turklāt ar apkopošanu. Tas ir ļoti slikti.
Tas, protams, ir slikti. Bet tas ir tikai no vienas puses. No otras puses, tas ir ļoti labi, jo problēmai viennozīmīgi ir risinājums un lūgums, ko var uzlabot.
Neej pie zīlnieces (C).
Vaicājumu plāns nav tik sarežģīts, taču tas ir diezgan orientējošs:
Izpildes plāns

Interesantākais un noderīgākais, kā parasti, ir sākumā un beigās.
Ligzdota cilpa (maksa = 935.84..479763226.18 rindas = 3322 platums = 135) (faktiskais laiks = 31.536..8220420.295 rindas = 8111656 cilpas = 1)
Plānošanas laiks: 3.807 ms
Izpildes laiks: 8222351.640 ms
Izpildes laiks ir vairāk nekā 2 stundas.

Nepatiesas hipotēzes, kas prasīja laiku

1. hipotēze – optimizētājs pieļauj kļūdu un izveido nepareizu plānu.

Lai vizualizētu izpildes plānu, mēs izmantosim vietni https://explain.depesz.com/. Tomēr vietne neko interesantu un noderīgu neparādīja. No pirmā un otrā acu uzmetiena nekas īsti nevarētu palīdzēt. Vai ir iespējams, ka pilna skenēšana ir minimāla. Uz priekšu.

2. hipotēze-Ietekme uz pamatni no autovakuuma puses, jāatbrīvojas no bremzēm.

Bet autovakuuma dēmoni uzvedas labi, nav ilgstošu procesu. Nav nopietnas slodzes. Mums jāmeklē kaut kas cits.

3. hipotēze - Statistika ir novecojusi, viss ir jāpārrēķina

Atkal, ne tas. Statistika ir atjaunināta. Kas, ņemot vērā problēmu trūkumu ar autovakuumu, nav pārsteidzoši.

Sāksim optimizēt

Galvenā tabula 'wdata' noteikti nav maza, gandrīz 3 miljoni ierakstu.
Un tieši šai tabulai seko Full Scan.

Jauktais nosacījums: ((w."SPENT_ID" = s."SPENT_ID") UN ((1. apakšplāns) = s."SPENT_DATE"))
-> Seq Scan uz wdata w (maksa = 0.00..574151.49 rindas = 26886249 platums = 46) (faktiskais laiks = 0.005..8153.565 rindas = 26873950 cilpas = 1)
Mēs darām standarta lietu: "Nāc, izveidosim indeksu, un viss lidos."
Laukā “SPENT_ID” tika izveidots rādītājs
Rezultātā:
Vaicājuma izpildes plāns, izmantojot indeksu

Nu, vai tas palīdzēja?
Bija: 8 222 351.640 ms (nedaudz vairāk par 2 stundām)
Kļuva: 6 985 431.575 ms (gandrīz 2 stundas)
Vispār tie paši āboli, skats no malas.
Atcerēsimies klasiku:
“Vai jums ir tāds pats, bet bez spārniem? Meklēsies".

Principā to varētu saukt par labu rezultātu, nu, ne labu, bet pieņemamu. Sniedziet klientam vismaz lielu pārskatu, kurā aprakstīts, cik daudz ir paveikts un kāpēc paveiktais bija labs.
Tomēr galīgais lēmums vēl ir tālu. Ļoti tālu.

Un tagad pats interesantākais - turpinām optimizēt, pieslīpēsim pieprasījumu

Pirmais solis — izmantojiet JOIN

Pārrakstītais pieprasījums tagad izskatās šādi (nu vismaz tā ir skaistāka):
Pieprasiet, izmantojot JOINSELECT
p.“PARAMETER_ID” kā parametra_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS klienta_daļas numurs,
ar “LRM” AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_vērtība,
w.“LOWER_SPEC_LIMIT” AS zemāks_spec_limits,
w.“UPPER_SPEC_LIMIT” AS augšējais_spec_limits,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS iztērētais_nosaukums,
s.“SPENT_DATE” AS iztērēts_datums,
izraksts(gads no "SPENT_DATE") AS gads,
izraksts (mēnesis no "SPENT_DATE") kā mēnesis,
s."REPORT_NAME" AS pārskata_nosaukums,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS klienta parametra_nosaukums
NO wdata w INNER JOIN iztērēts s ON w.“SPENT_ID”=s.”“SPENT_ID”
IEKŠĒJĀ PIEVIENOŠANĀS pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
IEKŠĒJĀ PIEVIENOŠANĀS pavadīja_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
IEKŠĒJĀ JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
KUR
s.“SPENT_DATE” >= '2018-07-01' UN s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (ATLASĪT MAX(s2.“SPENT_DATE”)
NO wdata w2 IEKŠĒJĀ JOIN iztērēts s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
IESLĒGTS w2.“LRM” = w.“LRM” );
Plānošanas laiks: 2.486 ms
Izpildes laiks: 1223680.326 ms

Tātad, pirmais rezultāts.
Bija: 6 985 431.575 ms (gandrīz 2 stundas).
Kļuva: 1 223 680.326 ms (nedaudz vairāk nekā 20 minūtes).
Labs rezultāts. Principā atkal varētu pie tā apstāties. Bet tas ir tik neinteresanti, ka jūs nevarat apstāties.
PRIEKŠ

Otrais solis – atbrīvojieties no korelētā apakšvaicājuma

Mainīts pieprasījuma teksts:
Bez korelēta apakšvaicājumaSELECT
p.“PARAMETER_ID” kā parametra_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS klienta_daļas numurs,
ar “LRM” AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_vērtība,
w.“LOWER_SPEC_LIMIT” AS zemāks_spec_limits,
w.“UPPER_SPEC_LIMIT” AS augšējais_spec_limits,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS iztērētais_nosaukums,
s.“SPENT_DATE” AS iztērēts_datums,
izraksts(gads no "SPENT_DATE") AS gads,
izraksts (mēnesis no "SPENT_DATE") kā mēnesis,
s."REPORT_NAME" AS pārskata_nosaukums,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS klienta parametra_nosaukums
NO wdata w IEKŠĒJS JOIN iztērēts s UZ s.“SPENT_ID” = w.“SPENT_ID”
IEKŠĒJĀ PIEVIENOŠANĀS pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
IEKŠĒJĀ PIEVIENOŠANĀS pavadīja_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
IEKŠĒJĀ JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
IEKŠĒJĀ PIEVIENOŠANĀS (ATLASĪT w2.“LRM”, MAX(s2.“SPENT_DATE”)
NO iztērētā s2 IEKŠĒJĀ PIEVIENOŠANĀS wdata w2 ON s2.“IZPĒRTĒTAS_ID” = w2.“IZPĒRTĒTAS_ID”
GROUP BY w2.“LRM”
) md uz w.“LRM” = md.“LRM”
KUR
s."SPENT_DATE" >= '2018-07-01' UN s."SPENT_DATE" <= '2018-09-30';
Plānošanas laiks: 2.291 ms
Izpildes laiks: 165021.870 ms

Bija: 1 223 680.326 ms (nedaudz vairāk nekā 20 minūtes).
Kļuva: 165 021.870 ms (nedaudz vairāk nekā 2 minūtes).
Šis jau ir diezgan labs.
Tomēr, kā saka briti "Bet vienmēr ir bet" Pārāk labam rezultātam automātiski vajadzētu izraisīt aizdomas. Šeit kaut kas nav kārtībā.

Hipotēze par vaicājuma labošanu, lai atbrīvotos no korelētā apakšvaicājuma, ir pareiza. Bet jums tas ir nedaudz jāpielāgo, lai gala rezultāts būtu pareizs.
Rezultātā pirmais starprezultāts:
Rediģēts vaicājums bez korelēta apakšvaicājumaSELECT
p.“PARAMETER_ID” kā parametra_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS klienta_daļas numurs,
ar “LRM” AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_vērtība,
w.“LOWER_SPEC_LIMIT” AS zemāks_spec_limits,
w.“UPPER_SPEC_LIMIT” AS augšējais_spec_limits,
p"TYPE_CALCUL" AS type_calcul,
s"SPENT_NAME" AS iztērētais_nosaukums,
s.“SPENT_DATE” AS iztērēts_datums,
izraksts(gads no s.“SPENT_DATE”) AS gads,
izraksts (mēnesis no s.“SPENT_DATE”) kā mēnesis,
s."REPORT_NAME" AS pārskata_nosaukums,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS klienta parametra_nosaukums
NO wdata w IEKŠĒJS JOIN iztērēts s UZ s.“SPENT_ID” = w.“SPENT_ID”
IEKŠĒJĀ PIEVIENOŠANĀS pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
IEKŠĒJĀ PIEVIENOŠANĀS pavadīja_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
IEKŠĒJĀ JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
IEKŠĒJĀ PIEVIENOŠANĀS ( ATLASĪT w2.“LRM”, MAX(s2.“SPENT_DATE”) KĀ “SPENT_DATE”
NO iztērētā s2 IEKŠĒJĀ PIEVIENOŠANĀS wdata w2 ON s2.“IZPĒRTĒTAS_ID” = w2.“IZPĒRTĒTAS_ID”
GROUP BY w2.“LRM”
) md ON md.“IZLĒDZIS_DATUMS” = s.“IZLĒDZIS_DATUMS” UN md.“LRM” = w.“LRM”
KUR
s."SPENT_DATE" >= '2018-07-01' UN s."SPENT_DATE" <= '2018-09-30';
Plānošanas laiks: 3.192 ms
Izpildes laiks: 208014.134 ms

Tātad, tas, ko mēs galu galā iegūstam, ir pirmais pieņemamais rezultāts, ko nav kauns parādīt klientam:
Sākās ar: 8 222 351.640 ms (vairāk nekā 2 stundas)
Mums izdevās sasniegt: 1 223 680.326, 20 ms (nedaudz vairāk par XNUMX minūtēm).
Rezultāts (starpposms): 208 014.134 ms (nedaudz vairāk nekā 3 minūtes).

Izcils rezultāts.

Kopsavilkums

Mēs varējām tur apstāties.
BET…
Apetīte rodas ēdot. Tas, kurš iet, pārvaldīs ceļu. Jebkurš rezultāts ir starpposms. Apstājās un nomira. utt.
Turpināsim optimizāciju.
Lieliska ideja. Īpaši ņemot vērā, ka klients pat neiebilda. Un pat stipri par to.

Tātad, ir pienācis laiks pārveidot datubāzi. Pati vaicājuma struktūru vairs nevar optimizēt (lai gan, kā vēlāk izrādījās, ir iespēja nodrošināt, ka viss tiešām neizdodas). Bet sākt optimizēt un attīstīt datu bāzes dizainu jau ir ļoti daudzsološa ideja. Un pats galvenais interesanti. Atkal atcerieties savu jaunību. Galu galā es ne uzreiz kļuvu par DBA, es uzaugu kā programmētājs (BASIC, montētājs, C, dubultā plus C, Oracle, plsql). Interesanta tēma, protams, atsevišķam memuāram ;-).
Tomēr nenovērsīsim uzmanību.

Tātad,

Vai varbūt sadalīšana mums palīdzēs?
Spoileris — "Jā, tas palīdzēja, tostarp optimizējot veiktspēju."

Bet tas ir pavisam cits stāsts...

Turpinājums sekos…

Avots: www.habr.com