Skip to content

Instantly share code, notes, and snippets.

@robcowie
Created October 15, 2021 15:33
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save robcowie/9064f54975b3a5470eab325d07720870 to your computer and use it in GitHub Desktop.
Save robcowie/9064f54975b3a5470eab325d07720870 to your computer and use it in GitHub Desktop.
Find foreign keys that do not have an index on them in Postgresql
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment