2 PostgreSQL索引在同一表的同一列 - 多余？ [英] 2 PostgreSQL indices on the same column of the same table - redundant?
问题描述
我有一个PostgreSQL表有2个索引。其中一个索引是 website_id 和 tweet_id 列，是唯一的B树索引。
第二个索引仅涵盖 website_id 列，并且是非唯一索引。
I have a PostgreSQL table with 2 indices. One of the indices is covers the website_id and tweet_id columns and is an unique B-tree index.
The 2nd index only covers the website_id column, and is a non-unique index.
如果第一个索引存在，第二个索引冗余？换句话说，拥有第二个索引没有优势。
Is the 2nd index redundant if the first index exists? In other words, will there be no advantages to having the 2nd index?
推荐答案
postgres多列索引可用于搜索第一列，所以在实践中它是多余的。
postgres multicolumn indexes can be used to search on the first columns only,so in practise it is redundant.
多列B树索引可用于涉及索引列的任何子集的查询条件，但索引最有效在最前面（最左边）列有约束。精确的规则是，对引导列的等式约束加上不具有等式约束的第一列上的任何不等式约束将用于限制被扫描的索引的部分。
A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned.
有一个远程的情况下，其他索引可能是有用的（见下面更详细的东西）如果你在第一个索引上做大部分查询，并且有一个非常小的缓存可用于索引。在这种情况下，组合索引可能不适合缓存，但是较小的单列。
there is a remote case where the other index might be useful (see below for more detailed stuff) ie. If you do most of your queries on the first index and have a very small cache available for the indexes. In this case the combined index might not fit the cache , but the smaller single column one would.
这篇关于2 PostgreSQL索引在同一表的同一列 - 多余？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
