Foreign keys and slow insert

Dan Black wrote:

I read in documentation that primary key doesn't require additional indexes
but I could find nothing about foreign keys.
Do I need to create additional indexes when I create foreign keys?
Example:
create table master

create table slave

Do I need to create index

CREATE INDEX my_index
ON slave
USING btree
(master_id);

Yes. The primary key uses a "UNIQUE INDEX" to enforce uniqueness, so you
get the index for "free". The foreign-key has no such constraint of course.

--
Richard Huxton
Archonet Ltd

On Wed, 8 Jun 2005, Dan Black wrote:

I read in documentation that primary key doesn't require additional indexes
but I could find nothing about foreign keys.
Do I need to create additional indexes when I create foreign keys?
Example:
create table master
{
master_id INT4,
master_name VARCHAR(64),
CONSTRAINT master_pkey PRIMARY KEY (master_id)
}
create table slave
{
slave_id INT4,
slave_name VARCHAR(64),
master_id INT4,
CONSTRAINT slave_pkey PRIMARY KEY (slave_id),
CONSTRAINT slave_fkey_master_id FOREIGN KEY (master_id) REFERENCES master
(master_id) ON UPDATE CASCADE ON DELETE RESTRICT
}

Do I need to create index

CREATE INDEX my_index
ON slave
USING btree
(master_id);

?

Generally you want to do so to speed up lookups when master changes.
However, if master is basically write once, almost never update or delete,
then you may not need one.

I've observed that inserts into slave table became slower when I use foreign
key than without one.
Can it be related to foreign key?
And I am interested how much performance of database with foreign keys can
be different from performance of database without foreign keys? In other
words, how much performance decrease on using foreign keys?

Thanks :-)
--
Verba volent, scripta manent
My ISP - http://www.netbynet.ru

On Wed, 2005-06-08 at 12:39, Dan Black wrote:

I've observed that inserts into slave table became slower when I use
foreign key than without one.
Can it be related to foreign key?
And I am interested how much performance of database with foreign
keys can be different from performance of database without foreign
keys? In other words, how much performance decrease on using foreign
keys?

The problem you're seeing is usually caused by adding records to a table
set that starts out empty, and the planner uses seq scans, and as it
grows, should switch to random seeks, but doesn't know to, because no
one has bothered to analyze said tables.

Set up the pg_autovacuum daemon or cron vacuumdb -az to run every so
often to help that.

On the other hand, foreign keys are never zero cost, so even the most
efficient implementation is gonna be slower than not using them. Data
coherency costs, either up front (i.e. in the database doing it) or in
the back (i.e. hiring 20 summer interns to go through your data and find
the parts that are bad...) :)

I think 21 interns will be enough :)

2005/6/8, Scott Marlowe <smarlowe@g2switchworks.com>:

On Wed, 2005-06-08 at 12:39, Dan Black wrote:

I've observed that inserts into slave table became slower when I use
foreign key than without one.
Can it be related to foreign key?
And I am interested how much performance of database with foreign
keys can be different from performance of database without foreign
keys? In other words, how much performance decrease on using foreign
keys?

The problem you're seeing is usually caused by adding records to a table
set that starts out empty, and the planner uses seq scans, and as it
grows, should switch to random seeks, but doesn't know to, because no
one has bothered to analyze said tables.

Set up the pg_autovacuum daemon or cron vacuumdb -az to run every so
often to help that.

On the other hand, foreign keys are never zero cost, so even the most
efficient implementation is gonna be slower than not using them. Data
coherency costs, either up front (i.e. in the database doing it) or in
the back (i.e. hiring 20 summer interns to go through your data and find
the parts that are bad...) :)

--
Verba volent, scripta manent
My ISP - http://www.netbynet.ru