Goal This query identifies standalone indexes—specifically those not backing a declarative constraint (such as a PRIMARY KEY or UNIQUE constraint)—that fail to follow standard naming conventions. It flags indexes that omit both the parent table's full name and its acronym (an abbreviation formed from the initial letters of its name components). Incorporating the table identifier into the index name is crucial: in PostgreSQL, indexes share the schema-wide namespace with tables, meaning this naming practice prevents collisions and provides immediate clarity when inspecting execution plans, performance statistics, or slow query logs.
Notes The query uses DISTINCT to remove duplicate rows in case of composite indexes.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability Medium Medium number of false-positive results
License MIT (opens in new tab)
Data Source system catalog only
SQL Query
WITH index_names AS (
SELECT 
  n.nspname AS table_schema,
  c2.relname AS table_name,
  c.relname AS index_name,
  i.indisunique AS is_unique
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 a.attnum>=1
AND a.attisdropped = FALSE
AND (n.nspname = 'public'
	 OR u.rolname <> 'postgres')
AND NOT EXISTS (SELECT * FROM pg_catalog.pg_constraint AS o WHERE o.conindid=i.indexrelid AND o.contype IN ('u','p')))
SELECT DISTINCT table_schema, table_name, index_name, is_unique
FROM index_names
WHERE index_name NOT ILIKE  '%' || table_name || '%'
AND translate(index_name,'_','') NOT ILIKE  '%' || translate(table_name,'_','') || '%'
AND index_name !~* ('(^|_)' || regexp_replace(table_name, '_*([^_])[^_]*(?:_+|$)', '\1', 'g') || '(_|$)')
ORDER BY is_unique DESC, table_schema, table_name;

Fixing Suggestion

Rename the index using ALTER INDEX ... RENAME TO to include the parent table's full name or its standard acronym as a component.

Collections

This query belongs to the following collections:

Find problems about names

A selection of queries that return information about the names of database objects. 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 .

NameDescription
Find problems about namesA selection of queries that return information about the names of database objects. 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:

Comfortability of database evolution

Queries of this category provide information about the means that influence database evolution.

Naming

Queries of this category provide information about the style of naming.

NameDescription
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
NamingQueries of this category provide information about the style of naming.