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.
SELECT tc. TABLE_NAME, kcu. COLUMN_NAME FROM 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 WHERE 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.