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?
In PostgreSQL, you can create multiple indexes for a single column.
For example, if you have an index on column {a} or columns {a,b}, you can't create another index on the same column or set of columns in the same order. In 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different.
The two types of indexes are single-column indexes and multicolumn indexes. A single-column index is an index based on the values in one column of a table. A multicolumn index is an index based on the values in multiple columns of a table.
Indexes can have up to 32 columns, including INCLUDE columns. (This limit can be altered when building PostgreSQL.) Only B-tree currently supports unique indexes. An operator class with optional parameters can be specified for each column of an index.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With