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?

You did not mention the type of the second index. Actually, you would need to post the table definition including relevant columns and index definitions to get a definitive answer - use `\d tbl` in psql. — Erwin Brandstetter, Jul 05 '13 at 01:13

score 9 · Accepted Answer · edited Apr 13 '17 at 12:42

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

score 6 · Answer 2 · edited May 23 '17 at 10:30

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.

score 0 · Answer 3 · answered Jul 04 '13 at 23:58

0

Yes, it is (redundant).

The compound index behavior is common not only to Postgres but to pretty much any other RDBMS.

answered Jul 04 '13 at 23:58

Alix Axel

151,645
95
393
500

1

In this case, yes it's redundant. But an important point is that if the question were asking if another index on `tweet_id` is redundant the answer would be no based on multicolumn index ordering. – Ryan Weir Jul 05 '13 at 00:06
well according to that postgresql manual bit i cutpasted, it seems to be somewhat redundant for tweet_id's too, but less efficient. – Markus Mikkolainen Jul 05 '13 at 00:10
and mysql will probably just do a harakiri if you try this. – Markus Mikkolainen Jul 05 '13 at 00:11
I was refering only to the leftmost indexes, didn't even knew pgsql could skip leading indexes! – Alix Axel Jul 05 '13 at 00:31

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

3 Answers3

Linked