Usare le raccomandazioni sugli indici

L'ottimizzazione degli indici rende persistenti le raccomandazioni che genera in un set di tabelle situato nello schema intelligentperformance nel database azure_sys.

Queste raccomandazioni possono essere lette usando la pagina di ottimizzazione dell'indice nel portale di Azure o usando il comando dell'interfaccia della riga di comando di Azure az postgres flexible-server index-tuning list-recommendations .

Tuttavia, nessuno di questi due metodi rivela il testo delle query per cui sono state prodotte le raccomandazioni. Questo comportamento è intenzionale, perché i testi delle query potrebbero contenere informazioni riservate. La visualizzazione del testo di tali istruzioni deve essere consentita solo ai soggetti autorizzati ad accedere al database. Tuttavia, non deve essere consentito a soggetti a cui viene concesso l'accesso solo all'istanza del Database di Azure per server PostgreSQL flessibile, in qualità di risorsa di Azure.

Pertanto, se è necessario leggere il testo delle query, è necessario concedere le autorizzazioni per connettersi al motore di database, in modo da poter eseguire query per recuperare tali informazioni da due viste disponibili all'interno intelligent performance del azure_sys database.

Annotazioni

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.

Annotazioni

L'ottimizzazione dell'indice dipende dal query store, che non è supportato in Azure Database for PostgreSQL versione 18.

Procedura per elencare le raccomandazioni sugli indici

Portale

Usare il portale di Azure:

1. Selezionare l'istanza del server flessibile di Database di Azure per PostgreSQL.

2. Nel menu delle risorse, in Prestazioni intelligenti, selezionare Ottimizzazione dell'indice.

   • Se la funzionalità è abilitata ma non vengono ancora generate raccomandazioni, la schermata è simile alla seguente:

     

   • Se la funzionalità è disabilitata e non ha mai prodotto raccomandazioni in passato, la schermata è simile alla seguente:

     

   • Se la funzionalità è disabilitata ma è stata abilitata prima e sono state generate raccomandazioni, la schermata sarà simile alla seguente:

     

3. Se sono disponibili raccomandazioni, selezionare il riepilogo Visualizza raccomandazioni indice per accedere all'elenco completo:

   

4. L'elenco mostra tutte le raccomandazioni disponibili con alcuni dettagli per ognuno di essi. Per impostazione predefinita, l'elenco è ordinato in base all'ultimo consigliato in ordine decrescente, che mostra le raccomandazioni più recenti nella parte superiore. Tuttavia, è possibile ordinare in base a qualsiasi altra colonna e usare la casella di filtro per ridurre l'elenco di elementi visualizzati. Gli elementi filtrati sono quelli i cui nomi di database, schema o tabella contengono il testo specificato:

   

5. Per visualizzare altre informazioni su una raccomandazione specifica, selezionare il nome della raccomandazione e il riquadro Dettagli raccomandazione indice si apre sul lato destro dello schermo per visualizzare tutti i dettagli disponibili sulla raccomandazione:

   

CLI

È possibile elencare le raccomandazioni per l'ottimizzazione degli indici prodotte dall'ottimizzazione degli indici in un server esistente tramite il comando az postgres flexible-server index-tuning list-recommendations .

Per elencare tutte le raccomandazioni CREATE INDEX, usare questo comando:

az postgres flexible-server index-tuning list-recommendations \
  --resource-group <resource_group> \
  --server-name <server> \
  --recommendation-type createindex

Il comando restituisce tutte le informazioni sulle raccomandazioni CREATE INDEX generate dall'ottimizzazione dell'indice, con un risultato simile all'output seguente:

[
  {
    "analyzedWorkload": {
      "endTime": "2025-02-26T14:40:18.788628+00:00",
      "queryCount": 18,
      "startTime": "2025-02-26T13:40:18.788628+00:00"
    },
    "details": {
      "databaseName": "<database>",
      "includedColumns": "",
      "indexColumns": "\"<table>\".\"<column>\"",
      "indexName": "<index>",
      "indexType": "BTREE",
      "schema": "<schema>",
      "table": "<table>"
    },
    "estimatedImpact": [
      {
        "absoluteValue": 0.3984375,
        "dimensionName": "IndexSize",
        "queryId": null,
        "unit": "MB"
      },
      {
        "absoluteValue": 62.86969111969111,
        "dimensionName": "QueryCostImprovement",
        "queryId": -555955670159268890,
        "unit": "Percentage"
      }
    ],
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/tuningOptions/index/recommendations/<recommendation_id>",
    "implementationDetails": {
      "method": "SQL",
      "script": "create index concurrently <index> on <schema>.<table>(<column>)"
    },
    "improvedQueryIds": [
      -555955670159268890
    ],
    "initialRecommendedTime": "2025-02-26T14:40:19.707617+00:00",
    "kind": "",
    "lastRecommendedTime": "2025-02-26T14:40:19.707617+00:00",
    "name": "CreateIndex_<database>_<schema>_<column>_idx",
    "recommendationReason": "Column \"<table>\".\"<column>\" appear in Equal Predicate clause(s) in query -555955670159268890;",
    "recommendationType": "CreateIndex",
    "resourceGroup": "<resource_group>",
    "systemData": null,
    "timesRecommended": 1,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/tuningOptions/index"
  },
  {
    .
    .
    .
  }
]

