Goal Find indexes that are identical, i.e., have the same properties, including uniqueness. The query considers all types of indexes, including indexes that have been automatically created to support a constraint and function-based indexes.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability High Few or no false-positive results
License MIT (opens in new tab)
Fixing Suggestion Drop the duplicates and leave only one in place.
Data Source system catalog only
SQL Query 
SELECT trim(substring(indexdef, '(^([[:upper:]]| )+)')) || ' ' || regexp_replace(indexdef, '(^CREATE.*ON )', '', 'g') AS indexdef, Count(*) AS nr_of_indexes, string_agg(indexname,',<br>' ORDER BY indexname) AS index_names 
FROM pg_indexes
GROUP BY trim(substring(indexdef, '(^([[:upper:]]| )+)')) || ' ' || regexp_replace(indexdef, '(^CREATE.*ON )', '', 'g')
HAVING Count(*)>1
ORDER BY nr_of_indexes DESC, indexdef;

Collections

This query belongs to the following collections:

Find problems about integrity constraints

A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview

Find problems automatically

Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

NameDescription
Find problems about integrity constraintsA selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories

This query is classified under the following categories:

Duplication of implementation elements

Queries of this catergory provide information about the duplication of the database objects.

Performance

Queries of this category provide information about indexes in a database.

Uniqueness

Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.

NameDescription
Duplication of implementation elementsQueries of this catergory provide information about the duplication of the database objects.
PerformanceQueries of this category provide information about indexes in a database.
UniquenessQueries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.