How to find missing foreign key indexes in PostgreSQL

If you ever wondered how to almost immediately speed up your PostgreSQL database when it is created without proper foreign key indexes, e.g. with schema generated by JPA, you may want to use the following query to collect candidate indexes:

select 'CREATE INDEX idx_'||conname||' ON '||conrelid|| ' ('||attname||');' from (
 SELECT conrelid::regclass
 ,reltuples::bigint,unnest(conkey) coloid
 FROM pg_constraint
 JOIN pg_class ON (conrelid = pg_class.oid)
 WHERE contype = 'f'
 SELECT 1 FROM pg_index
 WHERE indrelid = conrelid
 AND conkey @> indkey AND conkey <@ indkey
ORDER BY reltuples DESC
) as cols
join pg_attribute pga on pga.attrelid = cols.conrelid and pga.attnum=cols.coloid

It will return a list of DDL creating missing foreign key indexes ready for execution after your review:

"CREATE INDEX idx_fk_4ywoftpugx4dycijy8i9tyhwb ON profile (project_id);"
"CREATE INDEX idx_fk_o4al1lv1rgjw8m8xvrwnv797f ON profile_user_data (profile_id);"
"CREATE INDEX idx_fk_euwbenmq4r06p1k5cjlypng8s ON profile_user_data (user_id);"
"CREATE INDEX idx_fk_b7i81l1tk1ph95xnhtoftyv53 ON task (project_id);"
"CREATE INDEX idx_fk_1ytmg44m35ff160cofosaum8h ON user_projects (project_id);"
"CREATE INDEX idx_fk_8xi9c7nbxov8bdubwdcgahnjp ON user_projects (user_id);"

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s