Ti ricordi cumu tuttu principia. Tuttu era per a prima volta è di novu

Circa cumu aghju avutu à trattà cù l'ottimisazione di query PostgreSQL è ciò chì hè vinutu da tuttu questu.
Perchè avete bisognu? Iè, perchè l'anni 4 precedenti tuttu hà travagliatu in silenziu, tranquillamente, cum'è un clock ticccò.
cum'è un epigrafu.

Basatu nantu à avvenimenti reali.
Tutti i nomi sò stati cambiati, e coincidenze sò casuali.

Quandu un certu risultatu hè ottenutu, hè sempre interessante di ricurdà ciò chì era l'impetu per u principiu, cumu tuttu principia.

Dunque, ciò chì hè accadutu in u risultatu hè brevemente descrittu in l'articulu "Sintesi cum'è unu di i metudi per migliurà u rendiment PostgreSQL».

Probabilmente serà interessante per ricreà a catena di avvenimenti previ.
A storia hà mantinutu a data di partenza esatta - 2018-09-10 18:02:48.
Inoltre, in a storia ci hè una dumanda da quale tuttu principia:
A dumanda di prublemaSELECT
p. "PARAMETER_ID" cum'è 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 low_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,
extract (annu da "SPENT_DATE") AS annu,
extract (mese da "SPENT_DATE") cum'è mese,
s "REPORT_NAME" AS report_name,
p "STPM_NAME" AS stpm_name,
p "CUSTOMERPARAM_NAME" AS customerparam_name
DA wdataw,
spesi s,
pmtrp,
spent_pdsp,
pd pd
WHERE s "SPENT_ID" = w "SPENT_ID"
AND p "PARAMETER_ID" = w "PARAMETER_ID"
AND s.“SPENT_ID” = sp.“SPENT_ID”
AND pd "PD_ID" = sp "PD_ID"
AND s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30'
è s."SPENT_DATE" = (SELECT MAX(s2."SPENT_DATE")
FROM spentu s2,
wdata w2
WHERE s2."SPENT_ID" = w2 "SPENT_ID"
AND w2.“LRM” = w.“LRM”);

Descrizzione di u prublema, previsiblemente standard - "Tuttu hè male. Dimmi quale hè u prublema ".
Aghju ricurdatu subitu un scherzu da i tempi di 3 unità è mezzo inch:

U lamer vene à u pirate.
- Nunda mi travaglia, dimmi induve hè u prublema.
- In DNA...

Ma, sicuru, questu ùn hè micca u modu per risolve incidenti di rendiment. "Ùn pudemu micca esse capitu"(Cù). Avemu bisognu di capisce.
Ebbè, andemu à scavà. Forse chì s'acumulerà in u risultatu.

investimentu cuminciatu

Allora, ciò chì pò esse vistu subitu à l'occhiu nudu, senza mancu ricorrere à l'aiutu di EXPLAIN.
1) I JOIN ùn sò micca usati. Questu hè male, soprattuttu se u numeru di cunnessione hè più di una.
2) Ma ciò chì hè ancu peggiu - una subquery correlata, in più, cù l'agregazione. Questu hè assai male.
Questu hè male di sicuru. Ma questu hè solu da una banda. Per d 'altra banda, questu hè assai bonu, perchè u prublema hà chjaramente una suluzione è una dumanda chì pò esse migliurata.
Ùn andate micca ind’è un indovinu (C).
U pianu di dumanda ùn hè micca cusì complicatu, ma abbastanza indicativu:
Pianu di esecuzione

U più interessante è utile, cum'è solitu, à u principiu è a fine.
Loop nidificatu (costu = 935.84..479763226.18 file = 3322 larghezza = 135) (tempu attuale = 31.536..8220420.295 file = 8111656 loops = 1)
Tempu di pianificazione: 3.807 ms
Tempu di esecuzione: 8222351.640 ms
U tempu di esecuzione hè più di 2 ore.

False ipotesi chì pigghianu tempu

Ipotesi 1- L'ottimisatore hè sbagliatu, custruisce u pianu sbagliatu.

