Find foreign keys that do not have an index on them in Postgresql

foreign_keys_without_indexes.sql

-- Show all foreign keys that do not have an index on them
-- Potential performance problem if the join is performed often

SELECT
    tc.table_name,
    kcu.column_name,
    tc.constraint_name
FROM
    information_schema.table_constraints AS tc
JOIN
    information_schema.key_column_usage AS kcu
ON
    tc.constraint_name = kcu.constraint_name
AND
    tc.table_schema = kcu.table_schema
LEFT OUTER JOIN (
    SELECT
        t.relname AS table_name,
        a.attname AS column_name,
        i.relname AS index_name
    FROM
        pg_class AS t,
        pg_class AS i,
        pg_index AS ix,
        pg_attribute AS a
    WHERE
        t.oid = ix.indrelid
        AND i.oid = ix.indexrelid
        AND a.attrelid = t.oid
        AND a.attnum = ANY(ix.indkey)
        AND t.relkind = 'r'
    ) as indexes
ON
    tc.table_name = indexes.table_name
AND
    kcu.column_name = indexes.column_name
WHERE
    tc.constraint_type = 'FOREIGN KEY'
AND
    indexes.index_name IS NULL;