PostgreSQL中为何允许按不同顺序索引同一组列？条件顺序影响检索速度吗？

首先明确：WHERE/ON子句中的条件书写顺序不会影响检索速度。PostgreSQL的查询优化器会自动分析条件的逻辑关系，不管你写WHERE x = ? AND y = ?还是WHERE y = ? AND x = ?，优化器都会将其视为同一个查询条件集合，然后选择最合适的索引执行查询。

那为什么允许创建(x,y)和(y,x)这两个不同顺序的复合索引？因为它们的适用场景完全不同，核心在于B树复合索引的前缀匹配特性：

• 对于index_1 (x, y)：

  • 高效支持以x为前缀的过滤或排序：比如WHERE x = ?、WHERE x = ? AND y = ?，或者ORDER BY x, y
  • 如果查询只涉及y的过滤（比如WHERE y = ?），这个索引几乎没用，因为无法利用前缀匹配

• 对于index_2 (y, x)：

  • 高效支持以y为前缀的过滤或排序：比如WHERE y = ?、WHERE y = ? AND x = ?，或者ORDER BY y, x
  • 如果查询只涉及x的过滤，这个索引同样无法高效利用

举个具体例子：
如果你的查询是SELECT * FROM A WHERE y = 100 ORDER BY x，那么index_2 (y, x)可以直接通过y=100定位到对应的索引条目，这些条目在索引里已经按x排好序了，不需要额外排序操作；而index_1 (x, y)则无法直接利用，只能全表扫描或者扫描整个索引后再筛选排序，性能差距明显。

总结来说：

• 查询条件的书写顺序不影响性能，优化器会自动处理
• 复合索引的列顺序决定了它能高效支持的查询模式，不同顺序的索引服务于不同的查询场景，因此PostgreSQL允许创建这两种索引

内容的提问来源于stack exchange，提问作者Seán Healy