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)
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:
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.