Per elencare tutte le raccomandazioni DROP INDEX, usare questo comando:

az postgres flexible-server index-tuning list-recommendations \
  --resource-group <resource_group> \
  --server-name <server> \
  --recommendation-type dropindex

Il comando restituisce tutte le informazioni sulle raccomandazioni DROP INDEX generate dall'ottimizzazione dell'indice, con un risultato simile all'output seguente:

[
  {
    "analyzedWorkload": {
      "endTime": "2025-02-26T19:02:47.522193+00:00",
      "queryCount": 0,
      "startTime": "2025-01-22T19:02:47.522193+00:00"
    },
    "details": {
      "databaseName": "<database>",
      "includedColumns": "",
      "indexColumns": "<column>",
      "indexName": "<index>",
      "indexType": "BTREE",
      "schema": "<schema>",
      "table": "<table>"
    },
    "estimatedImpact": [
      {
        "absoluteValue": 35.0,
        "dimensionName": "Benefit",
        "queryId": null,
        "unit": "Percentage"
      },
      {
        "absoluteValue": 31.28125,
        "dimensionName": "IndexSize",
        "queryId": null,
        "unit": "MB"
      }
    ],
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/tuningOptions/index/recommendations/<recommendation_id>",
    "implementationDetails": {
      "method": "SQL",
      "script": "drop index concurrently \"<schema>\".\"<index>\";"
    },
    "improvedQueryIds": null,
    "initialRecommendedTime": "2025-02-26T19:02:47.556792+00:00",
    "kind": "",
    "lastRecommendedTime": "2025-02-26T19:02:47.556792+00:00",
    "name": "DropIndex_<database>_<sechema>_<index>",
    "recommendationReason": "Duplicate of \"<index>\". The equivalent index \"<index>\" has a shorter length compared to \"<index>\".",
    "recommendationType": "DropIndex",
    "resourceGroup": "<resource_group>",
    "systemData": null,
    "timesRecommended": 1,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/tuningOptions/index"
  }
]

azure_sys

Uso di qualsiasi strumento client PostgreSQL preferito:

1. Connettersi al database azure_sys disponibile nel server con qualsiasi ruolo autorizzato a connettersi all'istanza. I membri del ruolo public possono leggere da queste visualizzazioni.

2. Eseguire query nella visualizzazione sessions per recuperare i dettagli sulle sessioni di raccomandazione.

3. Eseguire query sulla vista recommendations per recuperare le raccomandazioni generate dall'ottimizzazione dell'indice per CREATE INDEX e DROP INDEX.

Visualizzazioni

Le viste nel database azure_sys offrono un modo pratico per accedere e recuperare le raccomandazioni sugli indici generate dall'ottimizzazione dell'indice. In particolare, le viste createindexrecommendations e dropindexrecommendations contengono rispettivamente informazioni dettagliate sulle raccomandazioni CREATE INDEX e DROP INDEX. Queste viste espongono dati come l'ID sessione, il nome del database, il tipo di advisor, l'ora di avvio e arresto della sessione di ottimizzazione, l'ID raccomandazione, il tipo di raccomandazione, il motivo della raccomandazione e altri dettagli pertinenti. Gli utenti possono eseguire query su queste viste per accedere e analizzare facilmente le raccomandazioni sugli indici generate dall'ottimizzazione dell'indice.

intelligentperformance.sessions

La sessions vista espone tutti i dettagli per tutte le sessioni di ottimizzazione degli indici.

