pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCreating a foreign key
5+ messages / 4 participants
[nested] [flat]
* Creating a foreign key
@ 2008-05-08 16:52 Campbell, Lance <lance@uiuc.edu>
0 siblings, 3 replies; 5+ messages in thread
From: Campbell, Lance @ 2008-05-08 16:52 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating a foreign key
@ 2008-05-08 17:00 Joshua D. Drake <jd@commandprompt.com>
parent: Campbell, Lance <lance@uiuc.edu>
2 siblings, 0 replies; 5+ messages in thread
From: Joshua D. Drake @ 2008-05-08 17:00 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; +Cc: pgsql-performance
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
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating a foreign key
@ 2008-05-08 17:18 Shaun Thomas <sthomas@leapfrogonline.com>
parent: Campbell, Lance <lance@uiuc.edu>
2 siblings, 1 reply; 5+ messages in thread
From: Shaun Thomas @ 2008-05-08 17:18 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating a foreign key
@ 2008-05-08 18:11 Campbell, Lance <lance@uiuc.edu>
parent: Shaun Thomas <sthomas@leapfrogonline.com>
0 siblings, 0 replies; 5+ messages in thread
From: Campbell, Lance @ 2008-05-08 18:11 UTC (permalink / raw)
To: Shaun Thomas <sthomas@leapfrogonline.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating a foreign key
@ 2008-05-08 20:02 PFC <lists@peufeu.com>
parent: Campbell, Lance <lance@uiuc.edu>
2 siblings, 0 replies; 5+ messages in thread
From: PFC @ 2008-05-08 20:02 UTC (permalink / raw)
To: Campbell, Lance <lance@uiuc.edu>; pgsql-performance
> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-05-08 20:02 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-08 16:52 Creating a foreign key Campbell, Lance <lance@uiuc.edu>
2008-05-08 17:00 ` Joshua D. Drake <jd@commandprompt.com>
2008-05-08 17:18 ` Shaun Thomas <sthomas@leapfrogonline.com>
2008-05-08 18:11 ` Campbell, Lance <lance@uiuc.edu>
2008-05-08 20:02 ` PFC <lists@peufeu.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox