Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 664B89F99B2 for ; Mon, 29 Oct 2007 15:59:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 03533-09 for ; Mon, 29 Oct 2007 15:59:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id B86C89F9B58 for ; Mon, 29 Oct 2007 15:59:05 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l9TIx3KP014808; Mon, 29 Oct 2007 14:59:03 -0400 (EDT) To: "Jeff Larsen" cc: pgsql-admin@postgresql.org Subject: Re: Indexes for Foreign Keys? In-reply-to: References: Comments: In-reply-to "Jeff Larsen" message dated "Mon, 29 Oct 2007 12:39:03 -0500" Date: Mon, 29 Oct 2007 14:59:03 -0400 Message-ID: <14807.1193684343@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/297 X-Sequence-Number: 27211 "Jeff Larsen" 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