Per visualizà u pianu di esicuzzioni, avemu aduprà u situ https://explain.depesz.com/. Tuttavia, u situ ùn hà micca mostratu nunda interessante o utile. À u primu è secondu sguardu - nunda chì puderia veramente aiutà. A menu chì - Full Scan hè minimu. Avanti.

Ipotesi 2-Impattu nantu à a basa da u latu di l'autovacuum, avete bisognu di ridivà di i freni.

Ma i demoni di l'autovacuum si cumportanu bè, ùn ci sò micca prucessi longu. Nisuna carica seria. Avemu bisognu di circà qualcosa d'altru.

L'ipotesi 3-Statistics hè obsoleta, avete bisognu di recalculate tuttu mosche

Di novu, micca questu. E statistiche sò aghjurnate. Chì, datu a mancanza di prublemi cù l'autovacuum, ùn hè micca surprisante.

Cumincià à ottimisà

A tavula principale 'wdata' hè certamente micca chjucu, quasi 3 milioni di dischi.
È hè nantu à sta tavula chì Full Scan va.

Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") AND ((SubPlan 1) = s."SPENT_DATE"))
-> Seq Scan nantu à wdata w (costu = 0.00..574151.49 file = 26886249 larghezza = 46) (tempu attuale = 0.005..8153.565 file = 26873950 loops = 1)
Facemu a cosa standard: "Venite, facemu un indice è tuttu volarà".
Creatu un indice nantu à u campu "SPENT_ID".
Di cunsiguenza:
Pianu di esecuzione di a dumanda cù l'indice

Ebbè, hà aiutatu?
Era: 8 222 351.640 ms (pocu più di 2 ore)
Hè diventatu: 6 985 431.575 ms (quasi 2 ore)
In generale, i stessi pomi, vista laterale.
Ricurdemu i classici:
"Avete u stessu, ma senza ali? Cercherà".

In principiu, questu puderia esse chjamatu un bonu risultatu, bè, micca bonu, ma accettabile. À u minimu, furnisce un grande rapportu à u cliente chì descrive quantu hè statu fattu è perchè ciò chì hè fattu hè bonu.
Tuttavia, a decisione finale hè sempre luntanu. Assai luntanu.

È avà u più interessante - cuntinuemu à ottimisà, puliscemu a quistione

Passu unu - aduprà JOIN

