[Fwd: Indexes for Foreign Keys?]

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 --------
Subject: [ADMIN] Indexes for Foreign Keys?
Date: Mon, 29 Oct 2007 12:39:03 -0500
From: Jeff Larsen <jlar310(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org

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

--------------------------------