2 PostgreSQL indexes in one column of one table - redundant?

Question

I have a PostgreSQL table with two indexes. One of the indexes covers the columns of website_id and tweet_id and is a unique B-tree index. The second index covers only the website_id column and is a non-unique index.

Is the second index redundant if the first index exists? In other words, would there be any benefit of having a second index?

Answer 1

Postgres multi-column indexes can be used to search only the first columns, so in practice it is redundant.

The B-tree multi-column index can be used with query conditions that include any subset of the index columns, but the index is most effective when there are restrictions on the leading (leftmost) columns. The exact rule is that constraint constraints for leading columns plus any inequality constraints in the first column that do not have equality constraints will be used to constrain part of the index being checked.

Postgres 9.2 Documentation

there is a remote case where another index may be useful (see below for more details), i.e. If you fulfill most of your queries on the first index and have a very small cache for indexes. In this case, the combined index may not match the cache, but a smaller separate column.

https://dba.stackexchange.com/questions/27481/is-a-composite-index-also-good-for-queries-on-the-first-field/27493#27493

Answer 2

It depends.

Assuming we are talking only about default B-Tree indices. If other types of indexes, such as GIN or GiST , are not so simple.

In principle, an index on (a,b) is good for searches only on a , and another index only (a) not needed. ( But an additional index only for (b) generally makes sense! )
It may still be a good idea if column b large, so that only (a) index is substantially smaller.

You will need to consider the table size, available RAM, typical queries, involved data types, index size, overhead for each tuple and data size, data alignment and addition ... or just run tests with your actual data and queries (but carefully check what you are actually testing).

For example, if a and b at most 4 bytes ( integer , smallint , date , ...), the index on (a,b) will be as large as on just (a) , and there is no point in holding the second.

A more detailed answer to dba.SE for this case.

The guide for the current version of Postgres is always a good source for more details.

Answer 3

Yes, this is (redundant).

Compound index behavior is common not only for Postgres, but also for most other RDBMSs.