Ti ricordi come è iniziato tutto? Tutto era per la prima volta e ancora

Su come abbiamo dovuto ottimizzare la query PostgreSQL e cosa ne è venuto fuori.
Perché dovevi farlo? Sì, perché nei 4 anni precedenti tutto ha funzionato in silenzio, con calma, come il ticchettio dell'orologio.
Come un'epigrafe.

Basato su eventi reali.
Tutti i nomi sono stati cambiati, le coincidenze sono casuali.

Quando raggiungi un certo risultato, è sempre interessante ricordare qual è stato l'impulso all'inizio, dove tutto ha avuto inizio.

Quindi, quello che è successo di conseguenza è brevemente descritto nell’articolo “La sintesi come uno dei metodi per migliorare le prestazioni di PostgreSQL'.

Probabilmente sarà interessante ricreare la catena degli eventi precedenti.
La cronologia ha salvato la data di inizio esatta: 2018/09/10 18:02:48.
Inoltre, nella storia c'è una richiesta da cui tutto ha avuto inizio:
Richiesta di problemaSELEZIONA
p.“PARAMETER_ID” come parametro_id,
pd."PD_NAME" AS nome_pd,
pd."CUSTOMER_PARTNUMBER" AS numero_parte_cliente,
con "LRM" COME LRM,
w. "LOTID" COME lotid,
w.“RTD_VALUE” COME valore_RTD,
w.“LOWER_SPEC_LIMIT” COME lower_spec_limit,
w.“UPPER_SPEC_LIMIT” COME limite_spec_superiore,
p."TYPE_CALCUL" AS tipo_calcul,
s."SPENT_NAME" AS nome_speso,
s."SPENT_DATE" COME data_spesa,
extract(year from "SPENT_DATE") AS anno,
extract(mese da "SPENT_DATE") come mese,
s."REPORT_NAME" AS nome_report,
p."STPM_NAME" AS nome_stpm,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
DA wdata w,
trascorso s,
pmtr p,
speso_pd sp,
PD PD
DOVE 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'
e s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
DA speso s2,
wdata w2
DOVE s2.“ID_SPESA” = w2.“ID_SPESA”
AND w2.“LRM” = w.“LRM”);

La descrizione del problema è prevedibilmente standard: “Va tutto male. Dimmi qual è il problema."
Mi sono subito ricordato di un aneddoto dei tempi dei drive da 3 pollici e mezzo:

Il lamer arriva all'hacker.
-Non funziona niente per me, dimmi dov'è il problema.
-Nel DNA...

Ma ovviamente questo non è il modo di risolvere gli incidenti legati alle prestazioni. “Potrebbero non capirci" (Con). Dobbiamo capirlo.
Bene, scaviamo. Forse qualcosa si accumulerà di conseguenza.

Avviate le indagini

Quindi, cosa si vede immediatamente ad occhio nudo, senza nemmeno ricorrere a SPIEGARE.
1) I JOIN non vengono utilizzati. Ciò è negativo, soprattutto se il numero di connessioni è più di uno.
2) Ma ciò che è ancora peggio sono le subquery correlate, oltretutto, con l'aggregazione. Questo è molto brutto.
Questo è un male ovviamente. Ma questo è solo da un lato. D'altra parte questo è molto positivo, perché il problema ha chiaramente una soluzione e una richiesta che può essere migliorata.
Non andare da un'indovino (C).
Il piano di query non è così complicato, ma è abbastanza indicativo:
Progetto esecutivo

Il più interessante e utile, come al solito, è all'inizio e alla fine.
Ciclo nidificato (costo=935.84..479763226.18 righe=3322 larghezza=135) (tempo effettivo=31.536..8220420.295 righe=8111656 cicli=1)
Tempo di pianificazione: 3.807 ms
Tempo di esecuzione: 8222351.640 ms
Il tempo di completamento è superiore a 2 ore.

Ipotesi false che hanno richiesto tempo

Ipotesi 1 – L'ottimizzatore commette un errore e costruisce il piano sbagliato.

Per visualizzare il piano di esecuzione, utilizzeremo il sito https://explain.depesz.com/. Tuttavia, il sito non ha mostrato nulla di interessante o utile. A prima e seconda occhiata, non c’è nulla che possa davvero aiutare. È possibile che la scansione completa sia minima. Andare avanti.

