Is there any harm if Postgresql has a duplicate index?

Question

I have the following structure.

CREATE TABLE join_table ( id integer NOT NULL, col_a integer NOT NULL, col_b integer NOT NULL ) CREATE INDEX index_on_col_a ON join_table USING btree (col_a); CREATE INDEX index_on_col_b ON join_table USING btree (col_b); CREATE UNIQUE INDEX index_on_col_a_and_col_b ON join_table USING btree (col_a, col_b); 

There are also foreign keys on col_a and col_b.

It is clear that index_on_col_a no longer required, but is there a save or delete?

My suggestion:

• saving will slow insertion
• a selection using only col_a might be faster if I save it

Answer 1

You can drop the index on col_a . PostgreSQL can use a combined index if you request col_a , and you can also use an index if you request col_a and col_b . These types of queries can use a combined index:

 WHERE col_a = 'val' WHERE col_a = 'val' AND col_b = 'val' 

The combined index cannot be used to query only col_b or OR transition col_a and col_b . Thus, an extra index over col_b might make sense if you often request only col_b .

Edit: So: you have no advantage in creating index_on_col_a , but you have a lower write speed. Throw it.