with fk as (select
o.conname,
(select nspname from pg_namespace where oid=c.relnamespace) as fk_schema,
c.relname as fk_table,
c.oid as fk_table_oid,
o.conkey AS fk_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype='f'),
fk_unnest as (select conname, fk_schema, fk_table, fk_table_oid, fk_col, fk_col_num, ordin
from fk, unnest(fk.fk_col) with ordinality as k(fk_col_num, ordin)),
fk_with_names as (select conname, fk_schema, fk_table, array_agg(a_key.attname order by ordin) as fk_col
from fk_unnest k inner join pg_attribute a_key on k.fk_col_num = a_key.attnum and k.fk_table_oid = a_key.attrelid and a_key.attisdropped = false
group by conname, fk_schema, fk_table),
idx AS (SELECT
n.nspname AS index_schema,
c.relname AS index_name,
c2.relname AS table_name,
i.indrelid AS index_table_oid,
indkey AS index_col
FROM pg_catalog.pg_index AS i INNER JOIN pg_catalog.pg_class AS c ON i.indexrelid=c.oid
INNER JOIN pg_catalog.pg_class AS c2 ON i.indrelid=c2.oid
INNER JOIN pg_catalog.pg_namespace n ON c.relnamespace=n.oid
WHERE nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
idx_unnest as (select index_name, index_schema, table_name, index_table_oid, index_col_num, ordin
from idx, unnest(idx.index_col) with ordinality as k(index_col_num, ordin)),
idx_with_names as (select index_name, index_schema, table_name, array_agg(a_idx.attname order by ordin) as idx_col
from idx_unnest k inner join pg_attribute a_idx on k.index_col_num = a_idx.attnum and k.index_table_oid = a_idx.attrelid and a_idx.attisdropped = false
group by index_name, index_schema, table_name)
SELECT index_name, index_schema, table_name, idx_col
FROM idx_with_names
WHERE NOT EXISTS (SELECT 1
FROM fk_with_names
WHERE idx_with_names.index_schema=fk_with_names.fk_schema
AND idx_with_names.table_name=fk_with_names.fk_table
AND (idx_with_names.idx_col[1]=fk_with_names.fk_col[1] OR (idx_with_names.idx_col@>fk_with_names.fk_col AND fk_with_names.fk_col@>idx_with_names.idx_col)))
AND index_name~*'(ixfk|idxfk)'
ORDER BY index_schema, table_name, index_name;