| Query 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. |
| Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
| Query reliability: | High (Few or no false-positive results) |
| Query license: | MIT License |
| Fixing suggestion: | Drop the duplicates and leave only one in place. |
| Data source: | system catalog only |
| SQL query: | Click on query to copy it
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; |
| Collection name | Collection 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 . |
| Category name | Category 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. |