* set hint bits and collect statistics */ Without an index, this requires a sequential scan of the source table. It does so by searching if there are rows in the source table that would become orphaned by the data modification. Then PostgreSQL has to check if the foreign key constraint is still satisfied.
You delete rows or update key columns in the target table. If there is an index on the columns at the source, PostgreSQL can use an efficient nested loop join.Ģ. You perform a join between the two tables where you explicitly search for the source rows referencing one or a few target rows. The typical cases where you need that are:ġ. However, such an index is quite useful for finding all source rows that reference a target row. In contrast to the above, PostgreSQL requires no index at the source of a foreign key. The index also comes handy if you want to find the row in the target table that matches a row in the source table. This is required so that there is always a well-defined row to which the foreign key points.
Consequently, the target side of a foreign key is automatically indexed. Such constraints are implemented with unique indexes in PostgreSQL. The referenced columns in the target table must have a primary key or unique constraint. In the following, I will call the table, on which the foreign key constraint is defined, the source table and the referenced table the target table.
This article will explain that and show you how to search for missing indexes. Foreign key index performance © Laurenz Albe 2018įoreign key constraints are an important tool to keep your database consistent while also documenting relationships between tables.Ī fact that is often ignored is that foreign keys need proper indexing to perform well.