設定索引微調

您可以透過一組控制行為的參數來啟用、停用和設定索引微調，例如微調工作階段的執行頻率。

索引微調取決於 查詢存放區。 不建議在高載定價層上啟用查詢存放區，因為可能會造成效能影響。 基於相同原因，不建議針對高載層中使用計算的伺服器執行索引微調。

索引微調是選擇加入的功能，依預設不會在伺服器上啟用。 此功能會在指定伺服器上的所有資料庫中全域啟用或停用，並無法在每個資料庫中個別開啟或關閉。

啟用索引微調的步驟

備註

索引調整依賴查詢儲存庫，而 Azure Database 在 PostgreSQL 18 版本中不支援查詢庫。

入口網站

使用 Azure 入口網站：

1. 選取您的 Azure PostgreSQL 資料庫彈性伺服器實例。

2. 在資源功能表中的 [ 智慧效能] 下，選取 [索引微調]。

   

3. 如果 pg_qs.query_capture_mode 被設定為 NONE，或者 index_tuning.mode 被設定為 OFF，[索引微調] 頁面會提供選項以啟用索引微調。 請在兩個 啟用索引微調 按鈕中選擇一個，以啟用索引微調功能及其所需的查詢存放區相依性（如果查詢存放區已停用的話）。

   

4. 等待部署順利完成，再考慮啟用此功能。

   

5. 啟用索引微調之後，允許索引微調引擎 12 小時分析該時間期間查詢存放區所收集的工作負載，最後會產生建立或卸除索引建議。

重要

透過 [ 啟用索引微調] 按鈕啟用索引調整 時，如果 pg_qs.query_capture_mode 設定為 NONE，則會變更為 ALL。 如果它已設定為 TOP 或 ALL，則會保持其目前狀態。

命令列介面

您可以透過 az postgres flexible-server index-tuning update 命令，在現有伺服器中啟用索引微調。

若要啟用索引微調，請使用下列命令：

az postgres flexible-server index-tuning update \
  --resource-group <resource_group> \
  --server-name <server> \
  --enabled true

如果先前的命令執行成功，您應該會看到下列輸出：

WARNING: Enabling index tuning for the server.
WARNING: Index tuning is enabled for the server.

停用索引微調的步驟

入口網站

使用 Azure 入口網站：

1. 選取您的 Azure PostgreSQL 資料庫彈性伺服器實例。

2. 在資源功能表中的 [ 智慧效能] 下，選取 [索引微調]。

   

3. 選取 [停用索引微調 ] 以停用此功能。

   

4. 等待部署順利完成，再考慮停用此功能。

   

5. 評估您是否要繼續使用 監視查詢存放區的監視效能 ，以監視工作負載的效能，並讓工作負載保持啟用狀態，或者，如果您想要停用它，請將 設定 pg_qs.query_capture_mode 為 NONE。

重要

透過 [ 停用索引微調 ] 按鈕停用索引調整時，伺服器參數 pg_qs.query_capture_mode 不會設定為 NONE，而是在設定時保留。

命令列介面

您可以透過 az postgres flexible-server index-tuning update 命令，停用現有伺服器中的索引微調。

若要停用索引微調，請使用此命令：

az postgres flexible-server index-tuning update \
  --resource-group <resource_group> \
  --server-name <server> \
  --enabled false

如果先前的命令執行成功，您應該會看到下列輸出：

WARNING: Disabling index tuning for the server.
WARNING: Index tuning is disabled for the server.

評估您是否要繼續使用 監視查詢存放區的監視效能 ，以監視工作負載的效能，並讓工作負載保持啟用狀態，或者，如果您想要停用它，請將 設定 pg_qs.query_capture_mode 為 NONE。

重要

透過 CLI 命令停用索引調整時，伺服器參數 pg_qs.query_capture_mode 不會設定為 NONE，而是在設定時保留。

顯示索引微調狀態的步驟

