pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Larsen <jlar310@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Indexes for Foreign Keys?
Date: Mon, 29 Oct 2007 14:59:03 -0400
Message-ID: <14807.1193684343@sss.pgh.pa.us> (raw)
In-Reply-To: <d1f9b6f00710291039o7a25b423m57c78ccb29abf56f@mail.gmail.com>
References: <d1f9b6f00710291039o7a25b423m57c78ccb29abf56f@mail.gmail.com>

"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



view thread (3+ messages)

Message-ID: <14807.1193684343@sss.pgh.pa.us>
Permalink:  ../14807.1193684343@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/14807.1193684343@sss.pgh.pa.us

 · 

reply

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: tgl@sss.pgh.pa.us, jlar310@gmail.com
  Subject: Re: Indexes for Foreign Keys?
  In-Reply-To: <14807.1193684343@sss.pgh.pa.us>

* 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