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.