Date: Thu, 08 May 2008 22:02:47 +0200
To: "Campbell, Lance" <lance@uiuc.edu>, pgsql-performance@postgresql.org
Subject: Re: Creating a foreign key
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <B10E6810AC2A2F4EA7550D072CDE8760CDDD46@SAB-FENWICK.sab.uiuc.edu>
Content-Transfer-Encoding: 7bit
Message-ID: <op.uaupyxsecigqcu@apollo13.peufeu.com>
In-Reply-To: <B10E6810AC2A2F4EA7550D072CDE8760CDDD46@SAB-FENWICK.sab.uiuc.edu>
User-Agent: Opera Mail/9.24 (Linux)


> When you create a foreign key to a table is there an index that is
> created on the foreign key automatically?

	No, Postgres doesn't do it for you, because if you create (ref_id)  
references table.id, you will perhaps create an index on (ref_id, date)  
which would then fill the purpose (and other purposes), or perhaps your  
table will have 10 rows (but postgres doesnt' know that when you create  
it) and having an index would be useless, or your table could have many  
rows but only a few distinct referenced values, in which case again the  
index would only slow things down.
	PG does not presume to know better than yourself what you're gonna do  
with your data ;)
	UNIQUE and PRIMARY KEY do create UNIQUE INDEXes, of course.