Condividi tramite

Ottimizzazione indici

L'ottimizzazione degli indici è una funzionalità dell'istanza 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 integrata nell'istanza del server flessibile di Azure Database per PostgreSQL, che si basa sulla funzionalità di monitoraggio delle performance 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.

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 da Query Store le cui esecuzioni sono state acquisite nell'intervallo di ricerca su cui si concentra la 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 in Query Store e le cui statistiche di runtime non vengono reimpostate, il sistema le 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 all'analisi per la generazione 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.

Note

L'unico tipo di indici attualmente consigliato dal sistema è quello di tipo B-Tree.

Se una query fa riferimento a una colonna di una tabella e tale tabella non dispone di statistiche, l'intera query viene ignorata e non viene prodotta alcuna raccomandazione sull'indice 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.

Note

index_tuning.min_improvement_factor e index_tuning.max_regression_factor fanno entrambi riferimento al costo dei piani di query, non alla durata o alle risorse utilizzate durante l'esecuzione.

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).

Raccomandazioni relative a DROP INDEX e REINDEX

Per ogni database per il quale è stata determinata la funzionalità di ottimizzazione dell'indice, deve essere avviata una nuova sessione e, al termine della fase di raccomandazione CREATE INDEX, si 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.
  • Evidenzino un numero minimo (media giornaliera) di DML index_tuning.unused_dml_per_table nella tabella in cui viene creato l'indice.
  • Evidenzino un numero minimo (media giornaliera) di letture index_tuning.unused_reads_per_table nella tabella in cui viene creato l'indice.

Reindicizzare gli indici non validi

Le raccomandazioni per la reindicizzazione degli indici esistenti identificano gli indici contrassegnati come non validi. Per saperne di più sui motivi e sui casi in cui gli indici vengono 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 degli indici è abilitata, viene riattivata con una frequenza configurata nel parametro del server index_tuning.analysis_interval (il valore predefinito è 720 minuti o 12 ore) e inizia ad analizzare il carico di lavoro registrato da Query Store 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 degli indici 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 22: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:

Parametro Descrizione Predefinita Intervallo Unità
index_tuning.analysis_interval Imposta la frequenza con cui viene attivata ogni sessione di ottimizzazione degli indici quando index_tuning.mode è impostato su REPORT. 720 60 - 10080 minutes
index_tuning.max_columns_per_index Numero massimo di colonne che possono far parte della chiave di indice per qualsiasi indice consigliato. 2 1 - 10
index_tuning.max_index_count Numero massimo di indici consigliati per ogni database durante una sessione di ottimizzazione. 10 1 - 25
index_tuning.max_indexes_per_table Numero massimo di indici che possono essere consigliati per ogni tabella. 10 1 - 25
index_tuning.max_queries_per_database Numero di query più lente per ogni database per cui è possibile consigliare gli indici. 25 5 - 100
index_tuning.max_regression_factor Regressione accettabile introdotta da un indice consigliato in una delle query analizzate durante una sessione di ottimizzazione. 0.1 0.05 - 0.2 percentuale
index_tuning.max_total_size_factor Dimensione totale massima, in percentuale dello spazio su disco totale, che tutti gli indici consigliati per qualsiasi database specificato possono usare. 0.1 0 - 1 percentuale
index_tuning.min_improvement_factor Miglioramento dei costi che un indice consigliato deve fornire ad almeno una delle query analizzate durante una sessione di ottimizzazione. 0.2 0 - 20 percentuale
index_tuning.mode Configura l'ottimizzazione degli indici come disabilitata (OFF) o abilitata per generare solo raccomandazioni. Richiede l'abilitazione di Query Store impostando pg_qs.query_capture_mode su TOP o ALL. OFF OFF, REPORT
index_tuning.unused_dml_per_table Numero minimo di operazioni DML medie giornaliere che interessano la tabella, pertanto gli indici inutilizzati vengono considerati per l'eliminazione. 1000 0 - 9999999
index_tuning.unused_min_period Numero minimo di giorni in cui l'indice non è stato usato, in base alle statistiche di sistema, quindi viene considerato per l'eliminazione. 35 30 - 70
index_tuning.unused_reads_per_table Numero minimo di operazioni di lettura medie giornaliere che interessano la tabella in modo che gli indici inutilizzati vengano considerati per l'eliminazione. 1000 0 - 9999999

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 sono riportati l'elenco delle limitazioni e l'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 allo scopo di produrre raccomandazioni CREATE INDEX, usare l'estensione ipopg.

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

