From: Richard Broersma Jr <rabroersma@yahoo.com>
To: pgsql-admin@postgresql.org, Jeff Larsen <jlar310@gmail.com>
Subject: Re: Indexes for Foreign Keys?
Date: Mon, 29 Oct 2007 10:53:49 -0700 (PDT)
Message-ID: <96808.75196.qm@web31802.mail.mud.yahoo.com> (raw)
In-Reply-To: <d1f9b6f00710291039o7a25b423m57c78ccb29abf56f@mail.gmail.com>
--- On Mon, 10/29/07, Jeff Larsen <jlar310@gmail.com> wrote:
> Does PG *not* need an index to perform joins between parent
> and child tables quickly?
PG doesn't need the a FK index to quickly insure that the constraint is maintained.
> Or is it simply left up to the administrator to decide
> if the index is necessary for adequate performance (i.e.,
> avoiding sequential scans).
Yup, the DBA can decide to create indexs on foreign keys to improve performance.
> Or does PG somehow avoid sequential scans on FK
> joins some other way?
Nope. I will probably use a Seq. Scan in the absense on an index.
> 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).
Having an index on the FK does nothing to enforce any constraints from the referenced table. IIUC, in postgresql when you try to update or insert a value into a foreign key field, postgres will use the index in the referenced parent table WITH the index to quickly validate your input.
> You can not have a constraint without an underlying index in Informix.
This is not true in postgreSQL, with the exception of UNIQUE types of indexs. However, check constraints do not require any indexs.
Regards,
Richard Broersma Jr.
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: rabroersma@yahoo.com, jlar310@gmail.com
Subject: Re: Indexes for Foreign Keys?
In-Reply-To: <96808.75196.qm@web31802.mail.mud.yahoo.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