在 PostgreSQL 中创建索引时，为每个列创建一个索引更好，还是为 WHERE 子句的所有列创建一个复合索引更好？

复合索引和多个索引

关于索引的使用，有一个最常见问题：是为每个列创建一个索引更好，还是为WHERE子句的所有列创建一个索引更好？在大多数情况下，答案非常简单：一个包含多个列的索引会更好，即多列索引或复合索引。”多列索引“ 的文档中有详细的解释。

然而，无论您如何定义索引，都存在单个索引无法完美完成的查询；例如，带有两个或多个独立范围条件的查询，如下例所示：

SELECT first_name, last_name, date_of_birth
  FROM employees
 WHERE UPPER(last_name) < ?
   AND date_of_birth    < ?

在没有过滤谓词的情况下，不可能定义出支持此查询的 B 树索引。为了解释，你只需要记住一个索引就是一个链表。

如果将索引定义为UPPER(LAST_NAME), DATE_OF_BIRTH（按此顺序），则链表以 A 开头，以 Z 结尾。仅当有两名员工同名时，才会考虑出生日期。如果以DATE_OF_BIRTH, UPPER(LAST_NAME)的方式定义索引，它将从最年长的员工开始，以最年轻的员工结束。在这种情况下，名称对排序顺序的影响很小。

无论您如何扭转和调整索引的定义，条目始终沿一条链排列。小的条目在一端，大的条目在另一端。因此，一个索引只能支持一个范围条件作为访问谓词。支持两个独立的范围条件需要第二个轴线，比如像一个棋盘。然后，上面的查询将匹配来自棋盘一角的所有条目，但索引不像一个棋盘 — 它就像一条链。没有角落。

当然，您可以接受过滤谓词，并使用多列索引。不管怎样，在许多情况下，这是最好的解决方案。然后，索引的定义应该首先提及选择率更高的列，以便它可以同访问谓词一起使用。每次创建一个复合索引时，都必须明智地选择列的顺序。但是，有一种误解是，您应该始终将选择率最高的列放在第一个位置；那是错误的。

重要：在定义一个复合索引时，最重要的考虑点是如何选择列的顺序，以便可以尽可能频繁地使用它。

另一种选择是使用两个单独的索引，每个列一个。然后，数据库必须首先扫描这两个索引，然后合并结果。只是重复的索引查找，就已经涉及更多的工作了，因为数据库必须遍历两个索引树。此外，数据库需要大量内存和 CPU 时间，来组合中间结果。

注意：一个索引的扫描比两个索引的扫描要快。

组合多个索引

PostgreSQL 能够组合多个索引，来处理单个索引扫描无法实现的情况。“组合多个索引” 的文档中详细解释了相关的算法。

在一个数据仓库的世界中，会有许多不可预测的临时查询。只需单击几下，即可将任意条件组合到您选择的查询中。无法预测出WHERE子句中可能出现的列的组合，这使得索引的使用几乎不可能。

多个索引的优点是，它们可以很容易地组合。这意味着在单独地索引每个列时，您可以获得不错的性能。相反，如果您提前知道查询，以便您可以创建定制的多列 B 树索引，则它仍然会比组合多个索引更快。

在没有更好的访问路径的情况下，PostgreSQL 会将多个 B 树索引扫描的结果转换为内存中的位图结构。这些结果可以高效地组合起来。位图结构不是持久化存储的，而会在语句执行后被丢弃，从而避免了写数据时扩展性差的问题。缺点是它需要大量的内存和 CPU 时间。毕竟，这种方法也算是优化器最后的一种选择了。