Se l'estensione non esisteva in origine o è stata eliminata perché creata nello schema pg_catalog, l'ottimizzazione dell'indice la creerà in uno schema denominato ms_temp_recommendations709253 e, al termine della sessione di ottimizzazione, eliminerà l'estensione e lo schema.

Gli utenti che sono membri del ruolo azure_pg_admin possono eliminare l'estensione hypopg in qualsiasi momento, anche se è stata creata dalla funzione di ottimizzazione dell'indice. Tuttavia, l'eliminazione dell'estensione durante una sessione di ottimizzazione dell'indice potrebbe causare l'esito negativo della sessione e impedire la produzione di 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 versioni principali12 o successive delle istanze del server flessibile di Database di Azure per PostgreSQL.

Uso di search_path

L'ottimizzazione dell'indice usa 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 di search_path impostato quando la query è stata eseguita originariamente corrisponde a quello impostato per analizzare le possibili raccomandazioni.

Query con parametri

Le query con parametri create con PREPARE o usando il protocollo di query esteso vengono 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 da Query Store 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,anch’essa 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 su un server e si riduce la potenza di calcolo di tale server al di sotto del numero minimo di vCore richiesti, 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. Poiché il server non soddisfa i requisiti minimi, tutti i parametri del server index_tuning.* sono inaccessibili. Ogni volta che si aumenta la potenza di calcolo del server fino a soddisfare i requisiti minimi, index_tuning.mode viene configurato con il valore che risultava impostato prima che venisse ridotta la potenza di calcolo e non fossero soddisfatti 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 vengono implementati gli indici consigliati. Prestare particolare attenzione quando si creano indici le cui dimensioni sono stimate di grandi dimensioni.

Motivi per cui l'ottimizzazione dell'indice potrebbe non produrre raccomandazioni per la creazione di indici per determinate query

Di seguito è riportato un elenco di tipi di query per i quali l'ottimizzazione dell'indice non genererà raccomandazioni CREATE INDEX. Si tratta di query che:

  • Rilevano un errore quando il motore di ottimizzazione dell'indice tenta di ottenere l'output EXPLAIN durante la fase di analisi.
  • Tabelle di riferimento che non dispongono di statistiche sul relativo contenuto nel catalogo di sistema pg_statistic. Eseguire ANALYZE su tali tabelle in modo che il motore di ottimizzazione possa prendere in considerazione queste query in futuro.
  • Presentano il testo della query troncato nell'archivio query. Questo è il caso in cui la lunghezza del testo della query supera il valore configurato in pg_qs.max_query_text_length.
  • Fanno riferimento a oggetti eliminati o rinominati prima che si verifichi l'analisi. Queste query potrebbero comunque essere sintatticamente valide, ma non semanticamente valide.
  • Accedono a tabelle o indici temporanei nelle tabelle temporanee.
  • Accedono alle visualizzazioni o alle viste materializzate.
  • Accedono alle tabelle partizionate.
  • Vengono identificate come istruzioni di utilità. Le istruzioni di utilità o i comandi di utilità sono, fondamentalmente, qualsiasi istruzione non considerata SELECT, INSERT, UPDATE, DELETE o MERGE e alcuni comandi contenenti una di queste istruzioni.
  • Non sono tra le primi index_tuning.max_querys_per_database più lente, per database e periodo analizzati.
  • Sono state eseguite nel contesto di un database specifico, quando nessuna di queste query è stata identificata come la più lenta a livello di server.

Contenuti correlati

  • Last updated on