Re: Indexes for Foreign Keys?

--- On Mon, 10/29/07, Jeff Larsen <jlar310(at)gmail(dot)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.