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

Indexes for Foreign Keys?

From: "Jeff Larsen" <jlar310(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Indexes for Foreign Keys?
Date: 2007-10-29 17:39:03
Message-ID: d1f9b6f00710291039o7a25b423m57c78ccb29abf56f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

Responses

pgsql-admin by date

Next:From: Peter KoczanDate: 2007-10-29 17:39:53
Subject: Re: Postgresql takes more time to update
Previous:From: Andrew DunstanDate: 2007-10-29 14:37:29
Subject: Re: [HACKERS] grep command

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