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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-admin by date

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