Ottimizzazione indici

SI APPLICA A: Database di Azure per PostgreSQL - Server flessibile

L'ottimizzazione degli indici è una funzionalità del server flessibile di Database di Azure per PostgreSQL che migliora automaticamente le prestazioni del carico di lavoro analizzando le query rilevate e fornendo raccomandazioni sugli indici.

Si tratta di un'offerta predefinita nel server flessibile di Database di Azure per PostgreSQL, che si basa sulla funzionalità Monitoraggio delle prestazioni con query store. L'ottimizzazione dell'indice analizza il carico di lavoro rilevato dall'archivio query e produce raccomandazioni sugli indici per migliorare le prestazioni del carico di lavoro analizzato o per eliminare indici duplicati o inutilizzati.

• Identificare quali indici sono utili da creare, perché potrebbero migliorare significativamente le query analizzate durante una sessione di ottimizzazione dell'indice.
• Identificare gli indici che sono duplicati identici e che possono essere eliminati per ridurre l'impatto che la loro esistenza e manutenzione ha sulle prestazioni complessive del sistema.
• Identificare gli indici non utilizzati in un periodo configurabile e che potrebbero essere candidati all’eliminazione.

Descrizione generale dell'algoritmo di ottimizzazione dell'indice

Quando il parametro index_tuning.mode del server è configurato su report, le sessioni di ottimizzazione vengono avviate automaticamente con la frequenza configurata nel parametro index_tuning.analysis_interval del server, espressa in minuti.

Nella prima fase, la sessione di ottimizzazione cerca l'elenco dei database in cui ritiene che qualsiasi raccomandazione possa produrre un impatto significativo sulle prestazioni complessive del sistema. A tale scopo, raccoglie tutte le query registrate dall'archivio query le cui esecuzioni sono state acquisite nell'intervallo di ricerca su cui si concentra questa sessione di ottimizzazione. Al momento, l'intervallo di ricerca si estende agli ultimi index_tuning.analysis_interval minuti, a partire dall'ora di inizio della sessione di ottimizzazione.

Per tutte le query avviate dall'utente con esecuzioni registrate nell'archivioquery e le cui statistiche di runtime non vengono reimpostate, il sistema li classifica in base al tempo di esecuzione totale aggregato. Si incentra sulle query più importanti, in base alla loro durata.

Le query seguenti vengono escluse da tale elenco:

• Query avviate dal sistema, ovvero le query eseguite dal ruolo azuresu.
• Query eseguite nel contesto di qualsiasi database di sistema (azure_sys, template0, template1 e azure_maintenance).

L'algoritmo esegue l'iterazione sui database di destinazione, cercando possibili indici eventualmente in grado di migliorare le prestazioni dei carichi di lavoro analizzati. Cerca anche gli indici che possono essere eliminati perché vengono identificati come duplicati o come non usati per un periodo di tempo configurabile.

Raccomandazioni relative alla CREAZIONE DI INDICI

Per ogni database identificato come candidato per l'analisi per la produzione di raccomandazioni sugli indici, tutte le query SELECT, UPDATE, INSERT e DELETE eseguite durante l'intervallo di ricerca e nel contesto di tale database specifico vengono fattorizzate.

Il set di query risultante viene classificato in base al tempo di esecuzione totale aggregato e il primo index_tuning.max_queries_per_database viene analizzato per individuare le possibili raccomandazioni sugli indici.

Le potenziali raccomandazioni mirano a migliorare le prestazioni di questi tipi di query:

• Query con filtri, ovvero query con predicati nella clausola WHERE.
• Le query che si uniscono a più relazioni, indipendentemente dal fatto che seguano la sintassi in cui i join vengono espressi con la clausola JOIN o se i predicati di join sono espressi nella clausola WHERE.
• Query che combinano filtri e predicati di join.
• Query con raggruppamento (query con una clausola GROUP BY).
• Query che combinano filtri e raggruppamento.
• Query con ordinamento (query con una clausola ORDER BY).
• Query che combinano filtri e ordinamento.

Se una query fa riferimento a una colonna di una tabella e tale tabella non dispone di statistiche, ignora l'intera query e non genera raccomandazioni sugli indici per migliorarne l'esecuzione.