A dumanda riscritta avà pare cusì (bè almenu più bella):
Query usendu JOINSELECT
p. "PARAMETER_ID" cum'è 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 low_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,
extract (annu da "SPENT_DATE") AS annu,
extract (mese da "SPENT_DATE") cum'è mese,
s "REPORT_NAME" AS report_name,
p "STPM_NAME" AS stpm_name,
p "CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN spentu s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p "PARAMETER_ID" = w "PARAMETER_ID"
INNER JOIN spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd "PD_ID" = sp "PD_ID"
Dà
s.“SPENT_DATE” >= '2018-07-01' AND s.“SPENT_DATE” <= '2018-09-30'AND
s."SPENT_DATE" = (SELECT MAX(s2.SPENT_DATE")
FROM wdata w2 INNER JOIN spentu s2 ON w2."SPENT_ID"=s2."SPENT_ID"
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
Tempu di pianificazione: 2.486 ms
Tempu di esecuzione: 1223680.326 ms

Allora quì hè u primu risultatu.
Era: 6 985 431.575 ms (quasi 2 ore).
Hè diventatu: 1 223 680.326 ms (pocu più di 20 minuti).
Bon risultatu. In principiu, di novu, pudemu piantà quì. Ma hè cusì pocu interessante, ùn pudete micca piantà.
PER

Step Two - Sbarazzate di a subquery correlata

Testu di dumanda cambiatu:
Nisuna sottoquestione correlataSELECT
p. "PARAMETER_ID" cum'è 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 low_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,
extract (annu da "SPENT_DATE") AS annu,
extract (mese da "SPENT_DATE") cum'è mese,
s "REPORT_NAME" AS report_name,
p "STPM_NAME" AS stpm_name,
p "CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN spentu s ON s."SPENT_ID" = w "SPENT_ID"
INNER JOIN pmtr p ON p "PARAMETER_ID" = w "PARAMETER_ID"
INNER JOIN spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd "PD_ID" = sp "PD_ID"
INNER JOIN (SELECT w2."LRM", MAX (s2."SPENT_DATE")
FROM spent s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROUP BY w2.LRM
) md on w.“LRM” = md.“LRM”
Dà
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Tempu di pianificazione: 2.291 ms
Tempu di esecuzione: 165021.870 ms

Era: 1 223 680.326 ms (pocu più di 20 minuti).
Hè diventatu: 165 021.870 ms (pocu più di 2 minuti).
Questu hè digià abbastanza bè.
Tuttavia, cum'è dicenu l'inglesi,Ma, ci hè sempre un ma". Un risultatu troppu bonu deve suscitarà automaticamente suspetti. Qualcosa hè sbagliatu quì.

L'ipotesi di currezzione di a dumanda per sbarazzarsi di a subquery correlata hè curretta. Ma ci vole un pocu tweaking per ottene u risultatu finali ghjustu.
In u risultatu, u primu risultatu intermediu:
Interrogazione editata senza sottoquestione correlataSELECT
p. "PARAMETER_ID" cum'è 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 low_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,
extract(annu da s. "SPENT_DATE") AS annu,
extract(month from s. "SPENT_DATE") cum'è mese,
s "REPORT_NAME" AS report_name,
p "STPM_NAME" AS stpm_name,
p "CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN spentu s ON s."SPENT_ID" = w "SPENT_ID"
INNER JOIN pmtr p ON p "PARAMETER_ID" = w "PARAMETER_ID"
INNER JOIN spent_pd sp ON s."SPENT_ID" = sp."SPENT_ID"
INNER JOIN pd pd ON pd "PD_ID" = sp "PD_ID"
INNER JOIN (SELEZIONA w2."LRM", MAX (s2."SPENT_DATE") AS "SPENT_DATE"
FROM spent s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GROUP BY w2.LRM
) md ON md."SPENT_DATE" = s."SPENT_DATE" AND md."LRM" = w "LRM"
Dà
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Tempu di pianificazione: 3.192 ms
Tempu di esecuzione: 208014.134 ms

Allora, ciò chì avemu u risultatu hè u primu risultatu accettatu, chì ùn avemu micca vergogna di mostrà à u cliente:
Cuminciò cù: 8 222 351.640 ms (più di 2 ore)
Avemu riesciutu à ottene: 1 ms (un pocu più di 223 minuti).
Risultato (intermedi): 208 014.134 ms (pocu più di 3 minuti).

Eccellente risultatu.

U risultatu

Questu puderia esse firmatu.
MA…
L'appetite vene cù manghjà. A strada serà ammaistrata camminendu. Ogni risultatu hè intermediu. Fermatu mortu. Etc.
Cuntinuemu cù l'ottimisazione.
Grande idea. In particulare cunsiderà chì u cliente ùn era ancu contru. È ancu forti - per.

Dunque, hè ora di riprogettà a basa di dati. A struttura di a dumanda stessu ùn pò più esse ottimizzata (ancu se, cum'è hè stata dopu, ci hè una opzione per tuttu per veramente vola). Ma avà per ottimisà è sviluppà u disignu di a basa di dati, questu hè digià una idea assai promettente. È u più impurtante interessante. Di novu, ricordate di a ghjuventù. Dopu tuttu, ùn aghju micca diventatu immediatamente un DBA, aghju cresciutu da i programatori (basic, assembler, si, si twice plused, oracle, plsql). Un tema ntirissanti, sicuru, per i ricordi separati ;-).
Tuttavia, ùn andemu micca.

Cusì,

E forse a sezione ci aiuterà?
Spoiler - "Iè, hà aiutatu, è in ottimisazione di u rendiment, cumpresu".

Ma hè una storia completamente diversa...

À seguità…

Source: www.habr.com