Skip site navigation (1) Skip section navigation (2)

[Fwd: Indexes for Foreign Keys?]

From: "H(dot) Hall" <hhall1001(at)reedyriver(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: [Fwd: Indexes for Foreign Keys?]
Date: 2007-10-30 10:01:32
Message-ID: 472700FC.7040006@reedyriver.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

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


pgsql-admin by date

Next:From: Nandakumar TantryDate: 2007-10-30 12:57:04
Subject: Installing PostgreSQL as Admin
Previous:From: Achilleas MantziosDate: 2007-10-30 09:11:01
Subject: Re: How to kill process "idle in transaction"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group