nome colonna	tipo di dati	Descrizione
session_id	UUID (Identificatore Unico Universale)	Identificatore univoco globale assegnato a ogni nuova sessione di ottimizzazione avviata.
database_name	varchar(64)	Nome del database nel cui contesto è stata eseguita la sessione di ottimizzazione dell'indice.
tipo_sessione	intelligentperformance.recommendation_type	Indica i tipi di raccomandazioni che questa sessione di ottimizzazione dell'indice può produrre. I valori possibili sono: CreateIndex, DropIndex. Le sessioni di CreateIndex tipo possono produrre raccomandazioni di CreateIndex tipo. Le sessioni di tipo DropIndex possono produrre raccomandazioni di tipo DropIndex o ReIndex.
tipo_di_esecuzione	intelligentperformance.recommendation_run_type	Indica il modo in cui è stata avviata la sessione. I valori possibili sono:Scheduled. Alle sessioni eseguite automaticamente in base al valore di index_tuning.analysis_interval, viene assegnato un tipo di esecuzione di Scheduled.
state	intelligentperformance.recommendation_state	Indica lo stato corrente della sessione. I valori possibili sono i seguenti: Error, Success, InProgress. Le sessioni di cui l'esecuzione non è riuscita vengono impostate come Error. Le sessioni che hanno completato correttamente l'esecuzione, indipendentemente dal fatto che abbiano generato o meno raccomandazioni, vengano impostate come Success. Le sessioni ancora in esecuzione vengono impostate come InProgress.
ora_di_inizio	timestamp senza fuso orario	Timestamp in cui è stata avviata la sessione di ottimizzazione che ha prodotto questa raccomandazione.
tempo di fermo	timestamp senza fuso orario	Timestamp in cui è stata avviata la sessione di ottimizzazione che ha prodotto questa raccomandazione. NULL se la sessione è in corso o è stata interrotta a causa di un errore.
recommendations_count	integer	Numero totale di raccomandazioni prodotte in questa sessione.

raccomandazioni per prestazioni intelligenti

La recommendations vista espone tutti i dettagli per tutte le raccomandazioni generate in qualsiasi sessione di ottimizzazione i cui dati sono ancora disponibili nelle tabelle sottostanti.

nome colonna	tipo di dati	Descrizione
recommendation_id	integer	Numero che identifica in modo univoco una raccomandazione nell'intero server.
last_known_session_id	UUID (Identificatore Unico Universale)	A ogni sessione di ottimizzazione dell'indice viene assegnato un identificatore univoco globale. Il valore in questa colonna rappresenta quello della sessione che ha prodotto più di recente questa raccomandazione.
database_name	varchar(64)	Nome del database nel cui contesto è stato prodotto il consiglio.
tipo_di_raccomandazione	intelligentperformance.recommendation_type	Indica il tipo di raccomandazione prodotta. I valori possibili sono i seguenti: CreateIndex, DropIndex, ReIndex.
tempo_iniziale_raccomandato	timestamp senza fuso orario	Timestamp in cui è stata avviata la sessione di ottimizzazione che ha prodotto questa raccomandazione.
last_recommended_time	timestamp senza fuso orario	Timestamp in cui è stata avviata la sessione di ottimizzazione che ha prodotto questa raccomandazione.
times_recommended	integer	Timestamp in cui è stata avviata la sessione di ottimizzazione che ha prodotto questa raccomandazione.
reason	text	Motivo che giustifica il motivo per cui questa raccomandazione è stata prodotta.
contesto_raccomandazione	json	Contiene l'elenco di identificatori di query per le query interessate dalla raccomandazione, il tipo di indice consigliato, il nome dello schema e il nome della tabella in cui è consigliato l'indice, le colonne di indice, il nome dell'indice e le dimensioni stimate in byte dell'indice consigliato.

Motivi per la creazione di raccomandazioni sugli indici

Quando l'ottimizzazione dell'indice consiglia la creazione di un indice, aggiunge almeno uno dei motivi seguenti:

Motivo
Column <column> appear in Join On clause(s) in query <queryId>
Column <column> appear in Equal Predicate clause(s) in query <queryId>
Column <column> appear in Non-Equal Predicate clause(s) in query <queryId>
Column <column> appear in Group By clause(s) in query <queryId>
Column <column> appear in Order By clause(s) in query <queryId>

Motivi per eliminare le raccomandazioni sugli indici

Quando l'ottimizzazione dell'indice identifica gli indici contrassegnati come non validi, si propone di eliminarlo con il motivo seguente:

The index is invalid and the recommended recovery method is to reindex.

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.

Motivi per eliminare le raccomandazioni sugli indici

