Definition of a non-minimal superkey instead of a candidate key (based on unique indexes)

Back to Catalog
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.
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 License
Fixing Suggestion Drop the redundant indexes. Do not drop indexes that suport constraints.
Data Source system catalog only
SQL Query 
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 statements that help generate fixes for the identified problem.

SQL Query to Generate FixDescription
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.
Collections

This query belongs to the following collections:

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:

NameDescription
PerformanceQueries of this category provide information about indexes in a database.
UniquenessQueries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.
Validity and completenessQueries 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).