| Query goal: | Find indexes that may be redundant. In addition to identical indexes it also considers indexes that cover the same columns and have the same properties except 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: | Medium (Medium number of false-positive results) |
| Query license: | MIT License |
| Fixing suggestion: | Drop the redundant indexes. |
| Data source: | system catalog only |
| SQL query: | Click on query to copy it
SELECT regexp_replace(indexdef, '(^CREATE.*ON )', '', 'g') AS indexdef, Count(*) AS nr_of_indexes, string_agg(trim(substring(indexdef, '(^([[:upper:]]| )+)')) || ' ' || indexname,',<br>' ORDER BY indexname) AS index_names FROM pg_indexes GROUP BY 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. |