Postgres doesn’t create indexes for foreign keys

Story about another #epicfail. I understood if everything is ok I don’t like to write about that cause it looks simple. But when something goes wrong 🙂

Well I worked with Postgres last time about 4 years ago. So definitely I wasn’t a good postgres guys. Last 4 years in most cases I worked with MySql. And mysql automatically creates indexes for foreign keys. Some kind of “second system effect”. When you even don’t think about indexes cause you’re sure that they already should be created.

Well my topic would be useless if I didn’t provider any elegant solution. Right ?

So feel the power of postgres.

	information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc. CONSTRAINT_NAME = kcu. CONSTRAINT_NAME
JOIN information_schema.constraint_column_usage AS ccu ON ccu. CONSTRAINT_NAME = tc. CONSTRAINT_NAME
	constraint_type = 'FOREIGN KEY'
ORDER BY table_name

This will return table and column names for ALL foreign keys. Then you can just take this result to generate indexes.

Do not repeat my errors.

Leave a Reply

Your email address will not be published. Required fields are marked *