How to find missing indexes on foreign keys.

I’ve been using postgraphile for my new company, WhoTeam. Using postgraphile is, and I’m drastically simplifying here, like an ORM for postgresql and graphql. The core maintainer of postgraphile, Benjie Gillam gave me a lot of help in getting this system production ready. I’ve done a lot of nodejs in side projects and for fun, but I’ve never really bet on a database technology so new. I’m actually used to rails on the backend, and es6 / nodejs / react frontends. So I have to say, developing a full blown HR app in PostgresQL has come with a relatively steep learning curve. One of the major things I miss about rails backends is all the autogenerated indexes. Up until now, I haven’t really needed to understand how they’re created. But, if they’re missing, postgres will be slow, especially if you’re doing joins ( which Postgraphile does under the hood ). So I’m sharing with you today my jest test to find missing foreign keys.

This query works to find all non-indexed foreign keys on your namespaces (e.g. “whoteam”). It also accounts for compound key indexes, which is something I couldn’t find a way to do on StackOverflow. Enjoy 🙃