入口網站

使用 Azure 入口網站：

1. 選取您的 Azure PostgreSQL 資料庫彈性伺服器實例。

2. 在資源功能表中的 [ 智慧效能] 下，選取 [索引微調]。

   

3. 如果已啟用索引微調，頁面會顯示 [ 停用索引微調 ] 按鈕。

   

4. 如果停用索引微調，頁面會顯示 [ 啟用索引微調 ] 按鈕。

   

命令列介面

您可以透過 az postgres flexible-server index-tuning show 命令，在現有伺服器中顯示索引微調的狀態。

若要停用索引微調，請使用此命令：

az postgres flexible-server index-tuning show \
  --resource-group <resource_group> \
  --server-name <server>

如果已啟用索引微調，您應該會看到下列輸出：

WARNING: Index tuning is enabled for the server.

如果停用索引微調，您應該會看到下列輸出：

WARNING: Index tuning is disabled for the server.

列出索引微調設定的步驟

入口網站

使用 Azure 入口網站：

1. 選取您的 Azure PostgreSQL 資料庫彈性伺服器實例。

2. 在資源功能表中的 [ 智慧效能] 下，選取 [索引微調]。

   

3. 選取 微調設定。

   

命令列介面

您可以透過 az postgres flexible-server index-tuning show-settings 命令，在現有伺服器中顯示單一索引微調設定的值。

例如，若要顯示名為 analyze_interval的索引微調設定值，請使用此命令：

az postgres flexible-server index-tuning show-settings \
  --resource-group <resource_group> \
  --server-name <server> \
  --name analyze_interval

此命令會傳回對應至該索引微調設定之伺服器參數的所有資訊，而輸出類似下列內容：

{
  "allowedValues": "60-10080",
  "dataType": "Integer",
  "defaultValue": "720",
  "description": "Sets the frequency at which each index optimization session is triggered when index_tuning.mode is set to 'REPORT'.",
  "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.analysis_interval",
  "isConfigPendingRestart": false,
  "isDynamicConfig": true,
  "isReadOnly": false,
  "name": "index_tuning.analysis_interval",
  "resourceGroup": "<resource_group>",
  "source": "user-override",
  "systemData": null,
  "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
  "unit": "minutes",
  "value": "720"
}

此外，您也可以透過 az postgres flexible-server index-tuning list-settings 命令，在現有伺服器中顯示所有索引微調設定的清單。

若要列出所有索引微調設定，請使用此命令：

az postgres flexible-server index-tuning list-settings \
  --resource-group <resource_group> \
  --server-name <server>

此指令會傳回控制索引微調不同設定的所有伺服器參數，而且輸出類似下列內容：

