Query goal: | Find pairs of non-partial unique indexes where the columns of a index are a proper subset of the columns of another index. Include indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. Exclude the pairs where both participants have been created to support a constraint. Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define keys based on candidate keys, i.e., the keys should not have redundancy in terms of columns. Defining a unique index essentially means defining a key in the table but it is done at the lower level of abstraction. |
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, 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.indisunique=TRUE AND a.attnum>=1 AND a.attisdropped = FALSE AND (n.nspname = 'public' OR u.rolname <> 'postgres') AND NOT EXISTS (SELECT * FROM pg_indexes AS pi WHERE pi.schemaname=n.nspname AND pi.tablename=c2.relname AND pi.indexname=c.relname AND pi.indexdef ILIKE '% WHERE %') GROUP BY n.nspname, c2.relname, c.relname, i.indisunique, is_constraint) SELECT A.sch AS table_schema, A.tbl AS table_name, A.is_constraint AS is_idx1_constraint, A.indx AS idx1, A.clm AS idx1_columns, B.clm AS idx2_columns, B.is_constraint AS is_idx2_constraint, B.indx AS idx2 FROM indexes AS A, indexes AS B WHERE A.sch=B.sch AND A.tbl=B.tbl AND NOT (a.clm@>b.clm AND b.clm@>a.clm) AND a.clm@>b.clm 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, 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.indisunique=TRUE 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, is_constraint), duplicates AS (SELECT A.sch AS table_schema, A.tbl AS table_name, A.is_constraint AS is_idx1_constraint, A.indx AS idx1, A.clm AS idx1_columns, B.clm AS idx2_columns, B.is_constraint AS is_idx2_constraint, B.indx AS idx2 FROM indexes AS A, indexes AS B WHERE A.sch=B.sch AND A.tbl=B.tbl AND NOT (a.clm@>b.clm AND b.clm@>a.clm) AND a.clm@>b.clm 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 is_idx1_constraint=FALSE UNION SELECT format('DROP INDEX %1$I.%2$I;', table_schema, idx2) AS statements FROM duplicates WHERE is_idx2_constraint=FALSE ORDER BY statements; | Drop the index. |
Collection name | Collection 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 . |
Category name | Category description |
---|---|
Performance | Queries of this category provide information about indexes in a database. |
Uniqueness | Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes. |
Validity and completeness | Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness). |
Reference |
---|
https://stackoverflow.com/questions/4519825/what-are-the-differences-between-a-superkey-and-a-candidate-key |