Qualche mese fa — pubblico per PostgreSQL.
Nel frattempo, l'avete già utilizzato più di 6000 volte, ma una delle funzioni convenienti potrebbe essere rimasta inosservata: si tratta di suggerimenti strutturali, che appaiono più o meno così:
Fate attenzione a loro e le vostre query «diventeranno fluide e setose». 🙂
E se vogliamo essere seri, molte situazioni che rendono una query lenta e «affamatica» in termini di risorse sono tipiche e possono essere riconosciute dalla struttura e dai dati del piano.
In questo caso, ogni singolo sviluppatore non dovrà cercare un'opzione di ottimizzazione da solo, basandosi esclusivamente sulla propria esperienza: possiamo dirgli cosa sta succedendo, qual è la causa e come si può arrivare a una soluzione. Ed è esattamente ciò che abbiamo fatto.
Esaminiamo più in dettaglio questi casi: come vengono definiti e a quali raccomandazioni portano.
Per un approfondimento dell'argomento, potete prima ascoltare il blocco corrispondente di , e solo dopo passare all'analisi dettagliata di ciascun esempio:
#1: индексная «недосортировка»
Quando si verifica
Mostra l'ultima fattura del cliente «LLC Campanello».
Come riconoscere
-> Limite
-> Ordinamento
-> Scansione dell'indice [Solo] [Backwards] | Scansione Bitmap Heap
Raccomandazioni
Indice utilizzato ampliare con i campi di ordinamento.
Esempio:
CREA TABELLA tbl AS
SELEZIONA
generate_series(1, 100000) pk -- 100K "fatti"
, (random() * 1000)::integer fk_cli; -- 1K chiavi esterne diverse
CREA INDICE SU tbl(fk_cli); -- indice per chiave esterna
SELEZIONA
*
DA
tbl
DOVE
fk_cli = 1 -- selezione per un particolare relazione
ORDER BY
pk DESC -- vogliamo solo una "ultima" registrazione
LIMITA 1; 
Si può notare subito che dall'indice sono stati letti più di 100 registrazioni, che poi sono state tutte ordinate, e alla fine è stata lasciata solo una.
Correggiamo:
DROP INDEX tbl_fk_cli_idx;
CREA INDICE SU tbl(fk_cli, pk DESC); -- aggiunto chiave di ordinamento
Anche su un campione così primitivo — è stato 8.5 volte più veloce e 33 volte meno letture.L'effetto sarà tanto più evidente, quanti più «fatti» avrete per ogni valore fk.
Osserva che questo indice funzionerà come «prefisso» altrettanto bene e per altre query con fk, dove non c'era e non c'è ordinamento per pk non c'era e non c'è (si può leggere di più ). Include anche un adeguato supporto esplicito della chiave esterna per questo campo.
#2: пересечение индексов (BitmapAnd)
Quando si verifica
Mostra tutti i contratti del cliente «LLC Campanello», stipulati a nome di «NAO Lutik».
Come riconoscere
-> BitmapAnd
-> Scansione dell'indice Bitmap
-> Scansione dell'indice BitmapRaccomandazioni
Crea indice composito da campi di entrambe le origini o espandere uno dei campi esistenti con quelli provenienti dall'altra.
Esempio:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "fatti"
, (random() * 100)::integer fk_org -- 100 chiavi esterne diverse
, (random() * 1000)::integer fk_cli; -- 1K chiavi esterne diverse
CREATE INDEX ON tbl(fk_org); -- indice per chiave esterna
CREATE INDEX ON tbl(fk_cli); -- indice per chiave esterna
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- selezione per una coppia specifica 
Correggiamo:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Qui il guadagno è minore, poiché il Bitmap Heap Scan è già abbastanza efficiente di per sé. Tuttavia è 7 volte più veloce e richiede 2,5 volte meno letture.
#3: объединение индексов (BitmapOr)
Quando si verifica
Mostra le prime 20 richieste "proprie" o non assegnate per l'elaborazione, con le proprie in priorità.
Come riconoscere
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRaccomandazioni
di utilizzare UNION [ALL] per unire sottoquery in ciascuno dei blocchi di condizioni OR.
Esempio:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "fatti"
, CASE
WHEN random() < 1::real/16 THEN NULL -- con probabilità 1:16 registrazione "pareggio"
ELSE (random() * 100)::integer -- 100 chiavi esterne diverse
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- indice con ordinamento "sembra appropriato"
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- proprie
fk_own IS NULL -- ... o "pareggi"
ORDER BY
pk
, (fk_own = 1) DESC -- prima "proprie"
LIMIT 20;
Correggiamo:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- prima "proprie" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- poi "pareggi" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- ma in totale - 20, non serve di più 
Abbiamo sfruttato il fatto che tutte le 20 registrazioni necessarie sono state ottenute già nel primo blocco, quindi il secondo, con un Bitmap Heap Scan più "costoso", non è nemmeno stato eseguito — alla fine è 22 volte più veloce, con 44 volte meno letture!
Una descrizione più dettagliata di questo metodo di ottimizzazione con esempi specifici può essere letta negli articoli e .
Versione generalizzata selezione ordinata per più chiavi (e non solo per una coppia const/NULL) è stata trattata nell'articolo .
#4: читаем много лишнего
Quando si verifica
Di solito si verifica quando si desidera "aggiungere un ulteriore filtro" a una query già esistente.
"E non avete anche uno simile, ma con bottoni di madreperla?» film "La mano di diamante"
Ad esempio, modificando il compito di cui sopra, mostrare le prime 20 richieste "critiche" da elaborare, indipendentemente dalla loro assegnazione.
Come riconoscere
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × righe < RRbF -- filtrato >80% di quanto letto
&& loops × RRbF > 100 -- e con oltre 100 registrazioni in totale
Raccomandazioni
Creare [più] specializzato indice con condizione WHERE o includere nel indice campi aggiuntivi.
Se la condizione di filtraggio è "statica" per le tue esigenze, cioè non prevede un ampliamento dell'elenco dei valori in futuro, è meglio utilizzare l'indice WHERE. Questa categoria si adatta bene a vari stati booleani/enum.
Se invece la condizione di filtraggio può assumere valori diversi,, è preferibile ampliare l'indice con questi campi, come nella situazione con BitmapAnd menzionata sopra.
Esempio:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "fatti"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 chiavi esterne diverse
END fk_own
, (random() < 1::real/50) critical; -- 1:50, che richiede "critico"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20; 
Correggiamo:
CREATE INDEX ON tbl(pk)
WHERE critical; -- abbiamo aggiunto una condizione di filtraggio "statica"
Come vediamo, il filtraggio è completamente scomparso dal piano, e la query è diventata 5 volte più veloce.
#5: разреженная таблица
Quando si verifica
Varie tentativi di creare una propria coda di elaborazione compiti, quando un gran numero di aggiornamenti/cancellazioni di record nella tabella porta a una situazione con un gran numero di record "morti".
Come riconoscere
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (righe + RRbF) < (hit condivisi + lettura condivisa) × 8
-- letti più di 1KB per ogni record
&& hit condivisi + lettura condivisa > 64
Raccomandazioni
Eseguire regolarmente a mano VACUUM [FULL] o garantire un'esecuzione adeguatamente frequente affinando i suoi parametri, incluso .
Nella maggior parte dei casi, simili problemi sono causati da una scarsa strutturazione delle query durante le chiamate dalla logica di business come quelle esaminate in .
Ma è importante capire che anche VACUUM FULL non può sempre aiutare. Per tali casi, è utile consultare l'algoritmo dell'articolo .
#6: чтение с «середины» индекса
Quando si verifica
Sembra che abbiamo letto un po', e tutto tramite l'indice, e non abbiamo filtrato nulla in eccesso - eppure sono state lette sostanzialmente più pagine di quanto desiderato.
Come riconoscere
-> Index [Only] Scan [Backward]
&& loops × (righe + RRbF) < (hit condivisi + lettura condivisa) × 8
-- letti più di 1KB per ogni record
&& hit condivisi + lettura condivisa > 64
Raccomandazioni
Esaminare attentamente la struttura dell'indice utilizzato e i campi chiave specificati nella query - probabilmente, una parte dell'indice non è stata definita.Probabilmente dovrai creare un indice simile, ma senza campi prefissi o .
Esempio:
CREA TABELLA tbl COME
SELECT
generate_series(1, 100000) pk -- 100K "fatti"
, (random() * 100)::integer fk_org -- 100 diverse chiavi esterne
, (random() * 1000)::integer fk_cli; -- 1K diverse chiavi esterne
CREA INDICE SU tbl(fk_org, fk_cli); -- tutto quasi come in #2
-- solo che abbiamo già considerato inutile un indice separato su fk_cli e l'abbiamo rimosso
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- e fk_org non è specificato, anche se è presente prima nell'indice
LIMIT 20; 
Sembra che vada tutto bene, anche per l'indice, ma è un po' sospetto: per ciascuna delle 20 righe lette si sono dovute consultare 4 pagine di dati, 32KB per scrittura — non è un po' troppo? E anche il nome dell'indice tbl_fk_org_fk_cli_idx invita a riflessioni.
Correggiamo:
CREA INDICE SU tbl(fk_cli); 
Improvvisamente — 10 volte più veloce e 4 volte meno da leggere!
Altri esempi di situazioni di inefficace utilizzo degli indici possono essere visti nell'articolo .
#7: CTE × CTE
Quando si verifica
Nella query abbiamo scritto CTE "grasse" da tabelle diverse e poi abbiamo deciso di fare" tra di loro JOIN.
Il caso è pertinente per versioni inferiori a v12 o query con WITH MATERIALIZZATO.
Come riconoscere
-> CTE Scan
&& loops > 10
&& loops × (righe + RRbF) > 10000
-- prodotto cartesiano CTE troppo grande
Raccomandazioni
Analizzare attentamente la query — se ? Если все-таки да, то applicare "dizionarizzazione" in hstore/json secondo il modello descritto in .
#8: swap на диск (temp written)
Quando si verifica
L'elaborazione una tantum (ordinamento o univocizzazione) di un grande numero di righe non rientra nella memoria allocata per questo.
Come riconoscere
-> *
&& temp scritto > 0Raccomandazioni
Se la quantità di memoria utilizzata dall'operazione non supera di molto il valore impostato del parametro , vale la pena regolarlo. Puoi farlo direttamente nel config per tutti, oppure tramite SET [LOCAL] per una specifica query/transazione.
Esempio:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1; 
Correggiamo:
SET work_mem = '128MB'; -- prima di eseguire la query 
Per motivi evidenti, se si utilizza solo la memoria e non il disco, la query verrà eseguita molto più velocemente. Inoltre, parte del carico viene tolto dall'HDD.
Ma bisogna capire che non si può sempre allocare molta memoria — semplicemente non ci sarà sufficiente per tutti.
#9: неактуальная статистика
Quando si verifica
Sono stati iniettati molti dati nella base, ma non è stato possibile eseguire ANALIZZARE.
Come riconoscere
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& rapporto >> 10Raccomandazioni
Procedere ANALIZZARE.
Questa situazione è descritta in dettaglio in .
#10: «что-то пошло не так»
Quando si verifica
Si è verificata un'attesa di blocco, imposta da una query concorrente, o non ci sono stati sufficienti risorse hardware CPU/ipervisor.
Come riconoscere
-> *
&& (hit condiviso / 8K) + (lettura condivisa / 1K) 100ms -- abbiamo letto poco, ma troppo a lungo
Raccomandazioni
Utilizza esterno sistema per il monitoraggio del server per la presenza di blocchi o di un consumo anomalo delle risorse. Già abbiamo parlato della nostra modalità di organizzazione di questo processo per centinaia di server e .
Fonte: habr.com