Ipotesi 2-Impatto sulla base dal lato dell'autoaspirazione, bisogna togliere i freni.

Ma i demoni di autovacuum si comportano bene, non ci sono processi a lungo termine. Nessun carico serio. Dobbiamo cercare qualcos'altro.

Ipotesi 3 – Le statistiche sono obsolete, tutto deve essere ricalcolato

Ancora una volta, non quello. Le statistiche sono aggiornate. Il che, data l’assenza di problemi con l’autovuoto, non sorprende.

Iniziamo a ottimizzare

La tabella principale "wdata" non è certamente piccola, quasi 3 milioni di record.
Ed è questa tabella che segue Full Scan.

Cond. Hash: ((w."SPENT_ID" = s."SPENT_ID") AND ((Sottopiano 1) = s."SPENT_DATE"))
-> Scansione sequenziale su wdata w (costo=0.00..574151.49 righe=26886249 larghezza=46) (tempo effettivo=0.005..8153.565 righe=26873950 loop=1)
Facciamo la cosa standard: “dai, facciamo un indice e tutto volerà”.
Creato un indice sul campo "SPENT_ID".
Risultato:
Piano di esecuzione delle query utilizzando index

Beh, ti è stato d'aiuto?
Era: 8 222 351.640 ms (poco più di 2 ore)
È diventato: 6 985 431.575 ms (quasi 2 ore)
In generale, le stesse mele, vista laterale.
Ricordiamo i classici:
“Hai lo stesso, ma senza ali? Cercherà".

In linea di principio, questo potrebbe essere definito un buon risultato, beh, non buono, ma accettabile. Per lo meno, fornire al cliente un ampio rapporto che descriva quanto è stato fatto e perché ciò che è stato fatto è stato buono.
Ma la decisione finale è ancora lontana. Molto lontano.

E ora la cosa più interessante: continuiamo a ottimizzare, perfezioneremo la richiesta

Passo uno: utilizza UNISCITI

La richiesta riscritta ora appare così (beh almeno più bello):
Interrogare utilizzando JOINSELEZIONA
p.“PARAMETER_ID” come parametro_id,
pd."PD_NAME" AS nome_pd,
pd."CUSTOMER_PARTNUMBER" AS numero_parte_cliente,
con "LRM" COME LRM,
w. "LOTID" COME lotid,
w.“RTD_VALUE” COME valore_RTD,
w.“LOWER_SPEC_LIMIT” COME lower_spec_limit,
w.“UPPER_SPEC_LIMIT” COME limite_spec_superiore,
p."TYPE_CALCUL" AS tipo_calcul,
s."SPENT_NAME" AS nome_speso,
s."SPENT_DATE" COME data_spesa,
extract(year from "SPENT_DATE") AS anno,
extract(mese da "SPENT_DATE") come mese,
s."REPORT_NAME" AS nome_report,
p."STPM_NAME" AS nome_stpm,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN speso s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN speso_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
DOVE
s.“SPENT_DATE” >= '2018-07-01' AND s.“SPENT_DATE” <= '2018-09-30'AND
s.“DATA_SPESA” = (SELEZIONA MAX(s2.“DATA_SPESA”)
DA wdata w2 INNER JOIN speso s2 SU w2.“SPENT_ID”=s2.“SPENT_ID”
JOIN INTERNO wdata w
ON w2.“LRM” = w.“LRM” );
Tempo di pianificazione: 2.486 ms
Tempo di esecuzione: 1223680.326 ms

Quindi, il primo risultato.
Era: 6 ms (quasi 985 ore).
È diventato: 1 223 680.326 ms (poco più di 20 minuti).
Buon risultato. In linea di principio, ancora una volta, potremmo fermarci qui. Ma è così poco interessante che non puoi fermarti.
PERCHE '

Passaggio due: eliminare la sottoquery correlata

