具有三个键的复合索引，如果我查询跳过中间一个键会怎样?

问题描述

对于PostgreSQL，我想在三列A, B, C上使用复合索引. B是created_at日期时间，有时我可能会在没有B的情况下进行查询.

With PostgreSQL, I want to use a compound index on three columns A, B, C. B is the created_at datetime, and occasionally I might query without B.

如果我复合了(A, B, C)上的索引，但随后又查询了A和C而不是B的条件，会发生什么情况? (即A和C，但要在所有时间内都使用它，而不仅是在特定时间范围内?)

What happens if I compound index on (A, B, C) but then query with conditions on A and C, but not B? (That is, A and C but want it over all time, not just some specific time range?)

Postgres是否足够聪明，仍然可以使用(A, B, C)复合索引，而只是跳过B?

Is Postgres smart enough to still use the (A, B, C) compound index but just skip B?

推荐答案

Postgres可以在b树索引中使用非前导列，但效率要低得多.

Postgres can use non-leading columns in a b-tree index, but in a far less efficient mode.

如果第一列具有很高的选择性(每个A仅几行)，那么您几乎不会注意到性能上的差异，因为任何一种访问方法(甚至是对简化集合的顺序扫描)都便宜.性能影响随着每个A的行数而增加.

If the first column is very selective (only few rows per A) then you will hardly notice a difference in performance since either access method (even a sequential scan over the reduced set) is cheap. The performance hit grows with the number of rows per A.

对于您所描述的情况，我建议在 (A, C, B) 或(C, A, B)(只需确保B排在最后)上创建索引以优化性能.这样，您可以在(A, B, C)和(A, C)上的查询中获得最佳性能.

For the case you describe I suggest to create the index on (A, C, B) or (C, A, B) (just make sure that B comes last) to optimize performance. This way you get best performance for queries on (A, B, C) and on (A, C) alike.

与索引中的列顺序不同，查询中的谓词顺序无关紧要.

Unlike the sequence of columns in the index, the sequence of predicates in the query does not matter.

我们已经在dba.SE上对此进行了详细讨论:

We have discussed this in great detail on dba.SE:

• 在PostgreSQL中使用索引

请注意，对于您遇到的情况，您是否领先A, C或C, A都无关紧要 :

Note that it does not matter whether you lead with A, C or C, A for the case at hand:

• 多列索引和性能

还有其他一些注意事项，但是您的问题并没有所有相关的详细信息.

There are also some other considerations, but your question does not have all the relevant details.

• 复合索引对第一字段的查询也有用吗?

这篇关于具有三个键的复合索引，如果我查询跳过中间一个键会怎样?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！