How to find missing foreign key indexes in PostgreSQL

Would you like to quickly find and generate 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
 ,conname
 ,reltuples::bigint,unnest(conkey) coloid
 FROM pg_constraint
 JOIN pg_class ON (conrelid = pg_class.oid)
 WHERE contype = 'f'
 AND NOT EXISTS (
 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);"

Comments (0)

Thanks! Your comment is awaiting moderation.

Testimonials

They have a very good company culture of their own, which gives them a real edge compared to other providers.

CEO

Leading UK system integrator

They're very skilled technically and are also able to see the bigger picture.

Managing Partner

Scalable SaaS for healthcare IoT built on Salesforce platform

They've been consistently able to deliver work on time and within budget.

CTO

High performance SaaS for financial insitutions

We are seriously impressed by the quality and broader picture of anything they do for us.

CEO

Gold trading platform

Speak with an Expert

How can we help? If you would like a member of the Stratoflow team to get in touch, please send us your message and we will contact you shortly.

Contact