[
  {
    "allowedValues": "60-10080",
    "dataType": "Integer",
    "defaultValue": "720",
    "description": "Sets the frequency at which each index optimization session is triggered when index_tuning.mode is set to 'REPORT'.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.analysis_interval",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.analysis_interval",
    "resourceGroup": "<resource_group>",
    "source": "user-override",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "minutes",
    "value": "720"
  },
  {
    "allowedValues": "1-10",
    "dataType": "Integer",
    "defaultValue": "2",
    "description": "Maximum number of columns that can be part of the index key for any recommended index.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_columns_per_index",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_columns_per_index",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "2"
  },
  {
    "allowedValues": "1-25",
    "dataType": "Integer",
    "defaultValue": "10",
    "description": "Maximum number of indexes that can be recommended for each database during one optimization session.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_index_count",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_index_count",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "10"
  },
  {
    "allowedValues": "1-25",
    "dataType": "Integer",
    "defaultValue": "10",
    "description": "Maximum number of indexes that can be recommended for each table.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_indexes_per_table",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_indexes_per_table",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "10"
  },
  {
    "allowedValues": "5-100",
    "dataType": "Integer",
    "defaultValue": "25",
    "description": "Number of slowest queries per database for which indexes can be recommended.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_queries_per_database",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_queries_per_database",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "25"
  },
  {
    "allowedValues": "0.05-0.2",
    "dataType": "Numeric",
    "defaultValue": "0.1",
    "description": "Acceptable regression introduced by a recommended index on any of the queries analyzed during one optimization session.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_regression_factor",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_regression_factor",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "percentage",
    "value": "0.1"
  },
  {
    "allowedValues": "0-1.0",
    "dataType": "Numeric",
    "defaultValue": "0.1",
    "description": "Maximum total size, in percentage of total disk space, that all recommended indexes for any given database can use.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_total_size_factor",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_total_size_factor",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "percentage",
    "value": "0.1"
  },
  {
    "allowedValues": "0-20.0",
    "dataType": "Numeric",
    "defaultValue": "0.2",
    "description": "Cost improvement that a recommended index must provide to at least one of the queries analyzed during one optimization session.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.min_improvement_factor",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.min_improvement_factor",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "percentage",
    "value": "0.2"
  },
  {
    "allowedValues": "off,report",
    "dataType": "Enumeration",
    "defaultValue": "off",
    "description": "Configures index optimization as disabled ('OFF') or enabled to only emit recommendation. Requires Query Store to be enabled by setting pg_qs.query_capture_mode to 'TOP' or 'ALL'.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.mode",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.mode",
    "resourceGroup": "<resource_group>",
    "source": "user-override",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "off"
  },
  {
    "allowedValues": "0-9999999",
    "dataType": "Integer",
    "defaultValue": "1000",
    "description": "Minimum number of daily average DML operations affecting the table, so that their unused indexes are considered for dropping.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.unused_dml_per_table",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.unused_dml_per_table",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "1000"
  },
  {
    "allowedValues": "30-720",
    "dataType": "Integer",
    "defaultValue": "35",
    "description": "Minimum number of days the index has not been used, based on system statistics, so that it is considered for dropping.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.unused_min_period",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.unused_min_period",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "days",
    "value": "35"
  },
  {
    "allowedValues": "0-9999999",
    "dataType": "Integer",
    "defaultValue": "1000",
    "description": "Minimum number of daily average read operations affecting the table, so that their unused indexes are considered for dropping.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.unused_reads_per_table",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.unused_reads_per_table",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "1000"
  }
]

修改索引微調設定的步驟

入口網站

使用 Azure 入口網站：

1. 選取您的 Azure PostgreSQL 資料庫彈性伺服器實例。

2. 在資源功能表中的 [ 智慧效能] 下，選取 [索引微調]。

   

3. 選取 微調設定。

   

4. 修改您想要變更之設定數目的值，然後選取 [ 儲存]。

   

5. 等待部署順利完成，再考慮變更設定的值。

   

命令列介面

您可以透過 az postgres flexible-server index-tuning set-settings 命令，修改現有伺服器中的單一索引微調設定值。

例如，若要將名為 analyze_interval 的索引微調設定值設定為 1440，請使用下列命令：

az postgres flexible-server index-tuning set-settings \
  --resource-group <resource_group> \
  --server-name <server> \
  --name analyze_interval \
  --value 1440

此命令會傳回對應至該索引微調設定之伺服器參數的所有資訊，而輸出類似下列內容：

{
  "allowedValues": "60-10080",
  "dataType": "Integer",
  "defaultValue": "720",
  "description": "Sets the frequency at which each index optimization session is triggered when index_tuning.mode is set to 'REPORT'.",
  "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
  "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.analysis_interval",
  "isConfigPendingRestart": false,
  "isDynamicConfig": true,
  "isReadOnly": false,
  "name": "index_tuning.analysis_interval",
  "resourceGroup": "<resource_group>",
  "source": "user-override",
  "systemData": null,
  "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
  "unit": "minutes",
  "value": "1440"
}

相關內容

• 索引微調
• 使用索引建議
• 查詢存放區