Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D427C475FED for ; Tue, 28 Jan 2003 00:46:44 -0500 (EST) Received: from txsmtp01.texas.rr.com (ms-smtp-01.texas.rr.com [24.93.36.229]) by postgresql.org (Postfix) with ESMTP id 52179475FEC for ; Tue, 28 Jan 2003 00:46:44 -0500 (EST) Received: from spaceship.com (cs24243214-140.austin.rr.com [24.243.214.140]) by txsmtp01.texas.rr.com (8.12.5/8.12.2) with ESMTP id h0S5fxua013648 for ; Tue, 28 Jan 2003 00:41:59 -0500 (EST) Message-ID: <3E361946.4060000@spaceship.com> Date: Mon, 27 Jan 2003 23:46:46 -0600 From: Matt Mello User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: PgSQL Performance ML Subject: Re: Indexing foreign keys References: <3E35991D.5050704@spaceship.com> <1043702012.9896.72.camel@haggis> In-Reply-To: <1043702012.9896.72.camel@haggis> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/366 X-Sequence-Number: 1013 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. -- Matt Mello