你当前正在访问 Microsoft Azure Global Edition 技术文档网站。如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

配置索引优化

2025-10-02

可以通过控制索引优化行为的参数集来启用、禁用和配置索引优化，例如优化会话的运行频率。

索引优化取决于查询存储。鉴于可能产生的性能影响，建议不要在可突发定价层上启用查询存储。出于同样的原因，不建议对可突发层中使用计算的服务器进行索引优化。

索引优化是一项选择加入功能，默认情况下它未在服务器上启用。对于给定服务器上的所有数据库，可以将其进行全局启用或禁用，但无法针对每个数据库启用或禁用。

启用索引调优的步骤

注意

索引优化取决于查询存储，而查询存储现为预览版，不受 Azure Database for PostgreSQL 灵活服务器版本 18 支持。

Portal
CLI

使用 Azure 门户：

选择你的 Azure Database for PostgreSQL 灵活服务器实例。
在资源菜单中的 “智能性能”下，选择 “索引优化”。
如果 pg_qs.query_capture_mode 设置为 NONE 或 index_tuning.mode 设置为 OFF，则“索引优化”页将提供启用索引优化的选项。如果已禁用查询存储，请选择两个“启用索引优化”按钮之一，启用索引优化功能及其所需的查询存储依赖项。
在考虑启用该功能之前，请等待部署成功完成。
启用索引优化后，允许索引优化引擎在 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 门户：

选择你的 Azure Database for PostgreSQL 灵活服务器实例。
在资源菜单中的 “智能性能”下，选择 “索引优化”。
选择 “禁用索引优化 ”以禁用该功能。
在考虑禁用该功能之前，请等待部署成功完成。
评估是否要继续使用查询存储监控性能来监视您的工作负荷性能，并保持其启用或通过将 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，而是保持其原有配置。

显示索引优化状态的步骤

Portal
CLI

使用 Azure 门户：

选择你的 Azure Database for PostgreSQL 灵活服务器实例。
在资源菜单中的 “智能性能”下，选择 “索引优化”。
如果启用了索引优化，页面将显示 “禁用索引优化 ”按钮。
如果禁用索引优化，页面将显示 “启用索引优化 ”按钮。

可以通过 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.

列出索引调优设置的步骤

Portal
CLI

使用 Azure 门户：

选择你的 Azure Database for PostgreSQL 灵活服务器实例。
在资源菜单中的 “智能性能”下，选择 “索引优化”。
选择 “调优设置”。

可以通过 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"
  }
]

修改索引优化设置的步骤

Portal
CLI

使用 Azure 门户：

选择你的 Azure Database for PostgreSQL 灵活服务器实例。
在资源菜单中的 “智能性能”下，选择 “索引优化”。
选择 “调优设置”。
根据需要修改任意数量的设置的值，然后选择“ 保存”。
等待部署成功完成，然后再考虑更改设置的值。

可以通过 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"
}

反馈

此页面是否有帮助？

通过

配置索引优化

启用索引调优的步骤

禁用索引优化的步骤

显示索引优化状态的步骤

列出索引调优设置的步骤

修改索引优化设置的步骤

相关内容

反馈

其他资源