L'analisi necessaria per raccogliere statistiche può essere attivata manualmente usando il comando ANALYZE o automaticamente dal daemon autovacuum.

index_tuning.max_indexes_per_table specifica il numero di indici che possono essere consigliati, escludendo gli indici eventualmente già esistenti sulla tabella per ogni singola tabella a cui si fa riferimento da un numero qualsiasi di query durante una sessione di ottimizzazione.

index_tuning.max_index_count specifica il numero di raccomandazioni sugli indici prodotte per tutte le tabelle di ogni singolo database analizzato durante una sessione di ottimizzazione.

Affinché venga generata una raccomandazione sull'indice, il motore di ottimizzazione deve stimare che questa migliori almeno una query nel carico di lavoro analizzato da un fattore specificato con index_tuning.min_improvement_factor.

Allo stesso modo, vengono controllate tutte le raccomandazioni sugli indici per far sì che non introducano regressioni in nessuna delle singole query presenti in tale carico di lavoro di un fattore specificato con index_tuning.max_regression_factor.

Tutti i parametri indicati nei paragrafi precedenti, i valori predefiniti e gli intervalli validi sono descritti nelle opzioni di configurazione.

Lo script prodotto insieme alla raccomandazione per creare un indice segue questo modello:

create index concurrently {indexName} on {schema}.{table}({column_name}[, ...])

Include la clausola concurrently. Per altre informazioni sugli effetti di questa clausola, vedere la documentazione ufficiale di PostgreSQL per CREATE INDEX.

L'ottimizzazione dell'indice genera automaticamente i nomi degli indici consigliati, che in genere sono costituiti dai nomi delle diverse colonne chiave separate da "_" (underscore) e con un suffisso "_idx" costante. Se la lunghezza totale del nome supera i limiti di PostgreSQL o se si scontra con eventuali relazioni esistenti, il nome è leggermente diverso. Potrebbe essere troncato e alla fine del nome potrebbe essere aggiunto un numero.

Calcolare l'impatto di una raccomandazione CREATE INDEX

L'impatto della creazione di una raccomandazione sull'indice viene misurato su IndexSize (in megabyte) e QueryCostImprovement (in percentuale).

IndexSize è un valore singolo che rappresenta le dimensioni stimate dell'indice, considerando la cardinalità corrente della tabella e le dimensioni delle colonne a cui fa riferimento l'indice consigliato.

QueryCostImprovement è costituito da una matrice di valori, in cui ciascun elemento rappresenta il miglioramento del costo del piano per ogni query il cui costo del piano si stima migliorerebbe se esistesse tale indice. Ogni elemento mostra l'identificatore della query (sottoposto a query) e la percentuale in base alla quale il costo del piano migliorerebbe se la raccomandazione venisse implementata (dimensionale).

Indicazioni SU DROP INDEX e REINDEX

Per ogni database per cui viene determinata la funzionalità di ottimizzazione dell'indice, deve avviare una nuova sessione e dopo il completamento della fase di raccomandazione CREATE INDEX, consiglia di eliminare o reindicizzare gli indici esistenti, in base ai criteri seguenti:

• Eliminare se è considerato duplicato di altri.
• Eliminare se non viene usato per un periodo di tempo configurabile.
• Reindicizzare gli indici contrassegnati come non validi.

Eliminare indici duplicati

Raccomandazioni per l'eliminazione di indici duplicati: identificare prima di tutto quali indici hanno duplicati.

I duplicati vengono classificati in base a funzioni diverse che possono essere attribuite all'indice e in base alle dimensioni stimate.

Infine, il consiglio è di eliminare tutti i duplicati con una classificazione inferiore rispetto al relativo leader di riferimento, descrivendo il motivo per cui ogni duplicato è stato così classificato.

Affinché due indici vengano considerati duplicati, è necessario che:

• Vengano creati nella stessa tabella.
• Siano un indice dello stesso tipo.
• Le colonne chiave corrispondano e, per le chiavi indice a più colonne, corrispondano all'ordine in cui sono referenziate.
• Corrispondano all’albero delle espressioni del predicato. Applicabile solo agli indici parziali.
• Corrispondano all’albero delle espressioni di tutti i riferimenti a colonne nonsimple. Applicabile solo agli indici creati nelle espressioni.
• Corrispondano alle regole di confronto di ogni colonna a cui si fa riferimento nella chiave.

Eliminare gli indici inutilizzati

Le raccomandazioni per eliminare gli indici inutilizzati identificano gli indici che:

• Non vengono usati per almeno index_tuning.unused_min_period giorni.
• Mostra un numero minimo (medio giornaliero) di index_tuning.unused_dml_per_table DMLs nella tabella in cui viene creato l'indice.
• Mostra un numero minimo (media giornaliera) di index_tuning.unused_reads_per_table letture nella tabella in cui viene creato l'indice.

Reindicizzare indici non validi

Le raccomandazioni per la reindicizzazione degli indici esistenti identificano gli indici contrassegnati come non validi. Per altre informazioni sui motivi e sui casi in cui gli indici sono contrassegnati come non validi, vedere la documentazione ufficiale di REINDEX in PostgreSQL.

Calcolare l'impatto di una raccomandazione DROP INDEX

L'impatto di una raccomandazione drop index viene misurato su due dimensioni: Benefit (in percentuale) e IndexSize (in megabyte).

Il vantaggio è un valore singolo che al momento può essere ignorato.

IndexSize è un valore singolo che rappresenta le dimensioni stimate dell'indice, considerando la cardinalità corrente della tabella e le dimensioni delle colonne a cui fa riferimento l'indice consigliato.

Configurazione dell'ottimizzazione degli indici

L'ottimizzazione dell'indice può essere abilitata, disabilitata e configurata tramite un set di parametri che ne controllano il comportamento.

Quando l'ottimizzazione dell'indice è abilitata, viene riattivata con una frequenza configurata nel index_tuning.analysis_interval parametro del server (il valore predefinito è 720 minuti o 12 ore) e inizia ad analizzare il carico di lavoro registrato dall'archivio query durante tale periodo.

Si noti che se si modifica il valore per index_tuning.analysis_interval, viene osservato solo dopo il completamento dell'esecuzione pianificata successiva. Ad esempio, se si abilita l'ottimizzazione dell'indice un giorno alle 10:00, poiché il valore predefinito per index_tuning.analysis_interval è 720 minuti, la prima esecuzione viene pianificata per l'avvio alle 10:00 dello stesso giorno. Tutte le modifiche apportate al valore di index_tuning.analysis_interval tra le 10:00 e le 10:00 non influiscono sulla pianificazione iniziale. Solo al termine dell'esecuzione pianificata, leggerà il valore corrente impostato per index_tuning.analysis_interval e pianifica l'esecuzione successiva in base a tale valore.

Per la configurazione dei parametri di ottimizzazione degli indici sono disponibili le opzioni seguenti:

Se si usano i comandi dell'interfaccia della riga di comando az postgres flexible-server index-tuning show-settings e az postgres flexible-server index-tuning set-settings per visualizzare o modificare una delle impostazioni di ottimizzazione dell'indice, i valori accettati come argomenti per il --name parametro sono quelli visualizzati nella colonna Parameter della tabella precedente, ma senza includere il prefisso index_tuning..

Informazioni generate dall'ottimizzazione dell'indice

Come leggere, interpretare e utilizzare le raccomandazioni prodotte dall'ottimizzazione dell'indice descrive in dettaglio come ottenere e utilizzare le raccomandazioni generate dall'ottimizzazione dell'indice.

Limitazioni e supporto

Di seguito è riportato l'elenco delle limitazioni e dell'ambito di supporto per l'ottimizzazione dell'indice.

Eliminazione automatica delle raccomandazioni

Le raccomandazioni vengono eliminate automaticamente 35 giorni dopo l'ultima volta che vengono prodotte. Per il funzionamento di questo meccanismo di eliminazione automatica, è necessario abilitare l'ottimizzazione dell'indice.

Dipendenza dall'estensione ipopg

Per l'ottimizzazione dell'indice per produrre raccomandazioni CREATE INDEX, usa l'estensione ipopg .

