Trying to write a query to find unindexed foreign keys referencing a table, I tried to do this:
SELECT *
FROM pg_constraint
WHERE contype = 'f'
AND confrelid = <the oid of the referenced table>
AND conindid = 0
To my surprise, the query did not return any rows, even though I knew there existed indexless foreign keys referencing my table. After investigating further, saw that conindid contained the oid of the referenced table's primary key, not the oid of the index "implementing" the foreign key.
I'm running PostgreSQL 9.5 running on Ubuntu linux 3.19.0-49-generic.
| conindid | oid | pg_class.oid | The index supporting this constraint, if it's a unique, primary key, foreign key, or exclusion constraint; else 0 |
Regards,
Roy Brokvam
