From: Beth Gatewood <beth@vizxlabs.com>
To: 'Christopher Kings-Lynne' <chriskl@familyhealth.com.au>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: make a unique index for foreign keys?
Date: Thu, 13 Jun 2002 09:23:35 -0700
Message-ID: <004701c212f6$ab821900$6501a8c0@bethvizx> (raw)
In-Reply-To: <GNELIHDDFBOCMGBFGEFOCELJCCAA.chriskl@familyhealth.com.au>
Chris/ Josh-
OK-response to Chris below. Synopsis here....simply by creating a foreign
key will not create an index. On large tables I SHOULD put a non-unique
index on the foreign key (right?)
Hmm...nope. Creating a foreign key doesn't create an index. However,
creating a primary key does - sure you're not getting mixed up there?
You are absolutely right! I am confused!!!! I did the actual test....
//create the tables
beth=> create table parent(pk INTEGER primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'parent_pkey'
for table 'parent'
CREATE
beth=> create table child (fk INTEGER references parent(pk) );
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s) CREATE
// now get the index oid for the parent table (there should only be 1 index
for the primary key)
beth=> select pg_index.* from pg_index, pg_class where pg_class.oid =
pg_index. indrelid and pg_class.relname='parent';
indexrelid | indrelid | indproc | indkey | indclass | indisclustered |
indisunique | indisprimary | indreference |
------------+----------+---------+--------+----------+--------------
--+-------------+--------------+--------------+---------
26271 | 26269 | - | 1 | 1978 | f | t
| t | 0 |
(1 row)
// get the index for the parent table--and it is the index for the primary
key
beth=> select pg_get_indexdef(26271);
pg_get_indexdef ------------------
------------------------------------------ CREATE UNIQUE
INDEX parent_pkey ON parent USING btree (pk) (1 row)
//now try that same thing for the child table and there are NO indices (also
confirmed by \di)
beth=> select pg_index.* from pg_index, pg_class where pg_class.oid =
pg_index. indrelid and pg_class.relname='child';
indexrelid | indrelid | indproc | indkey | indclass | indisclustered |
indisunique | indisprimary | indreference |
------------+----------+---------+--------+----------+--------------
--+-------------+--------------+--------------+---------
(0 rows)
Postgres doesn't create indices for you, unless as part of a primary key or
unique constraint.
OK-I've got it now!!!
Thank you so much!!!!
Beth
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: beth@vizxlabs.com, chriskl@familyhealth.com.au, josh@agliodbs.com
Subject: Re: make a unique index for foreign keys?
In-Reply-To: <004701c212f6$ab821900$6501a8c0@bethvizx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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