From: Jeff Larsen <jlar310@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Indexes for Foreign Keys?
Date: Mon, 29 Oct 2007 12:39:03 -0500
Message-ID: <d1f9b6f00710291039o7a25b423m57c78ccb29abf56f@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jlar310@gmail.com
Subject: Re: Indexes for Foreign Keys?
In-Reply-To: <d1f9b6f00710291039o7a25b423m57c78ccb29abf56f@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox