可以通过 az postgres flexible-server autonomous-tuning list-index-recommendations 命令列出通过现有服务器中自主优化生成的索引建议。
若要列出所有 CREATE INDEX 建议，请使用以下命令：
az postgres flexible-server autonomous-tuning list-index-recommendations \
--resource-group <resource_group> \
--server-name <server> \
--recommendation-type createindex
该命令返回有关自动优化生成的 CREATE INDEX 建议的所有信息，并显示如下所示的输出。
[
{
"analyzedWorkload": {
"endTime": "2026-01-27T14:40:18.788628+00:00",
"queryCount": 18,
"startTime": "2026-01-27T13:40:22.544654+00:00"
},
"currentState": "Active",
"details": {
"databaseName": "<database>",
"includedColumns": [
""
],
"indexColumns": [
"\"<tabe>\".\"<column>\""
],
"indexName": "<index>",
"indexType": "BTREE",
"schema": "<schema>",
"table": "<table>"
},
"estimatedImpact": [
{
"absoluteValue": 1.9296875,
"dimensionName": "IndexSize",
"queryId": null,
"unit": "MB"
},
{
"absoluteValue": 99.98674047373842,
"dimensionName": "QueryCostImprovement",
"queryId": -2000193826232128395,
"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": [
-2000193826232128395
],
"initialRecommendedTime": "2026-01-27T14:40:19.707617+00:00",
"kind": "",
"lastRecommendedTime": "2026-01-27T14:40:19.707617+00:00",
"name": "CreateIndex_<database>_<schema>_<index>",
"recommendationReason": "Column \"<table>\".\"<column>\" appear in Equal Predicate clause(s) in query -2000193826232128395;",
"recommendationType": "CreateIndex",
"resourceGroup": "<resource_group>",
"systemData": null,
"timesRecommended": 1,
"type": "Microsoft.DBforPostgreSQL/flexibleServers/tuningOptions/index"
},
{
.
.
.
}
]
若要列出所有 DROP INDEX 建议，请使用以下命令：
az postgres flexible-server autonomous-tuning list-index-recommendations \
--resource-group <resource_group> \
--server-name <server> \
--recommendation-type dropindex
该命令返回所有关于自动调优生成的 DROP INDEX 建议的信息，显示类似于以下输出的内容：
[
{
"analyzedWorkload": {
"endTime": "2026-01-27T19:02:47.522193+00:00",
"queryCount": 0,
"startTime": "2026-01-27T19:02:47.522193+00:00"
},
"currentState": "Active",
"details": {
"databaseName": "<database>",
"includedColumns": [
""
],
"indexColumns": [
"<column>"
],
"indexName": "<index>",
"indexType": "BTREE",
"schema": "<schema>",
"table": "<table>"
},
"estimatedImpact": [
{
"absoluteValue": 31.0,
"dimensionName": "Benefit",
"queryId": null,
"unit": "Percentage"
},
{
"absoluteValue": 0.0078125,
"dimensionName": "IndexSize",
"queryId": null,
"unit": "MB"
}
],
"id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/tuningOptions/index/recommendations/recommendations/<recommendation_id>",
"implementationDetails": {
"method": "SQL",
"script": "DROP INDEX CONCURRENTLY \"public\".\"idx_dropindextable_c2\";"
},
"improvedQueryIds": null,
"initialRecommendedTime": "2026-01-27T19:02:47.556792+00:00",
"kind": "",
"lastRecommendedTime": "2026-01-27T19:02:47.556792+00:00",
"name": "DropIndex_<database>_<schema>_<index>",
"recommendationReason": "Duplicate of \"<index>\". The equivalent index \"<index>\" has a smaller oid compared to \"<index>\".",
"recommendationType": "DropIndex",
"resourceGroup": "<resource_group>",
"systemData": null,
"timesRecommended": 5,
"type": "Microsoft.DBforPostgreSQL/flexibleServers/tuningOptions/index"
},
{
.
.
.
}
]
若要列出所有 REINDEX 建议，请使用以下命令：
az postgres flexible-server autonomous-tuning list-index-recommendations \
--resource-group <resource_group> \
--server-name <server> \
--recommendation-type reindex
该命令返回所有关于自主调优生成的 REINDEX 建议的信息，输出类似于以下内容：
[
{
"analyzedWorkload": {
"endTime": "2026-01-27T19:02:47.522193+00:00",
"queryCount": 0,
"startTime": "2026-01-27T19:02:47.522193+00:00"
},
"currentState": "Active",
"details": {
"databaseName": "<database>",
"includedColumns": [
""
],
"indexColumns": [
"<column>"
],
"indexName": "<index>",
"indexType": "BTREE",
"schema": "<schema>",
"table": "<table>"
},
"estimatedImpact": [
{
"absoluteValue": 41.0,
"dimensionName": "Benefit",
"queryId": null,
"unit": "Percentage"
},
{
"absoluteValue": 0.0,
"dimensionName": "IndexSize",
"queryId": null,
"unit": "MB"
}
],
"id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/tuningOptions/index/recommendations/recommendations/<recommendation_id>",
"implementationDetails": {
"method": "SQL",
"script": "REINDEX INDEX CONCURRENTLY \"<schema>\".\"<schema>\";"
},
"improvedQueryIds": null,
"initialRecommendedTime": "2026-01-27T15:26:37.647505+00:00",
"kind": "",
"lastRecommendedTime": "2026-01-27T19:26:43.297535+00:00",
"name": "ReIndex_<database>_<schema>_<index>",
"recommendationReason": "The index is invalid and the recommended recovery method is to reindex.",
"recommendationType": "ReIndex",
"resourceGroup": "<resource_group>",
"systemData": null,
"timesRecommended": 5,
"type": "Microsoft.DBforPostgreSQL/flexibleServers/tuningOptions/index"
},
{
.
.
.
}
]
若要列出所有 ANALYZE 建议，请使用以下命令：
az postgres flexible-server autonomous-tuning list-table-recommendations \
--resource-group <resource_group> \
--server-name <server> \
--recommendation-type analyzetable
该命令返回由自主优化生成的 ANALYZE 推荐的所有信息，显示的输出类似于以下示例：
[
{
"analyzedWorkload": {
"endTime": "2026-01-27T19:02:47.522193+00:00",
"queryCount": 0,
"startTime": "2026-01-27T19:02:47.522193+00:00"
},
"currentState": null,
"details": {
"databaseName": "<database>",
"includedColumns": null,
"indexColumns": null,
"indexName": null,
"indexType": null,
"schema": "<schema>",
"table": "<table>"
},
"estimatedImpact": null,
"id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/tuningOptions/index/recommendations/recommendations/<recommendation_id>",
"implementationDetails": {
"method": "SQL",
"script": "ANALYZE \"<schema>\".\"<table>\";"
},
"improvedQueryIds": [
1574410013562407420
],
"initialRecommendedTime": "2026-01-27T17:26:40.825994+00:00",
"kind": "",
"lastRecommendedTime": "2026-01-27T17:26:40.825994+00:00",
"name": "Analyze_<database>_<schema>_<table>",
"recommendationReason": "Table \"<schema>\".\"<table>\" has not been analyzed but is being used by queries: \"1574410013562407420\".",
"recommendationType": "Analyze",
"resourceGroup": "<resource_group>",
"systemData": null,
"timesRecommended": 1,
"type": "Microsoft.DBforPostgreSQL/flexibleServers/tuningOptions/table"
},
{
.
.
.
}
]
若要列出所有 VACUUM 建议，请使用以下命令：
az postgres flexible-server autonomous-tuning list-table-recommendations \
--resource-group <resource_group> \
--server-name <server> \
--recommendation-type vacuumtable
该命令返回关于自动调优生成的 VACUUM 建议的所有信息，并显示出类似于以下输出的内容：
[
{
"analyzedWorkload": {
"endTime": "2026-01-27T17:26:40.102894+00:00",
"queryCount": 5,
"startTime": "2026-01-27T16:26:40.102895+00:00"
},
"currentState": null,
"details": {
"databaseName": "<database>",
"includedColumns": null,
"indexColumns": null,
"indexName": null,
"indexType": null,
"schema": "<schema>",
"table": "<table>"
},
"estimatedImpact": null,
"id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/tuningOptions/table/recommendations/<recommendation_id>",
"implementationDetails": {
"method": "SQL",
"script": "VACUUM \"<schema>\".\"<table>\";"
},
"improvedQueryIds": [
-2306335776078168728
],
"initialRecommendedTime": "2026-01-27T17:26:40.823239+00:00",
"kind": "",
"lastRecommendedTime": "2026-01-27T17:26:40.823239+00:00",
"name": "Vacuum_<database>_<schema>_<table>",
"recommendationReason": "Table \"<schema>\".\"<table>\" should be vacuumed. It has an estimated size of <table_size>GB and a bloat percentage of <bloat_percentage>% (bloat size represents <bloat_size>GB).",
"recommendationType": "Vacuum",
"resourceGroup": "<resource_group>",
"systemData": null,
"timesRecommended": 1,
"type": "Microsoft.DBforPostgreSQL/flexibleServers/tuningOptions/table"
},
{
.
.
.
}
]
使用您偏好的任何 PostgreSQL 客户端工具：
使用有权连接到实例的任何角色，连接到服务器中可用的 azure_sys 数据库。
public 角色的成员可以从这些视图中读取。
在 sessions 视图上执行查询，以检索有关建议会话的详细信息。
对 recommendations 视图执行查询，以检索通过针对 CREATE INDEX、DROP INDEX 和 REINDEX 进行自主优化生成的建议。
Views
数据库中的 azure_sys 视图提供了一种访问和检索通过自动调优生成的建议的便利方法。 具体而言， intelligentperformance.sessions 和 intelligentperformance.recommendations 视图包含有关 CREATE INDEX、DROP INDEX、REINDEX、ANALYZE 和 VACUUM 建议的详细信息。 这些视图公开详细信息，例如会话标识符、数据库名称、会话类型、优化会话的启动和停止时间、建议类型、生成建议的原因以及其他相关详细信息。 用户可以查询这些视图，以便轻松地访问和分析自动优化生成的建议。
sessions 视图展示所有索引调优会话的所有详细信息。
| 列名 |
数据类型 |
Description |
| 会话ID (session_id) |
UUID（通用唯一识别码） |
分配给启动的每个新调优会话的通用唯一标识符。 |
| 数据库名称 |
varchar（64） |
执行索引优化会话的上下文所属的数据库名称。 |
| 会话类型 |
intelligentperformance.recommendation_type |
指示此索引调优会话可能会生成的建议类型。 可能的值有：CreateIndex、DropIndex、Table。
CreateIndex 类型的会话可以生成 CreateIndex 类型的建议。
DropIndex 类型的会话可以生成 DropIndex 或 ReIndex 类型的建议。
Table 类型的会话可以生成 Analyze 或 Vacuum 类型的建议。 |
| 运行类型 |
智能性能.推荐运行类型 |
指示此会话的启动方式。 可能的值为：Scheduled 根据 index_tuning.analysis_interval 的值自动执行的会话被设置为 Scheduled。 |
| 状态 |
intelligentperformance.recommendation_state |
指示会话的当前状态。 可能的值有：Error、Success、InProgress。 执行失败的会话设置为 Error。 正确完成执行的会话，无论是否生成了建议，都设置为 Success。 仍在执行的会话设置为 InProgress. |
| 开始时间 |
不带时区的时间戳 |
生成此建议的调优会话启动时的时间戳。 |
| stop_time |
不带时区的时间戳 |
生成此建议的调优会话启动时的时间戳。 如果会话正在进行或由于某些故障而中止，则为 NULL。 |
| 推荐数量 |
整数 |
此会话中生成的建议总数。 |
recommendations 视图展示了在基础表中仍有可用数据的任何优化会话所生成的所有建议的详细信息。
| 列名 |
数据类型 |
Description |
| 推荐编号 |
整数 |
在整个服务器中唯一标识一条建议的数字。 |
| 当前状态 |
intelligentperformance.recommendation_current_state |
指示生成的建议的当前状态。 可能的值有：Active、Detected。 |
| 最后已知会话ID (last_known_session_id) |
UUID（通用唯一识别码） |
每个索引优化会话都分配有一个全局唯一标识符。 此列中的值表示最近生成此建议的会话的值。 |
| 最后已知会话类型 |
intelligentperformance.recommendation_type |
建议会话的类型，上次被推荐的地方。 可能的值有：CreateIndex、DropIndex、ReIndex。 |
| 数据库名称 |
varchar（64） |
在其上下文中生成建议的数据库的名称。 |
| 推荐类型 |
intelligentperformance.recommendation_type |
指示生成的建议的类型。 可能的值包括：CreateIndex、DropIndex、ReIndex、AnalyzeTable、VacuumTable。 |
| 初始推荐时间 |
不带时区的时间戳 |
生成此建议的调优会话启动时的时间戳。 |
| last_recommended_time |
不带时区的时间戳 |
生成此建议的调优会话启动时的时间戳。 |
| times_recommended |
整数 |
生成此建议的调优会话启动时的时间戳。 |
| 原因 |
文本消息 |
证明生成此建议的原因的理由。 |
| recommendation_context |
json |
包含受建议影响的查询的查询标识符列表、建议的索引类型、建议的架构名称和建议索引所在的表的名称、索引列、索引名称以及建议索引的估计大小（以字节为单位）。 |
CREATE INDEX 建议的原因
当自治优化建议创建索引时，它至少会添加以下其中一个原因：
| 原因 |
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> |
REINDEX 建议的原因
当自治优化发现任何索引被标记为无效时，建议基于以下原因重新索引它们：
The index is invalid and the recommended recovery method is to reindex.
要了解有关索引标记为无效的原因和时间的更多信息，请参阅 PostgreSQL 官方文档中的 REINDEX。
删除索引建议的原因
当自主调优检测到一个索引在至少index_tuning.unused_min_period天内未被使用时，它会建议删除该索引，原因如下：
The index is unused in the past <days_unused> days.
当自主调优检测到重复索引时，其中一个重复索引将被保留，并建议删除其余的索引。 所提供的原因总是有如下起始文本：
Duplicate of <surviving_duplicate>.
紧接着是另一段文本，说明选择一个重复项进行删除的原因：
| 原因 |
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>". |
如果索引不仅由于重复而可删除，而且至少在 index_tuning.unused_min_period 中设置的天数内未被使用，那么以下文本会附加到原因之后：
Also, the index is unused in the past <days_unused> days.
ANALYZE 建议的原因
当自动调优检测到所研究的查询中引用了某个表格，并确定该表格从未被分析时，它建议对该表格运行 ANALYZE，原因如下：
Table "<schema>"."<table>" has not been analyzed but is being used by queries: "<queryId-1>, ..., <queryId-n>"
当自动调优检测到研究的某个查询中引用的表，并确定该表曾经被分析过，但当前没有统计信息（在统计信息保存之前服务器发生崩溃时），建议对该表运行 ANALYZE，原因如下：
Table "<schema>"."<table>" lacks statistics, has more than <liveRows> rows, and is used by queries: "<queryId-1>, ..., <queryId-n>"
VACUUM 建议的原因
当自治优化检测到在所研究的查询中引用的一个表，并确定该表膨胀，而 autovacuum_enabled 在服务器级别未设置为 off 时， 它建议基于以下基本理由对该表运行 VACUUM。
Table "<schema>"."<table>" should be vacuumed. It has an estimated size of <estimatedSize>GB and a bloat percentage of <bloatPercentage>% (bloat size represents <bloatSize>GB).
如果在服务器和表级别启用 autovacuum，则以下文本将追加到基本原因：
Autovacuum is enabled at both the server and table level, but appears to be falling behind.
如果在表级别禁用 autovacuum，则会将以下文本追加到基本原因：
Autovacuum is disabled at the table level.
