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)
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:
Name
Description
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 .
Categories
This query is classified under the following categories:
Name
Description
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.