Re: Creating a foreign key

Shaun,
Thanks for the very detailed description of why posgres does not auto
create indexes. That makes a lot of sense.

Thanks again,

Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu

-----Original Message-----
From: Shaun Thomas [mailto:sthomas(at)leapfrogonline(dot)com]
Sent: Thursday, May 08, 2008 12:19 PM
To: Campbell, Lance
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Creating a foreign key

On Thu, 2008-05-08 at 17:52 +0100, Campbell, Lance wrote:

> Is there an index automatically created on field fk_a_id in table B
> when I create a foreign key constraint?

No. The problem with doing this is it assumes certain things about your
infrastructure that may be entirely false. Indexes are to speed up
queries by logarithmically reducing the result set to matched index
parameters, and pretty much nothing else. Indexes are also not free,
taking up both disk space and CPU time to maintain, slowing down
inserts.

Foreign keys are not bi-directional either. They actually check the
index in the *source* table to see if the value exists. Having an index
on a column referring to another table may be advantageous, but it's not
always necessary. If you never use that column in a where clause, or it
isn't restrictive enough, you gain nothing and lose speed in table
maintenance. It's totally up to the focus of your table schema design,
really. Only careful app management and performance analysis can really
tell you where indexes need to go, beyond the rules-of-thumb concepts,
anyway.

> I also see "CREATE TABLE / PRIMARY KEY will create implicit index"
> when creating a primary key but I don't see any similar statement when
> creating a foreign key.

That's because the definition of a primary key is an index that acts as
the primary lookup for the table. This is required to be an index,
partially because it has an implied unique constraint, and also because
it has a search-span of approximately 1 when locating a specific row
from that table.

But indexes aren't some kind of magical "make a query faster" sauce.
With too many values, the cost of scanning them individually becomes
prohibitive, and the database will fall-back to a faster sequence-scan,
which can take advantage of the block-fetch nature of most storage
devices to just blast through all the results for the values it's
looking for. It's restrictive where clauses *combined* with well-chosen
indexes that give you good performance, with a little tweaking here and
there to make the query-planner happy.

But that's the long version. Postgres is by no means bare-bones, but it
assumes DBAs are smart enough to manage the structures they bolt onto
the metal. :)

--

Shaun Thomas
Database Administrator

Leapfrog Online
807 Greenwood Street
Evanston, IL 60201
Tel. 847-440-8253
Fax. 847-570-5750
www.leapfrogonline.com