Recordes com va començar tot? Tot va ser per primera vegada i una altra vegada

Sobre com vaig haver de fer front a l'optimització de consultes PostgreSQL i què va sortir de tot això.
Per què vau haver de fer-ho? Sí, perquè els 4 anys anteriors tot va funcionar tranquil·lament, amb calma, com un rellotge.
com a epígraf.

Basat en fets reals.
S'han canviat tots els noms, les coincidències són aleatòries.

Quan s'aconsegueix un determinat resultat, sempre és interessant recordar quin va ser l'impuls del començament, com va començar tot.

Per tant, el que va passar com a resultat es descriu breument a l'article "La síntesi com un dels mètodes per millorar el rendiment de PostgreSQL».

Probablement serà interessant recrear la cadena d'esdeveniments anteriors.
L'historial va mantenir la data d'inici exacta: 2018-09-10 18:02:48.
A més, a la història hi ha una petició de la qual va començar tot:
Sol·licitud de problemaSELECT
p. "PARAMETER_ID" com a parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" COM LRM,
w. "LOTID" COM a lotid,
w. "RTD_VALUE" COM a valor_RTD,
w. "LOWER_SPEC_LIMIT" COM a límit_especificació inferior,
w. "UPPER_SPEC_LIMIT" COM a upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS spent_date,
extracte(any de "SPENT_DATE") AS any,
extracte(mes de "SPENT_DATE") com a mes,
s."REPORT_NAME" AS nom_informe,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
DES de wdataw,
gastat 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' I s."SPENT_DATE" <= '2018-09-30'
i s."SPENT_DATE" = (SELECT MAX(s2."SPENT_DATE")
DE gastat s2,
wdata w2
WHERE s2."SPENT_ID" = w2."SPENT_ID"
AND w2."LRM" = w."LRM");

Descripció del problema, previsiblement estàndard - "Tot està malament. Digueu-me quin és el problema".
De seguida vaig recordar una broma dels temps de les unitats de 3 polzades i mitja:

El lamer arriba al pirata informàtic.
- No em funciona res, digueu-me on és el problema.
-A l'ADN...

Però, per descomptat, aquesta no és la manera de resoldre els incidents de rendiment. “Potser no ens entenen"(Amb). Cal esbrinar-ho.
Bé, anem a cavar. Potser això s'acumularà com a resultat.

inversió començada

Així doncs, el que es pot veure immediatament a ull nu, sense ni tan sols recórrer a l'ajuda d'EXPLICAR.
1) Els JOIN no s'utilitzen. Això és dolent, sobretot si el nombre de connexions és més d'una.
2) Però el que és encara pitjor: una subconsulta correlacionada, a més, amb l'agregació. Això és molt dolent.
Això és dolent, és clar. Però això només és d'una banda. D'altra banda, això és molt bo, perquè el problema té clarament solució i la petició es pot millorar.
No vagis a l'endevina (C).
El pla de consultes no és tan complicat, però força indicatiu:
Pla d'execució

El més interessant i útil, com és habitual, al principi i al final.
Bucle imbricat (cost=935.84..479763226.18 files=3322 amplada=135) (temps real=31.536..8220420.295 files=8111656 bucles=1)
Temps de planificació: 3.807 ms
Temps d'execució: 8222351.640ms
El temps d'execució és superior a 2 hores.

Hipòtesis falses que van portar temps

Hipòtesi 1- L'optimitzador està equivocat, construeix el pla equivocat.

Per visualitzar el pla d'execució, utilitzarem el lloc https://explain.depesz.com/. Tanmateix, el lloc no mostrava res interessant o útil. A primera i segona vista, res que pugui ajudar realment. Tret que: l'escaneig complet sigui mínim. Endavant.

Hipòtesi 2-Impacte a la base des del costat de l'autoaspirador, cal desfer-se dels frens.

Però, els dimonis d'autovacuum es comporten bé, no hi ha processos de llarga durada. Qualsevol càrrega greu - no. Cal buscar una altra cosa.

La hipòtesi 3-Estadística està obsoleta, cal recalcular tot vola

De nou, això no. Les estadístiques estan actualitzades. La qual cosa, donada la manca de problemes amb l'autobuit, no és d'estranyar.

Començant a optimitzar

La taula principal 'wdata' certament no és petita, gairebé 3 milions de registres.
I és sobre aquesta taula on es troba Full Scan.

Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") I ((SubPlan 1) = s."SPENT_DATE"))
-> Seq Scan a wdata w (cost=0.00..574151.49 files=26886249 amplada=46) (temps real=0.005..8153.565 files=26873950 bucles=1)
Actuem com a estàndard: “fem un índex i tot vola”.
S'ha fet un índex al camp "SPENT_ID"
Com a resultat:
Consulta el pla d'execució mitjançant un índex

Bé, va ajudar?
Era: 8 222 351.640 ms (poc més de 2 hores)
Es va convertir en: 6 985 431.575 ms (gairebé 2 hores)
En general, les mateixes pomes, vista lateral.
Recordem els clàssics:
“Tens el mateix, però sense ales? buscarà".

En principi, això es podria anomenar un bon resultat, bé, no bo, però acceptable. Com a mínim, proporcioneu un informe gran al client que descrigui quant s'ha fet i per què el que es fa és bo.
Tanmateix, la decisió final encara està lluny. Molt lluny.

I ara el més interessant: seguim optimitzant, polirem la consulta

Primer pas: utilitzeu JOIN

