I'm aware that if you create a foreign key constraint, no indexes are automatically created.
I would like to find a way to programatically inspect all my foreign keys and identify possibly missing indexes on either table (either the table defining the constraint or the table being referenced).
I wasn't able to find anything searching Google or the pg archives.
Attached is a first attempt. Just run the missing-fk-indexes.sql through psql. e.g.,
psql -q mydb -f missing-fk-indexes.sql
I know the output can be improved, but is this headed toward the right direction and/or is there already a simpler way to accomplish this?
Briefly, it finds all the unique tables/columns referenced by foreign keys. Then it examines all the indexes, looking for any that are a prefix of the fk columns. It writes out any tables/columns where no indexes are found, followed by a list of the fk's that reference those tables/columns.
Also attached is a trivial test schema to run it against.
Life on your PC is safer, easier, and more enjoyable with Windows Vista®.
See how