Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3E4CB475F39 for ; Tue, 28 Jan 2003 10:15:09 -0500 (EST) Received: from lakemtao01.cox.net (lakemtao01.cox.net [68.1.17.244]) by postgresql.org (Postfix) with ESMTP id C0BBB475D00 for ; Tue, 28 Jan 2003 10:15:05 -0500 (EST) Received: from [192.168.1.1] ([68.11.66.83]) by lakemtao01.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030128151508.ZUTQ16369.lakemtao01.cox.net@[192.168.1.1]> for ; Tue, 28 Jan 2003 10:15:08 -0500 Subject: Re: Indexing foreign keys From: Ron Johnson To: PgSQL Performance ML In-Reply-To: <3E361946.4060000@spaceship.com> References: <3E35991D.5050704@spaceship.com> <1043702012.9896.72.camel@haggis> <3E361946.4060000@spaceship.com> Content-Type: text/plain Organization: Message-Id: <1043766909.9899.132.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 28 Jan 2003 09:15:09 -0600 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/370 X-Sequence-Number: 1017 On Mon, 2003-01-27 at 23:46, Matt Mello wrote: > Ron Johnson wrote: > > When you say "I created an index on those 2 fields", so you mean on > > the fields in the 400K row table, or on the keys in the "fact tables" > > that the 400K row table? > > > > Also, in IFX, could the creation of the foreign indexes have implicitly > > created indexes? > > The reason I ask is that this is what happens in Pg when you create a > > PK. > > > > The 400K row table has 2 fields that are FK fields. The already-indexed > PK fields that they reference are in another table. I just recently > added indexes to the 2 FK fields in the 400K row table to get the speed > boost. > > Yes. In IFX, when you create a FK, it seems to create indexes > automatically for you, just like PG does with PK's. > > In fact, I can't imagine a situation where you would NOT want a FK > indexed. I guess there must be one, or else I'm sure the developers > would have already added auto-creation of indexes to the FK creation, as > well. When I took my brain out of 1st gear, it was "Doh!": I realized that I was thinking backwards... -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "Fear the Penguin!!" | +---------------------------------------------------------------+