Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id A33BF9FB870 for ; Mon, 29 Oct 2007 14:39:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 15794-05 for ; Mon, 29 Oct 2007 14:39:05 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rn-out-0102.google.com (rn-out-0910.google.com [64.233.170.189]) by postgresql.org (Postfix) with ESMTP id 3954C9FB968 for ; Mon, 29 Oct 2007 14:39:06 -0300 (ADT) Received: by rn-out-0102.google.com with SMTP id s46so1370368rnb for ; Mon, 29 Oct 2007 10:39:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; bh=cttyCNxUibK6ldL2/Hk2lXHseFdLbenxVCmsEyUKT28=; b=MDn4UzuWPwvSKJDc7AFJywi9SQnEf5jqHtMeW5DgQ64RqdHuBV5jiXZaGihWHKNaNoQRws7TVNZhgm3zf1fMJiJLo2G8WXzErldJYIcwy3Xc65Y2LR1BMBbGiP/FLV3Nyj90KKc37GGa2o0alwHlNLGVo+cKVvoE+fWvCUx873s= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=XyO3+gFH6fALCuttpIPiWB/6DCgaPTmcIjIA+jTEzBHYgd0dPCWMjicLlkYuulbPb6QYPCswLNEPqy1H81zUTQ6Mrtjo1nYm1TyxFo6VvO1o+F8tCdFGB5G6pMZukpCkjyRYBH/7VBsMdNhTHGUOrRNU2rtxoE617GhlUxzVYi8= Received: by 10.142.77.11 with SMTP id z11mr1435154wfa.1193679543851; Mon, 29 Oct 2007 10:39:03 -0700 (PDT) Received: by 10.143.35.10 with HTTP; Mon, 29 Oct 2007 10:39:03 -0700 (PDT) Message-ID: Date: Mon, 29 Oct 2007 12:39:03 -0500 From: "Jeff Larsen" To: pgsql-admin@postgresql.org Subject: Indexes for Foreign Keys? MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/293 X-Sequence-Number: 27207 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