Query 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 about the query: | 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. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | High (Few or no false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Drop the redundant indexes. Do not drop indexes that suport constraints. |
Data source: | system catalog only |
SQL query: | Click on query to copy it
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) 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) ORDER BY A.sch, A.tbl, A.indx, B.indx; |
SQL query | Description |
---|---|
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. |
Collection name | Collection 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 . |
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. |
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). |