在本文中，我们来看看在PostgreSQL中设计索引时要考虑的要点，这可能是一个决定性因素。
这就是PostgreSQL的官方文档对索引的定义，非常简单明了——“索引是提高数据库性能的常用方法。索引允许数据库服务器查找和检索特定行的速度比没有索引的速度快得多。但是索引也会增加整个数据库系统的开销，因此应该合理地使用它们。
在这个定义中，最后一个词“明智地”是本文的关键。索引很好，它们可以增强查询的性能。这并不意味着我们为每个查询和每个列创建索引。请务必记住，虽然索引确实可以提高性能，但它们确实需要维护，这是开销。
在使用PostgreSQL性能时，以下是我为PostgreSQL索引发现的一些重要要点，这些要点将有助于设计增强性能的索引，其中性能增益超过维护开销：
1. 将新的/修改的索引部署到生产环境时，请使用“并发”选项和“创建索引”命令。这将允许对数据库的“写入”无缝进行。在某些情况下可以使用此命令，并且在部署期间也需要密切监视，因为部署失败可能会导致需要手动删除的无效索引。
2. 定义中包含多列的索引必须尽可能少用。PostgreSQL Planner使用“前导”列作为主要“过滤标准”的索引，因此定义中其他列的存在虽然将用于不平等比较或获取数据，但主要是维护开销而不是性能优势。
3. 我们可以在索引定义中使用“WHERE 子句”设计索引，称为部分索引。节省空间和时间，但需要非常小心地使用，只有当我们绝对确定条件将直接或算术连接到查询时，否则索引可能会成为维护开销，而没有性能优势。
4. 如果您的工作负载包括有时仅涉及 x 列、有时仅涉及 y 列以及有时同时涉及 x 和 y 的查询组合，则可以选择在 x 和 y 上创建两个单独的索引，依靠索引组合来处理使用这两列的查询。在上述方案中，这将比创建具有 x 和 y 的多列索引更好。
5. 我们可以在列的表达式上创建索引，例如（下 （col1）） ;(( first_name ||' ' ||last_name））、等。索引表达式的维护成本相对较高，因为必须在插入时以及更新时为每一行计算派生表达式。当检索速度比插入和更新速度更重要时，表达式上的索引非常有用。
6. PostgreSQL 支持仅索引扫描，它可以单独回答来自索引的查询，而无需随机堆访问，因为它主要是一个 B 树索引，并且查询必须仅引用存储在索引中的列。仅当表的堆页的很大一部分设置了其所有可见的映射位时，这才是胜利。但是，其中大部分行不变的表很常见，因此这种类型的扫描在实践中非常有用。
7. 若要有效利用仅索引扫描功能，可以选择创建覆盖索引，该索引是专门设计用于包含经常运行的特定查询类型所需的列的索引。由于查询通常需要检索更多的列，而不仅仅是它们搜索的列，PostgreSQL 允许您创建一个索引，其中某些列只是“有效负载”，而不是搜索键的一部分。这是通过添加列出额外列的 INCLUDE 子句来完成的。
8. 在将非键有效负载列添加到索引（尤其是宽列）时保持保守是明智的。如果索引元组超过索引类型允许的最大大小，则数据插入将失败。在任何情况下，非键列都会复制索引表中的数据并膨胀索引的大小，从而可能减慢搜索速度。请记住，在索引中包含有效负载列几乎没有意义，除非表更改得足够慢，以至于仅索引扫描可能不需要访问堆。如果无论如何都必须访问堆元组，则从那里获取列的值不会花费更多。
9. 后缀截断从上层 B 树级别删除非键列。作为有效负载列，它们从不用于指导索引扫描。当键列的剩余前缀恰好足以描述最低 B 树级别的元组时，截断过程还会删除一个或多个尾随键列。实际上，在没有 INCLUDE 子句的情况下覆盖索引通常会避免在上层存储有效负载的列。但是，将有效负载列显式定义为非键列可以可靠地保持上层中的元组较小。
10. 原则上，仅索引扫描可以与表达式索引一起使用。然而，PostgreSQL的规划者目前对这种情况不是很聪明。仅当查询所需的所有列都可以从索引中获取时，它才会认为查询可能通过仅索引扫描执行。例如，对于在 f（x） 上进行搜索的查询，除了在上下文 f（x） 中之外，不需要 x，但计划人员没有注意到这一点，并得出结论，仅索引扫描是不可能的。如果仅索引扫描看起来足够值得，可以通过添加 x 作为包含的列来解决此问题。部分索引还支持仅索引扫描。
11. 一个索引只能支持每个索引列的一个排序规则。如果对多个排序规则感兴趣，则可能需要多个索引。索引自动使用基础列的排序规则。
12. 在检查索引使用情况之前，请始终先运行 ANALYZE。此命令收集有关表中值分布的统计信息。此信息是估计查询返回的行数所必需的，计划器需要此信息为每个可能的查询计划分配实际成本。在没有任何实际统计数据的情况下，假设一些默认值，这些默认值几乎肯定是不准确的。
13. 使用非常小的测试数据集来检查索引使用情况是致命的。虽然从 1，000 行中选择 100，000 行可以作为索引的候选者，但从 1 行中选择 100 行几乎是不可能的，因为 100 行可能适合单个磁盘页，并且没有计划可以击败按顺序获取 1 个磁盘页。
请评估为上述每点设计的每个指数，并继续进行明智的指数设计过程。
另外，我确实需要指出这一点，对于从SQL Server迁移到PostgreSQL，请不要按原样将索引从SQL Server迁移到PostgreSQL。两种索引软件的架构非常非常不同。因此，在迁移到PostgreSQL时，请重新评估和重新设计索引。这将是一项努力，但值得你花时间。