Se l'estensione esiste già all'avvio di una sessione di ottimizzazione, viene usata nello schema in cui è stato creato. Al termine della sessione di ottimizzazione, l'estensione non viene eliminata. Un'eccezione è se l'estensione è stata creata nello pg_catalog schema. In questo caso, l'ottimizzazione dell'indice elimina l'estensione.

Se l'estensione non esiste al primo posto o è stata eliminata perché è stata creata nello pg_catalog schema, l'ottimizzazione dell'indice lo creerà in uno schema denominato ms_temp_recommendations709253 e, al termine della sessione di ottimizzazione, elimina l'estensione e rimuove lo schema.

Gli utenti membri del azure_pg_admin ruolo possono eliminare l'estensione ipopg in qualsiasi momento, anche quando è stata creata dalla funzionalità di ottimizzazione dell'indice. Tuttavia, l'eliminazione durante l'esecuzione di una sessione di ottimizzazione dell'indice potrebbe causare l'esito negativo della sessione e non produrre raccomandazioni.

Sku e livelli di calcolo supportati

L'ottimizzazione degli indici è supportata in tutti i livelli attualmente disponibili: con possibilità di burst, per utilizzo generico e ottimizzato per la memoria, e in qualsiasi SKU di calcolo attualmente supportato con almeno 4 vCore.

Versioni supportate di PostgreSQL

L'ottimizzazione degli indici è supportata nelle versioniprincipali 12 o successive di Database di Azure per PostgreSQL server flessibile.

Uso di search_path

L'ottimizzazione dell'indice utilizza il valore salvato in modo permanente nella colonna search_path di query_store.qs_view, in modo che, quando ogni query viene analizzata, lo stesso valore impostato search_path quando la query eseguita originariamente è quella in cui è impostata per analizzare le possibili raccomandazioni.

Query con parametri

Le query con parametri create con PREPARE o l'uso del protocollo di query esteso vengono analizzate e analizzate per produrre raccomandazioni sugli indici.

Per l'analisi delle query con parametri, l'ottimizzazione dell'indice richiede che pg_qs.parameters_capture_mode sia impostato su capture_first_sample quando Query Store acquisisce l'esecuzione della query. È inoltre necessario che i parametri vengano acquisiti correttamente dall'archivio query quando viene eseguita la query. In altre parole, per la query da analizzare, query_store.qs_view deve avere la colonna parameters_capture_status impostata su succeeded.

Modalità di sola lettura e repliche in lettura

Poiché l'ottimizzazione dell'indice si basa su Query Store, che non è supportato nelle repliche di lettura o quando un'istanza è in modalità di sola lettura, non è supportata nelle repliche di lettura o nelle istanze in modalità di sola lettura.

Tutte le raccomandazioni visualizzate su una replica in lettura sono state generate nella replica primaria dopo l'analisi esclusiva del carico di lavoro eseguito nella replica primaria.

Riduzione delle prestazioni di calcolo

Se l'ottimizzazione dell'indice è abilitata in un server e si riduce il numero minimo di vCore necessari, la funzionalità rimane abilitata. Poiché la funzionalità non è supportata nei server con meno di 4 vCore, non viene eseguita per analizzare il carico di lavoro e produrre raccomandazioni, anche se index_tuning.mode è stata impostata ON su quando il calcolo è stato ridotto. Anche se il server non soddisfa i requisiti minimi, tutti i index_tuning.* parametri del server non sono accessibili. Ogni volta che si esegue il backup del server in un ambiente di calcolo che soddisfa i requisiti minimi, index_tuning.mode viene configurato con qualsiasi valore impostato prima di ridimensionarlo a un ambiente di calcolo che non soddisfa i requisiti.

Disponibilità elevata e repliche in lettura

Se nel server sono configurate repliche a disponibilità elevata o in lettura, tenere presente le implicazioni associate alla produzione di carichi di lavoro a elevato utilizzo di scrittura nel server primario quando si implementano gli indici consigliati. Prestare particolare attenzione quando si creano indici le cui dimensioni sono stimate di grandi dimensioni.

Contenuto correlato

• Repository query.
• Configurare l'ottimizzazione dell'indice.
• Usare le raccomandazioni relative all'indice.