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.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT License
Fixing Suggestion Drop the redundant indexes.
Data Source system catalog only
SQL Query
SELECT regexp_replace(indexdef, '(^CREATE.*ON )', '', 'g') AS indexdef, Count(*) AS nr_of_indexes, string_agg(trim(substring(indexdef, '(^([[:upper:]]| )+)')) || ' ' || indexname,',
' 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;
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.