从历史上看，索引的创建、调优和运维一直是数据库管理员的工作，他们使用对不同的应用程序来进行不同的查询有一个详细的了解。现代应用程序发展的快速与云上转移的结合，已经将索引的责任转到了应用程序的开发人员身上——但没有为他们提供正确的工具。
应用程序的开发人员如今投入了大量的时间来为他们的Postgres查询手动创建索引，审查数据库统计信息来找到未被使用的索引，以及针对其查询工作负载对整个索引集进行微调。这就使人们忽略了真正重要的东西：构建高质量的软件和开发新的应用程序功能。
今天，我们兴奋地宣布pganalyze Index Advisor 的下一个革新，在新的pganalyze Indexing Engine（索引引擎）基础上，能够为你的整个数据库推荐跨查询的索引。作为新的Indexing Engine的一部分，pganalyze 能够预测 Postgres 查询规划器将如何评估一组多个查询，然后尝试数百个索引组合，并且能够使用其独特的“What if”分析对缺失的索引提出建议，使得在该过程中的生产数据库的开销为零。
由于在早期访问中我们与客户建立的持续合作，我们能够使用真实世界的数据在新的 pganalyze Index Advisor 上进行测试和迭代。
“Index Advisor监视我们的数据库，来找寻缺失的索引，并且通知我们关于性能优化的新机会，而不需要我们去手动测试数百个查询。``自从我们开始使用 pganalyze Index Advisor，我们能够更容易地发现索引缺失的机会，从而得到一些在性能上的有价值的提高。”``Gareth Warwick at Auto Trader UK
让我们仔细看看：
pganalyze Indexing Engine的介绍
新的Index Advisor 通过新pganalyze Indexing Engine来为Postgre提供动力。当为你的数据库进行索引优化时，我们应当遵循以下三个步骤：
1、将查询工作负载分解为每个表的单个扫描
2、通过 pganalyze Index Engine评估每个表，来确定最佳索引配置
3、使用 pganalyze Index Advisor 从 pganalyze Index Engine获得的外观建议以及界面中的查询和表统计信息
Pganalyze Index Engine是建立在我们独特的“What if”分析之上，可以快速评估潜在的索引，以及它们是如何影响查询工作量的，更多信息可以参见我们的创始人Lukas Fittl编撰的的技术博客文章：Postgres 的自动索引系统：我们如何构建 pganalyze Index Engine，以及查看我们的 pganalyze Index Engine文档。
所有这些都完全在 pganalyze 应用程序中运行，与生产数据库分离。你不需要安装任何特殊的扩展，我们可以在 Postgres 表统计数据的基础上进行分析，而不需要访问表数据。
接下来，让我们看看新的 pganalyze Index Advisor 是如何接受来自 pganalyze Index Engine的建议的:
pganalyze Index Adivisor的下一个革新
新的 Index Advisor 背后的动机是帮助你高效地理解和推理 Postgres 查询工作负载和它的索引机会。我们的目标是当涉及到表面性能改善的机会时，能给你提供一个服务，来为你解决很多繁重工作。在可能的情况下，我们将尽可能多地为你服务，使得通过提供关于 Postgres 特定信息的内容和教授来指导你找到解决方案是有意义的。
让我们看一下我们在 pganalyze Index Advisor 中提供的一组特性。
了解索引机会的概况
索引丢失的可能性概述
我们将向你的数据库中引入一个新的视图，该视图将重点放在为你找出丢失和未使用的索引可能性上。在默认情况下，我们根据对数据库的整体影响进行机会排序。你也可以根据其他属性进行排序，这取决于你正在寻找的可能性的类型。因此，你不再需要浏览查询页来查询索引可能性，当然你也可以这样做。
与之前的 Index Advisor 相比，我们的新逻辑架构极大地降低了噪音，为了为每个数据库提供大量的机会，我们不再单独分析单个查询，而是在我们的建议中引入了数据库统计数据和已有的索引。
了解数据库的Index Write Overhead
了解使用 Index Write Overhead 度量维护现有索引和建议索引的成本
为了表示出维护现有索引和建议索引的成本，我们定义了 Index Write Overhead 度量来估计维护该索引的 I/O 影响。这个指标可以指导你合并和删除很少使用的索引，以获得更大的 I/O 空间。
我们计算了在数据库中维护索引的估计值，作为对表的写操作的一部分。具体来说，对于每个写入该数据库中的表的字节，都会有这么多字节写入其索引。下面是如何计算 Index Write Overhead 的示例。假设我们有一个表和一个索引。
customer_organization_id_idx:
首先计算平均表行和索引行的大小:
索引写开销仅仅是预期的平均索引行大小除以预期的平均表行大小，在本例中为“0.14”(12.0/85.0)。你可以将其视为“对于1字节的表数据，我们将向索引写入0.14字节”。
我们还应用基于 HOT 更新的额外校正因子和索引选择性，这里没有显示。我们目前没有考虑其它细节，比如 B 树索引中上部页面的大小(通常是整个索引的几%) ，或者其他优化，比如 Postgres 13中添加的 B 树重复数据增加。
当 Index Write Overhead 显示在 Index Advisor 概述中时，它被总结为所有表的加权平均数，以每分钟平均表写入量为权重。这样，当数据库上发生单个表写操作时，就会产生“典型”的索引写开销。
你可能想知道我们如何计算这些类型的估计。我们在之前的博客文章中提到了这一点，在那里我们解释了如何解构 Postgres 规划程序来寻找索引机会。
聚合表扫描
pganalyze 索引顾问业务机会页面上的扫描列表
在特定索引机会以及表和查询页面上，你将看到一个名为“扫描”的新部分。每个扫描都有一个关联的估计成本，该成本在引用它的所有查询中取平均值。有关其工作原理的更多信息，请阅读此处的文档。
下面介绍了如何解释在Index Advisor可能页上的“扫描”列表中看到的内容：
每个扫描表达式都包含以下信息
- 当前成本，
- 估计每分钟扫描，
- 以及加权成本改进（如果您要应用建议）。
成本改进表示为乘数，例如，比旧的估计成本快 1.2 倍 - 该表按成本改进排序。
如果你一直在使用索引检查功能来查找查询未使用的索引，我们会将其包含在查询页上的新索引顾问中。
错失索引可能性的原因
pganalyze 索引顾问上缺少索引问题页面
使用缺失的索引问题页面信息来解释和测试我们的建议。我们共享会受到可能性影响的扫描和查询，因此你可以在生产环境的副本中针对它们进行基准测试。
在Index Advisor建议过程中，将尝试索引的不同变体，以查看哪些变体产生的成本最低（因此估计的 I/O 也最低）。当Index Advisor找到建议时，它将显示预期的成本改进。
成本改进是可以从查询中受益的每个扫描的单个成本改进的平均值，它按扫描频率（基于包含扫描的查询）进行加权。更频繁的查询将对整体加权成本改进产生更显著的影响。
通过像这样使数据可访问，我们希望你的开发团队中的任何人都可以提出是否添加索引的理由。如上所述，我们的目标是提供背景和教育，以帮助你解释关于建议方面的问题，并让你有足够的信心采取行动。
我们希望你的开发团队中的任何人能够提出是否添加索引的理由。
正如我们在之前的一篇博客文章中所发表的，我们的核心设计原则之一是讲故事。你可以查看这篇博文，来了解我们所有的设计原则。
为Index Advisor设置警报
Index Advisor与我们的警报和检查功能集成，以便你可以为新的索引可能设置警报规则。此外，你可以使用我们的 Slack 集成向团队工作的地方发送警报。通过使用 pganalyze Index Advisor，你可以专注于开发你的应用程序——当你有新的想法时，我们会通知您。
指数顾问的下一步是什么？
随着这次发布，我们在 pganalyze Index Advisor上的工作并没有结束，在管道中已经有很多事件，例如索引合并建议和解决已知限制。
结论
我们很高兴看到这将帮助你制定索引策略并促进与你的团队的对话。所以现在就试一试，让我们知道你的想法。
我们很乐意你能通过电子邮件或参加我们下一次关于 Postgres 索引的网络研讨会，给与我们任何想法或反馈，以获取有关 Postgres 和Index Advisor如何工作的更多信息。
备注：我们很快就会在pganalyze招聘新的工程团队成员。在推特上关注我们以了解更多信息。
分享这篇文章：点击这里在推特上与你的同行分享这篇文章。
在我们的网络研讨会重播中查看索引引擎和Index Advisor的幕后花絮
2022 年 6 月 16 日，我们举办了一场网络研讨会，介绍了我们创建最佳 Postgres 索引的方法，以及我们在新的 pganalyze 索引引擎背后的想法。
你可以点击此处链接来观看网络研讨会：网络研讨会重新运行：如何推理索引你的 Postgres 数据库。
原文标题：A balanced approach to automatic Postgres indexing: The new version of the pganalyze Index Advisor
原文作者：Jens Nikolaus
原文链接：https://pganalyze.com/blog/automatic-postgres-indexing-balanced-approach