Index Every Foreign Key on the Child Table
A foreign key constraint indexes nothing by itself in PostgreSQL: the parent's primary key is indexed, the child's referencing column is not. That missing index makes two things slow — joins from parent to child, and every delete or key update on the parent, because the database must scan the whole child table to check that no row still points at the vanishing parent. MySQL's InnoDB creates the index for you, which is why this bug surprises people arriving from MySQL.
Questions this Concept answers
- Why does deleting one parent row become slow when the child's referencing column is unindexed?
Beware The Missing Foreign Key Index: A Postgres Performance Gotcha
In PostgreSQL a foreign key constraint does not create an index on the child column that points at the parent. That means every delete or key change on the parent has to scan the whole child table to…