| Goal | Find non-function based indexes (both unique and non-unique) that duplicate each other because their first column is identical and the operator class that is used in case of the first column is identical. Include unique indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. |
|---|---|
| Notes | Find non-function based indexes (both unique and non-unique) that have identical first column but the operator class that is used in case of the first column is different. Include unique indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. Exclude pairs of indexes where both support a constraint. |
| Type | Problem detection (Each row in the result could represent a flaw in the design) |
| Reliability | High (Few or no false-positive results) |
| License | MIT (opens in new tab) |
| Fixing Suggestion | Drop the redundant indexes. Do not drop indexes that suport constraints. |
| Data Source | system catalog only |
| SQL Query |
|
SQL statements that help generate fixes for the identified problem.
Fix Action #1
WITH indexes AS (SELECT
n.nspname AS sch,
c2.relname AS tbl,
c.relname AS indx,
array_agg(a.attname ORDER BY a.attnum) AS clm,
i.indisunique,
indclass[0] AS operator_class,
EXISTS (SELECT * FROM pg_catalog.pg_constraint AS o WHERE o.conindid=i.indexrelid) AS is_constraint
FROM
pg_catalog.pg_class AS c INNER JOIN pg_catalog.pg_index AS i ON i.indexrelid = c.oid
INNER JOIN pg_catalog.pg_class AS c2 ON i.indrelid = c2.oid
INNER JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid
INNER JOIN pg_catalog.pg_authid AS u ON n.nspowner = u.oid
INNER JOIN pg_catalog.pg_attribute AS a ON a.attrelid = c.oid
WHERE c.relkind = 'i'
AND i.indexprs IS NULL
AND a.attnum>=1
AND a.attisdropped = FALSE
AND (n.nspname = 'public'
OR u.rolname <> 'postgres')
GROUP BY n.nspname, c2.relname, c.relname, i.indisunique, indclass[0], is_constraint),
index_with_type AS (SELECT sch, tbl, indx, clm, operator_class, is_constraint,
CASE WHEN is_constraint=TRUE THEN 'UNIQUE CONSTRAINT INDEX'
WHEN indisunique=TRUE AND is_constraint=FALSE THEN 'UNIQUE NON-CONSTRAINT INDEX'
ELSE 'NON-UNIQUE INDEX' END AS index_type
FROM indexes),
duplicates AS (SELECT A.sch AS table_schema, A.tbl AS table_name, A.index_type AS idx1_index_type, A.indx AS idx1, A.clm AS idx1_columns,
B.clm AS idx2_columns, B.index_type AS idx2_index_type, B.indx AS idx2
FROM index_with_type AS A, index_with_type AS B
WHERE A.sch=B.sch AND A.tbl=B.tbl AND A.indx>B.indx AND A.clm[1]=B.clm[1] AND A.operator_class=B.operator_class
AND NOT (a.is_constraint=TRUE AND b.is_constraint=TRUE))
SELECT format('DROP INDEX %1$I.%2$I;', table_schema, idx1) AS statements
FROM duplicates
WHERE idx1_index_type<>'UNIQUE CONSTRAINT INDEX'
UNION SELECT format('DROP INDEX %1$I.%2$I;', table_schema, idx2) AS statements
FROM duplicates
WHERE idx2_index_type<>'UNIQUE CONSTRAINT INDEX'
ORDER BY statements;Drop the index. Choose, which index to drop and which index should stay in place.
| SQL Query to Generate Fix | Description |
|---|---|
| Drop the index. Choose, which index to drop and which index should stay in place. |
Collections
This query belongs to the following collections:
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 .
| Name | Description |
|---|---|
| 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:
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.
| 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. |
Further reading and related materials:
Smell "Index abuse": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018).
| Reference |
|---|
| Smell "Index abuse": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018). |