多列索引与独立索引与部分索引

Question

今天在我的Rails应用程序上工作时，我注意到偏执狂gem说索引应该更新，以添加deleted_at IS NOT NULL作为索引创建(github链接)的where。但我突然意识到，当我想要with_deleted的时候，倒置的情况不会从指数中受益。

这让我好奇..。

我知道这有点迟钝，因为答案显然是“这取决于您需要什么”，但我正在尝试了解PostgreSQL支持的web应用程序中多列索引与单独索引与部分索引之间的差异。

基本上，我有两个正在查询的字段: p_id和deleted_at。大多数时候我是在查询WHERE p_id=1 AND deleted_at IS NOT NULL --但有时我只查询WHERE p_id=1。很少，我会WHERE p_id=1 AND deleted_at=1/1/2017。

所以，我最好还是：

1. 在p_id上有索引，在deleted_at上有单独的索引？
2. 在p_id上有一个索引，但添加“deleted_at不是空的地方”？
3. 在p_id和deleted_at上有一个组合索引吗？

注意:也许我应该提到，p_id目前是对p.id的外键引用。这提醒我，在Postgres中，外键是否有必要也有索引(或者它们是否获得了来自外键约束的索引--我在这方面看到了相互矛盾的答案)？

Answer 1

答案取决于

• 使用这些查询的频率，以及允许它们运行多长时间
• 如果查询速度足够重要，可以容忍缓慢的数据更改。

这三个条款的最佳索引是：

1. WHERE p_id=1 AND deleted_at IS NOT NULL 在mytable (p_id)上创建索引，其中deleted_at不是NULL；
2. WHERE p_id=1 AND deleted_at=1/1/2017 在mytable上创建索引(p_id，deleted_at)；
3. WHERE p_id=1 创建mytable索引(p_id)；

为2创建的索引也可以用于3。因此，如果您需要尽可能加快第二个查询的速度，并且稍微大一点的索引不会困扰您，那么只从2为这两个查询创建索引。

但是，从3开始的索引也会在2.中加快查询速度，而不是尽可能地提高查询速度，所以如果您可以忍受2中查询的性能稍差，并且希望在3中的查询中索引尽可能小和有效，那么只在3中创建索引。

我不会在2.an3中创建这两个索引；您应该选择对您最合适的索引。

1的情况不同，因为该索引只能用于第一个查询。只有在希望尽可能加快查询速度的情况下才创建该索引，如果表上的数据修改需要更长的时间，这并不重要，因为必须维护一个额外的索引。

在1中创建索引的另一个指示是，如果只有一小部分行满足deleted_at IS NOT NULL。如果不是，1中的索引与3中的索引相比没有太大的优势，您应该创建后者。

在这两列上有两个单独的索引可能不是最好的选择--它们只能与位图索引扫描结合使用，而且很可能PostgreSQL只选择使用其中的一个索引(取决于分布，但可能是p_id上的一个)，而另一个没有用。