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@xxxxxxxxxxxxxxxxxx] Sent: Thursday, May 08, 2008 12:19 PM To: Campbell, Lance Cc: pgsql-performance@xxxxxxxxxxxxxx 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
