Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9B89E9F9939 for ; Mon, 29 Oct 2007 14:53:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 70458-08 for ; Mon, 29 Oct 2007 14:53:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from web31802.mail.mud.yahoo.com (web31802.mail.mud.yahoo.com [68.142.207.65]) by postgresql.org (Postfix) with SMTP id 70E209F998A for ; Mon, 29 Oct 2007 14:53:51 -0300 (ADT) Received: (qmail 75205 invoked by uid 60001); 29 Oct 2007 17:53:50 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Message-ID; b=5KRYO8tSJNk3EJQozuRG8JjohgMH2Yv+y/MYJPV0H6qgXEkTsTWXr1uxvNTcUYvkY5qAt3VKBhuGLuh7yaCY/Gh9eI4hWv3T0zrkIJMK3Kk+J1dWJaiatGp7Zq9fwFi+D+dnp6O/mcrpzXXyH6EY8LoYAUJJve25dgiIqUc/xK0=; X-YMail-OSG: E0O.f7IVM1lzeYU_Wuw5LkbVEx6HvFQmNASFytoB23KhCMr9Yq8.P9ODsLw._jWr9ldSor8fQg-- Received: from [12.180.237.138] by web31802.mail.mud.yahoo.com via HTTP; Mon, 29 Oct 2007 10:53:49 PDT X-Mailer: YahooMailWebService/0.7.152 Date: Mon, 29 Oct 2007 10:53:49 -0700 (PDT) From: Richard Broersma Jr Subject: Re: Indexes for Foreign Keys? To: pgsql-admin@postgresql.org, Jeff Larsen In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Message-ID: <96808.75196.qm@web31802.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/295 X-Sequence-Number: 27209 --- On Mon, 10/29/07, Jeff Larsen 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.