Is there any harm to having a duplicate index in Postgresql?

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.

Clearly index_on_col_a is no longer needed, but is there a cost or benefit to keeping or deleting it?

My guess is;

• keeping it will slow down inserts
• selects using just col_a may be faster if I keep it

Answer 1

You can drop the index on col_a. PostgreSQL is able to use the combined index if you query on col_a and is also able to use the index if you query on col_a and col_b. These query types can use the 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 an OR junction of col_a and col_b. So the additional index over col_b can make sense if you frequently have queries querying only col_b.

Edit: So: you don't have an advantage creating index_on_col_a, but you have a slower write speed. Drop it.