Thread: Foreign keys and slow insert

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);

?

Thanks

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

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.

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

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 :)




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