Thread: Indexes for Foreign Keys?

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

--- 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
youtry to update or insert a value into a foreign key field, postgres will use the index in the referenced parent table
WITHthe 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
requireany indexs. 

Regards,
Richard Broersma Jr.

"Jeff Larsen" <jlar310@gmail.com> writes:
> 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.

This is intentional since depending on what your workload is like,
the index on the child table might be more overhead than it's worth.

If you frequently do updates or deletes on the parent column, you'll
want to have the child column indexed.

            regards, tom lane