Thread: Creating a foreign key
PostgreSQL: 8.2
When you create a foreign key to a table is there an index that is created on the foreign key automatically?
Example:
Table A has a field called ID.
Table B has a field called fk_a_id which has a constraint of being a foreign key to table A to field ID.
Is there an index automatically created on field fk_a_id in table B when I create a foreign key constraint?
I assume yes. But I wanted to check. I did not see it specifically mentioned in the documentation.
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.
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
On Thu, 8 May 2008 11:52:50 -0500 "Campbell, Lance" <lance@uiuc.edu> wrote: > PostgreSQL: 8.2 > > > > When you create a foreign key to a table is there an index that is > created on the foreign key automatically? No. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
Attachment
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
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@leapfrogonline.com] Sent: Thursday, May 08, 2008 12:19 PM To: Campbell, Lance Cc: pgsql-performance@postgresql.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
> 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.