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 License
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,',
' 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:

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:

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.