Quando l'ottimizzazione dell'indice rileva un indice inutilizzato per almeno il numero di giorni impostato in index_tuning.unused_min_period, propone di eliminarlo con il motivo seguente:

The index is unused in the past <days_unused> days.

Quando l'ottimizzazione dell'indice rileva indici duplicati, uno dei duplicati sopravvive e propone di eliminare gli indici rimanenti. Il motivo fornito ha sempre il testo iniziale seguente:

Duplicate of <surviving_duplicate>.

Seguito da un altro testo che spiega il motivo per cui ognuno dei duplicati è stato scelto per l'eliminazione:

Motivo
The equivalent index "<surviving_duplicate>" is a Primary key, while "<droppable_duplicate>" is not.
The equivalent index "<surviving_duplicate>" is a unique index, while "<droppable_duplicate>" is not.
The equivalent index "<surviving_duplicate>" is a constraint, while "<droppable_duplicate>" is not.
The equivalent index "<surviving_duplicate>" is a valid index, while "<droppable_duplicate>" is not.
The equivalent index "<surviving_duplicate>" has been chosen as replica identity, while "<droppable_duplicate>" is not.
The equivalent index "<surviving_duplicate>" was used to cluster the table, while "<droppable_duplicate>" was not.
The equivalent index "<surviving_duplicate>" has a smaller estimated size compared to "<droppable_duplicate>".
The equivalent index "<surviving_duplicate>" has more tuples compared to "<droppable_duplicate>".
The equivalent index "<surviving_duplicate>" has more index scans compared to "<droppable_duplicate>".
The equivalent index "<surviving_duplicate>" has been fetched more times compared to "<droppable_duplicate>".
The equivalent index "<surviving_duplicate>" has been read more times compared to "<droppable_duplicate>".
The equivalent index "<surviving_duplicate>" has a shorter length compared to "<droppable_duplicate>".
The equivalent index "<surviving_duplicate>" has a smaller oid compared to "<droppable_duplicate>".

Se l'indice non solo è rimovibile a causa della duplicazione, ma è anche inutilizzato per almeno il numero di giorni impostato in index_tuning.unused_min_period, il testo seguente viene aggiunto al motivo:

Also, the index is unused in the past <days_unused> days.

Passaggi per applicare le raccomandazioni sugli indici

Le raccomandazioni sugli indici contengono l'istruzione SQL che è possibile eseguire per implementare la raccomandazione.

Nella sezione seguente viene illustrato come ottenere questa dichiarazione per una raccomandazione specifica.

Dopo aver ottenuto l'istruzione, è possibile usare qualsiasi client PostgreSQL delle proprie preferenze per connettersi al server e applicare la raccomandazione.

Portale

Usare il portale di Azure:

1. Selezionare l'istanza del server flessibile di Database di Azure per PostgreSQL.

2. Nel menu delle risorse, in Prestazioni intelligenti, selezionare Ottimizzazione dell'indice.

   

3. Supponendo che l'ottimizzazione degli indici abbia prodotto raccomandazioni, selezionare il riepilogo Visualizza raccomandazioni sugli indici per accedere all'elenco delle raccomandazioni disponibili.

   

4. Dall'elenco di raccomandazioni, scegli una delle seguenti opzioni:

   • Seleziona l'ellissi a destra della raccomandazione per cui desideri ottenere l'istruzione SQL e seleziona Copia script SQL.

     

   • In alternativa, selezionare il nome della raccomandazione per visualizzare i dettagli della raccomandazione indice e cliccare sull'icona copia nella clipboard nella casella di testo Script SQL per copiare l'istruzione SQL.

     

CLI

È possibile elencare le raccomandazioni per l'ottimizzazione degli indici prodotte dall'ottimizzazione degli indici in un server esistente tramite il comando az postgres flexible-server index-tuning list-recommendations .

Per elencare tutte le raccomandazioni CREATE INDEX, usare questo comando:

az postgres flexible-server index-tuning list-recommendations \
  --resource-group <resource_group> \
  --server-name <server> \
  --query [].implementationDetails.script

Il comando restituisce tutte le istruzioni che devono essere eseguite per implementare tutte le raccomandazioni prodotte, con un risultato simile all'output seguente:

[
  "create index concurrently <column>_idx on <schema>.<table>(<column>)",
  "drop index concurrently \"<schema>\".\"<index>\";"
]

Contenuto correlato

• Ottimizzazione dell'indice
• Configurare l'ottimizzazione degli indici
• Archivio query