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 (opens in new tab)
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,',<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;

Collections

This query belongs to the following collections:

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 .

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:

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.

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.