2 PostgreSQL indices on the same column of the same table - redundant?

Question

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?

Answer 1

It depends.

Assuming we are talking about default B-Tree indexes only. If other index types like GIN or GiST are involved, things are not as simple.

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

You would have to consider the size of the table, available RAM, typical queries, the involved data types, the size of the index, overhead per tuple and size of data, data alignment and padding ... or just run tests with your actual data and queries (but careful what you are testing really).

For example if a and b are no bigger than 4 bytes (integer, smallint, date, ...) the index on (a,b) is exactly as big as the one on just (a) and there is no point whatsoever to keep the second.

A more detailed answer on dba.SE for this case exactly.

The manual for the current version of Postgres is always a good source for more detailed information.

Answer 2

postgres multicolumn indexes can be used to search on the first columns only,so in practise it is redundant.

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.

Postgres 9.2 documentation

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.

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