Testo della richiesta modificato:
Senza sottoquery correlataSELEZIONA
p.“PARAMETER_ID” come parametro_id,
pd."PD_NAME" AS nome_pd,
pd."CUSTOMER_PARTNUMBER" AS numero_parte_cliente,
con "LRM" COME LRM,
w. "LOTID" COME lotid,
w.“RTD_VALUE” COME valore_RTD,
w.“LOWER_SPEC_LIMIT” COME lower_spec_limit,
w.“UPPER_SPEC_LIMIT” COME limite_spec_superiore,
p."TYPE_CALCUL" AS tipo_calcul,
s."SPENT_NAME" AS nome_speso,
s."SPENT_DATE" COME data_spesa,
extract(year from "SPENT_DATE") AS anno,
extract(mese da "SPENT_DATE") come mese,
s."REPORT_NAME" AS nome_report,
p."STPM_NAME" AS nome_stpm,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN speso s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN speso_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”)
FROM speso s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GRUPPO PER w2.“LRM”
) md su w.“LRM” = md.“LRM”
DOVE
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Tempo di pianificazione: 2.291 ms
Tempo di esecuzione: 165021.870 ms

Era: 1 223 680.326 ms (poco più di 20 minuti).
È diventato: 165 021.870 ms (poco più di 2 minuti).
Questo è già abbastanza buono.
Tuttavia, come dicono gli inglesi "Ma c'è sempre un ma" Un risultato troppo positivo dovrebbe automaticamente destare sospetti. Qualcosa non va, qui.

L'ipotesi di correggere la query per eliminare la sottoquery correlata è corretta. Ma è necessario modificarlo leggermente affinché il risultato finale sia corretto.
Di conseguenza, il primo risultato intermedio:
Query modificata senza sottoquery correlataSELEZIONA
p.“PARAMETER_ID” come parametro_id,
pd."PD_NAME" AS nome_pd,
pd."CUSTOMER_PARTNUMBER" AS numero_parte_cliente,
con "LRM" COME LRM,
w. "LOTID" COME lotid,
w.“RTD_VALUE” COME valore_RTD,
w.“LOWER_SPEC_LIMIT” COME lower_spec_limit,
w.“UPPER_SPEC_LIMIT” COME limite_spec_superiore,
p."TYPE_CALCUL" AS tipo_calcul,
s."SPENT_NAME" AS nome_speso,
s."SPENT_DATE" COME data_spesa,
extract(year from s.“SPENT_DATE”) AS anno,
extract(mese da s.“SPENT_DATE”) come mese,
s."REPORT_NAME" AS nome_report,
p."STPM_NAME" AS nome_stpm,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN speso s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN speso_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.“DATA_SPESA”) COME “DATA_SPESA”
FROM speso s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GRUPPO PER w2.“LRM”
) md ON md.“DATA_SPESA” = s.“DATA_SPESA” AND md.“LRM” = w.“LRM”
DOVE
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
Tempo di pianificazione: 3.192 ms
Tempo di esecuzione: 208014.134 ms

Quindi, quello che otteniamo è il primo risultato accettabile, che non è un peccato mostrare al cliente:
Iniziato con: 8 222 351.640 ms (più di 2 ore)
Siamo riusciti a raggiungere: 1 ms (poco più di 223 minuti).
Risultato (provvisorio): 208 014.134 ms (poco più di 3 minuti).

Ottimo risultato

risultato

Avremmo potuto fermarci lì.
MA ...
L'appetito vien mangiando. Chi cammina padroneggerà la strada. Qualsiasi risultato è intermedio. Si è fermato ed è morto. Eccetera.
Continuiamo l'ottimizzazione.
Grande idea. Soprattutto considerando che al cliente non importava nemmeno. E anche fortemente per questo.

Quindi, è tempo di riprogettare il database. La struttura della query stessa non può più essere ottimizzata (anche se, come si è scoperto in seguito, esiste un'opzione per garantire che tutto fallisca effettivamente). Ma iniziare a ottimizzare e sviluppare la progettazione del database è già un’idea molto promettente. E soprattutto interessante. Ancora una volta, ricorda la tua giovinezza. Non sono diventato subito un DBA, sono cresciuto come programmatore (BASIC, assembler, C, double-plus C, Oracle, plsql). Un argomento interessante, ovviamente, per un libro di memorie separato ;-).
Tuttavia non distraiamoci.

Così,

O forse il partizionamento ci aiuterà?
Spoiler - "Sì, ha aiutato, anche nell'ottimizzazione delle prestazioni."

Ma questa è una storia completamente diversa ...

Continua…

Fonte: habr.com