Sourced from PostGraphile documentation
Many people don't realise that when you create a foreign key relation, PostgreSQL does NOT automatically create an index on the referencing column(s). That can mean that when you query based on that relation, which PostGraphile does a lot when traversing relationships, it can involve a full table scan which is very expensive.
Cameron Ellis has written a short article on finding missing indexes on foreign keys which utilises SQL similar to the following to automatically detect missing foreign key indexes:
WITH indexed_tables AS (
select
ns.nspname,