Diversi mesi fa - pubblico a PostgreSQL.
L'hai usato più di 6000 volte da allora, ma una delle funzioni utili potrebbe essere passata inosservata è indizi strutturali, che assomigliano a questo:
Ascoltali e le tue richieste "diventeranno lisce come la seta". 🙂
Ma seriamente, molte situazioni che rendono una richiesta lenta e “ghiotta” in termini di risorse, sono tipici e si riconoscono dalla struttura e dai dati del piano.
In questo caso, ogni singolo sviluppatore non dovrà cercare da solo un'opzione di ottimizzazione, affidandosi esclusivamente alla propria esperienza: possiamo dirgli cosa sta succedendo qui, quale potrebbe essere il motivo e come trovare una soluzione. Che è quello che abbiamo fatto.
Diamo un'occhiata più da vicino a questi casi: come vengono definiti e quali raccomandazioni portano.
Per una migliore immersione nell'argomento, puoi prima ascoltare il blocco corrispondente da , e solo allora vai a un'analisi dettagliata di ciascun esempio:
#1: indice "sottoordinamento"
Quando sorge
Mostra l'ultima fattura per il cliente "LLC Kolokolchik".
Come identificare
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Raccomandazioni
Indice utilizzato espandere con i campi di ordinamento.
Esempio:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
Si nota subito che più di 100 record sono stati sottratti dall'indice, che poi sono stati tutti ordinati, e poi è rimasto l'unico.
Risolviamo:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Anche su un campione così primitivo - 8.5 volte più veloce e 33 volte meno letture. L'effetto sarà più chiaro, più "fatti" avrai per ogni valore. fk.
Prendo atto che un tale indice funzionerà come un indice "prefisso" non peggiore del precedente per altre query con fk, dove ordinare per pk non era e non è (puoi leggere di più su questo ). In particolare, fornirà normale supporto esplicito per chiavi esterne da questo campo.
#2: intersezione indice (BitmapAnd)
Quando sorge
Mostra tutti i contratti per il cliente "LLC Kolokolchik" conclusi per conto di "NJSC Lyutik".
Come identificare
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRaccomandazioni
creare indice composito per campi da entrambe le fonti o espandere uno dei campi esistenti dal secondo.
Esempio:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
Risolviamo:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Qui il guadagno è minore, poiché Bitmap Heap Scan è abbastanza efficace da solo. Ma in ogni caso 7 volte più veloce e 2.5 volte meno letture.
#3: Combinazione di indici (BitmapOr)
Quando sorge
Mostra le prime 20 richieste "proprie" o non assegnate più vecchie per l'elaborazione, con la propria priorità.
Come identificare
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRaccomandazioni
Da usare UNIONE [TUTTI] per combinare sottoquery per ciascuno dei blocchi OR di condizione.
Esempio:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
Risolviamo:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
Abbiamo approfittato del fatto che tutti i 20 record necessari sono stati ottenuti immediatamente nel primo blocco, quindi il secondo, con il più “costoso” Bitmap Heap Scan, non è stato nemmeno eseguito - di conseguenza 22 volte più veloce, 44 volte meno letture!
Una storia più dettagliata su questo metodo di ottimizzazione su esempi concreti si può leggere negli articoli и .
Versione generalizzata selezione ordinata da più chiavi (e non solo per una coppia di const / NULL) è discusso nell'articolo .
#4: Leggiamo troppo
Quando sorge
Di norma, si verifica quando si desidera "associare un altro filtro" a una richiesta esistente.
“E tu non hai lo stesso, ma con bottoni in madreperla? » film "Mano di diamante"
Ad esempio, modificando l'attività sopra, mostra le prime 20 richieste "critiche" più vecchie per l'elaborazione, indipendentemente dal loro scopo.
Come identificare
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Raccomandazioni
Crea [più] specializzato indice con clausola WHERE o includere campi aggiuntivi nell'indice.
Se la condizione di filtro è "statica" per le tue attività, cioè non include l'espansione elenco di valori in futuro: è meglio utilizzare un indice WHERE. Vari stati boolean/enum rientrano bene in questa categoria.
Se la condizione di filtrazione può assumere valori diversi, è meglio espandere l'indice con questi campi, come nella situazione con BitmapE sopra.
Esempio:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
Risolviamo:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Come puoi vedere, il filtro dal piano è completamente sparito e la richiesta è diventata 5 volte più veloce.
#5: tabella sparsa
Quando sorge
Vari tentativi di mettere in coda l'elaborazione delle proprie attività, quando un gran numero di aggiornamenti/cancellazioni di record sulla tabella portano a una situazione di un gran numero di record "morti".
Come identificare
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Raccomandazioni
Eseguire manualmente regolarmente VUOTO [PIENO] o ottenere elaborazioni adeguatamente frequenti mettendo a punto i suoi parametri, tra cui .
Nella maggior parte dei casi, tali problemi sono causati da un layout di query scadente quando chiamato dalla logica aziendale, come quelli discussi in .
Ma dobbiamo capire che anche VACUUM FULL non può sempre aiutare. Per questi casi, dovresti familiarizzare con l'algoritmo dell'articolo. .
#6: lettura dal "centro" dell'indice
Quando sorge
Sembra che abbiano letto un po 'e tutto è stato indicizzato e non hanno filtrato nessuno in più, ma sono state lette molte più pagine di quanto vorremmo.
Come identificare
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Raccomandazioni
Dai un'occhiata da vicino alla struttura dell'indice utilizzato e ai campi chiave specificati nella query - molto probabilmente, parte indice non impostata. Molto probabilmente dovrai creare un indice simile, ma senza campi prefisso, o .
Esempio:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
Tutto sembra andare bene, anche in termini di indice, ma in qualche modo sospetto - per ciascuno dei 20 record letti, sono state sottratte 4 pagine di dati, 32 KB per record - non è grassetto? Sì e nome indice tbl_fk_org_fk_cli_idx porta al pensiero.
Risolviamo:
CREATE INDEX ON tbl(fk_cli);
All'improvviso - 10 volte più veloce e 4 volte meno da leggere!
Per ulteriori esempi di utilizzo inefficiente degli indici, vedere l'articolo .
N. 7: CTE × CTE
Quando sorge
A richiesta ha ottenuto un CTE "grasso". da tavoli diversi, e poi ha deciso di fare tra di loro JOIN.
Il caso è rilevante per le versioni precedenti alla v12 o richieste con WITH MATERIALIZED.
Come identificare
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Raccomandazioni
Analizza attentamente la richiesta ? Se sì, allora applicare "dizionario" in hstore/json secondo il modello descritto in .
# 8: scambia su disco (temp scritto)
Quando sorge
L'elaborazione una tantum (ordinamento o univozzazione) di un numero elevato di record non rientra nella memoria allocata per questo.
Come identificare
-> *
&& temp written > 0Raccomandazioni
Se la quantità di memoria utilizzata dall'operazione non supera di molto il valore impostato del parametro , dovrebbe essere corretto. Puoi immediatamente nella configurazione per tutti, oppure puoi passare SET [LOCAL] per una specifica richiesta/transazione.
Esempio:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Risolviamo:
SET work_mem = '128MB'; -- перед выполнением запроса
Per ovvi motivi, se viene utilizzata solo la memoria e non il disco, la query verrà eseguita molto più velocemente. Allo stesso tempo, parte del carico viene rimossa anche dall'HDD.
Ma devi capire che anche l'allocazione di molta memoria non funzionerà sempre: semplicemente non sarà sufficiente per tutti.
#9: Statistiche irrilevanti
Quando sorge
Molto è stato versato subito nella base, ma non hanno avuto il tempo di scacciarlo ANALYZE.
Come identificare
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Raccomandazioni
Spendere lo stesso ANALYZE.
Questa situazione è descritta più dettagliatamente in .
#10: "qualcosa è andato storto"
Quando sorge
C'era un blocco in attesa di una richiesta concorrente o non c'erano risorse hardware CPU/hypervisor sufficienti.
Come identificare
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Raccomandazioni
Usa un esterno sistema di monitoraggio server per il blocco o il consumo anomalo delle risorse. Abbiamo già parlato della nostra versione dell'organizzazione di questo processo per centinaia di server. и .
Fonte: habr.com