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
