Skip site navigation (1) Skip section navigation (2)

Re: Indexing foreign keys

From: Matt Mello <alien(at)spaceship(dot)com>
To: PgSQL Performance ML <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Indexing foreign keys
Date: 2003-01-28 05:46:46
Message-ID: 3E361946.4060000@spaceship.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Matt MelloDate: 2003-01-28 05:51:31
Subject: Re: Indexing foreign keys
Previous:From: Ron JohnsonDate: 2003-01-28 01:42:41
Subject: Re: 7.3.1 New install, large queries are slow

pgsql-general by date

Next:From: Matt MelloDate: 2003-01-28 05:51:31
Subject: Re: Indexing foreign keys
Previous:From: Andrey MosienkoDate: 2003-01-28 05:13:32
Subject: A powerful dump utility

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group