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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]