Missing / Orphan Indexes? In the process of finding duplicate indexes, I've been using the following query to identify them:

SELECT 
indrelid::regclass AS TableName 
,array_agg(indexrelid::regclass) AS Indexes 
FROM pg_index 
GROUP BY 
indrelid 
,indkey 
HAVING COUNT(*) &gt; 1;


From which I get an entry with the following data:

tablename   indexes
distribucion.asignacion {distribucion.distribucion_unique_asignacion_imei,distribucion.pk_distribucion_asignacion}


Too check that the index are actually duplicate, I manually see the columns involving each reported index, with this query:

select * from pg_indexes where tablename='&lt;table_name&gt;';


But when I search for the above indexes for ''distribucion.asignacion'' table I get no results.

What could be wrong? Have these indexes been left "orphan", if so, may be there are other orphan structures too in my database, so how can I find such?
pg_indexes.tablename only contains the table name, not the schema name. The schema name is available in the column schemaname.

So you need to use

select * 
from pg_indexes 
where tablename ='asignacion'
  and schemaname = 'distribucion';