Consulta reescrita, ara té aquest aspecte (bé, almenys més bonic):
Consulta amb JOINSELECT
p. "PARAMETER_ID" com a parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" COM LRM,
w. "LOTID" COM a lotid,
w. "RTD_VALUE" COM a valor_RTD,
w. "LOWER_SPEC_LIMIT" COM a límit_especificació inferior,
w. "UPPER_SPEC_LIMIT" COM a upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS spent_date,
extracte(any de "SPENT_DATE") AS any,
extracte(mes de "SPENT_DATE") com a mes,
s."REPORT_NAME" AS nom_informe,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN gastat 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"
WHERE
s."SPENT_DATE" >= '2018-07-01' I s."SPENT_DATE" <= '2018-09-30'AND
s."SPENT_DATE" = (SELECCIONA MAX(s2."SPENT_DATE")
FROM wdata w2 INNER JOIN gastat s2 ON w2."SPENT_ID"=s2."SPENT_ID"
INNER JOIN wdata w
ON w2."LRM" = w."LRM" );
Temps de planificació: 2.486 ms
Temps d'execució: 1223680.326ms

Així que aquí teniu el primer resultat.
Era: 6 985 431.575 ms (quasi 2 hores).
Es va convertir en: 1 223 680.326 ms (poc més de 20 minuts).
Bon resultat. En principi, de nou, seria possible aturar-s'hi. Però tan poc interessant, no pots parar.
PER

Segon pas: desfer-se de la subconsulta correlacionada

S'ha canviat el text de la sol·licitud:
No hi ha subconsulta correlacionadaSELECT
p. "PARAMETER_ID" com a parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" COM LRM,
w. "LOTID" COM a lotid,
w. "RTD_VALUE" COM a valor_RTD,
w. "LOWER_SPEC_LIMIT" COM a límit_especificació inferior,
w. "UPPER_SPEC_LIMIT" COM a upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS spent_date,
extracte(any de "SPENT_DATE") AS any,
extracte(mes de "SPENT_DATE") com a mes,
s."REPORT_NAME" AS nom_informe,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN gastat 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 gastat s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GRUP PER w2.LRM
) md on w. "LRM" = md. "LRM"
WHERE
s."PENT_DATE" >= '2018-07-01' I s."SPENT_DATE" <= '2018-09-30';
Temps de planificació: 2.291 ms
Temps d'execució: 165021.870ms

Era: 1 223 680.326 ms (poc més de 20 minuts).
Es va convertir en: 165 021.870 ms (poc més de 2 minuts).
Això ja està força bé.
Tanmateix, com diuen els anglesos,Però, sempre hi ha un però". Un resultat massa bo hauria de despertar sospita automàticament. Alguna cosa no funciona aquí.

La hipòtesi sobre la correcció de la consulta per desfer-se de la subconsulta correlacionada és correcta. Però cal una mica de retoc per aconseguir el resultat final correcte.
Com a resultat, el primer resultat intermedi:
Consulta editada sense subconsulta correlacionadaSELECT
p. "PARAMETER_ID" com a parameter_id,
pd. "PD_NAME" AS pd_name,
pd. "CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LRM" COM LRM,
w. "LOTID" COM a lotid,
w. "RTD_VALUE" COM a valor_RTD,
w. "LOWER_SPEC_LIMIT" COM a límit_especificació inferior,
w. "UPPER_SPEC_LIMIT" COM a upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spent_name,
s."SPENT_DATE" AS spent_date,
extracte(any del s. "SPENT_DATE") AS any,
extreu(mes a partir del dia "SPENT_DATE") com a mes,
s."REPORT_NAME" AS nom_informe,
p. "STPM_NAME" AS stpm_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wdata w INNER JOIN gastat 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") COM "SPENT_DATE"
FROM gastat s2 INNER JOIN wdata w2 ON s2."SPENT_ID" = w2."SPENT_ID"
GRUP PER w2.LRM
) md ON md."SPENT_DATE" = s."SPENT_DATE" I md."LRM" = w."LRM"
WHERE
s."PENT_DATE" >= '2018-07-01' I s."SPENT_DATE" <= '2018-09-30';
Temps de planificació: 3.192 ms
Temps d'execució: 208014.134ms

Així doncs, el que tenim com a resultat és el primer resultat acceptable, que no tenim vergonya de mostrar al client:
Va començar amb: 8 222 351.640 ms (més de 2 hores)
Assolit: 1 ms (poc més de 223 minuts).
Resultat (intermedi): 208 014.134 ms (poc més de 3 minuts).

Excel·lent resultat.

Total

Això podria haver-se aturat.
PERÒ…
La gana ve amb menjar. El camí es dominarà caminant. Qualsevol resultat és intermedi. Parat mort. Etc.
Continuem amb l'optimització.
Bona idea. Sobretot tenint en compte que el client ni tan sols estava en contra. I fins i tot amb força - per.

Per tant, és hora de redissenyar la base de dades. L'estructura de la consulta ja no es pot optimitzar (tot i que, com va resultar més tard, hi ha una opció perquè tot surti realment). Però ara per optimitzar i desenvolupar el disseny de la base de dades, aquesta ja és una idea molt prometedora. I el més important és interessant. De nou, recordeu la joventut. Al cap i a la fi, no em vaig convertir immediatament en un DBA, vaig sorgir de programadors (bàsic, assemblador, si, si dues vegades més, oracle, plsql). Un tema interessant, és clar, per a memòries separades ;-).
Tanmateix, no ens divaguem.

Per tant,

I potser el seccionament ens ajudarà?
Spoiler - "Sí, va ajudar, i en l'optimització del rendiment, inclòs."

Però aquesta és una història completament diferent...

Continuarà…

Font: www.habr.com