Check for duplicate indexes

[mkindahl]

Check if there are duplicate indexes for a table, that is, two different indexes that are for the same relation and have the same set of key columns.

[mkindahl]

This query will check for exactly the same set of columns as keys:

SELECT indrelid::regclass as relation,
       a.indexrelid::regclass as index1,
       b.indexrelid::regclass as index2
  FROM pg_index a JOIN pg_index b USING (indrelid, indkey)
 WHERE a.indexrelid != b.indexrelid;

It might be interesting to check if one index is redundant in the sense that there is another index that has the same set of initial columns. Something like:

CREATE TABLE foo (a int, b int, c int);
CREATE INDEX ON foo(a,b);
CREATE INDEX ON foo(a,b,c);

There seems to be a few customer cases with this kind of situation, especially if the customer creates a covering index but forget to remove the old index.