For PostgreSQL 9.4 (http://www.postgresql.org/docs/9.4/static/ddl-constraints.html) it is stated that
A foreign key must reference columns that either are a primary key or form a unique constraint. This means that the referenced columns always have an index (the one underlying the primary key or unique constraint); so checks on whether a referencing row has a match will be efficient. Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.
and I wondered whether the author(s) of these lines were overlooking one major point: When I have a parent-child relation (e.g. a cart with cart positions) where the child (cart position) marks its relation to the parent (cart) using a foreign key to the parents (cart) primary key when I want to get all children for a given parent I use the non-indexed foreign key column.
So, does the overhead of not generating an index for foreign key columns by default (in contrast to primary key columns) and letting the user experience suboptimal performance on one hand overweigh the overhead of maintenancing an index which, at smaller table sizes, do only minor improvements of query performance on the other hand?
Perhaps someone has more insights in database implementation details and reasonings leading to this decision and/or can point me to reasons why a default index generation for foreign key columns (in Postgresql) could lead to unnoticed serious problems.