In a normalized table, the purpose of a primary key is to identify the combination of attributes (table columns) that uniquely identify each row in the table. Some of those primary key columns may also be foreign keys.
Foreign keys are used to identify the relationships between tables and the constraints that are to be imposed e.g. cascade update the FK when the parent changes. If the foreign key relationship is "identifying" then the foreign key is also part of the primary key of the child table. If the relationship is "non-identifying", then the foreign key is not part of its primary key.
PG automatically creates indexes for primary keys therefore it will automatically include identifying foreign keys in the index of the primary key.
Whether or not you want to index non-identifying foreign keys is determined by the requirements of the database design. For example, assume that we have a table that contains sales territories, which rarely change. We will use this table to create drop down lists that we will use to set territories. Now let's create a table for sales person and include an attribute for sales territory. We will create a non-identifying foreign key relationship between this value and the sales territories and create proper constraints. We will not index the column in the sales person table because the only thing that would use the index is the foreign key constraint and it will rarely fire and therefore the index is not worth the cost.
My point: Automatic indexes for foreign keys is not desirable.
Cheers,
H.E. Hall
ReedyRiver.com
-------- Original Message --------
I've noticed that PG automatically creates indexes when you create a
primary key. But when you create a foreign key on a child table, it
does not create an index on the referencing columns of the child
table.
Does PG *not* need an index to perform joins between parent and child
tables quickly? Or is it simply left up to the administrator to decide
if the index is necessary for adequate performance (i.e., avoiding
sequential scans). Or does PG somehow avoid sequential scans on FK
joins some other way? To be honest I have not spent any significant
time studying query plans as we are still in the early stages of a
potential migration.
I come from an Informix background where the server will either use an
existing index on the specified columns, or automatically create an
index to support a constraint of any type (PK, FK, UNIQUE). You can
not have a constraint without an underlying index in Informix.
Thanks,
Jeff
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--------------------------------