Optimization - single / double key

    Hi!

    I'm designing a database and I'm having some trouble selecting
which optimizations should I implement (in Postgres and maybe Oracle)

    Can someone please give an opinion on the following ?:
As an example of my doubt:

Imagine two tables representing a trivial hard disk hierarchy:

create table directory
(
    int dir_key,
    text name,
    PRIMARY KEY (dir_key)
};

create table file
{
    int file_key,
    int foreign_dir_key,
    text name,
    PRIMARY KEY (file_key)
};

Now the query to list all files from one specific directory:
SELECT     file.name
FROM    file,directory
WHERE   directory.name='foo' and file.foreign_dir_key=directory.dir_key;

I would like to know if it would speed up database access in this query
if I make foreign_dir_key part of file's table key,
as if it was :  PRIMARY_KEY(foreign_dir_key, file_key)

Of course there is a index on it anyway, but would making it part of the
key speed up access ? And at maintenance time, would the table be
rewritten according to the key, or to the indexes ? (or it isn't rewritten
unless a pg_dump/undump is made ?)

In Oracle, if I specify foreign_dir_key to be a FOREIGN KEY, I believe it
does the trick, but in Postgres, how can I be sure of maximum speed ?
My opinion is that only being an INDEX or not makes difference, but, if
you think the way information is written on disk... maybe the key is the
determinant factor.

 Thank you very much for your attention and time!

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
`````````````````````````````````````````````
Silvio Emanuel Nunes Barbosa de Macedo
mailto:smacedo@inescn.pt

INESC - Porto - Grupo CAV
Pc da Republica, 93 R/C   Tel:351 2 209 42 21
4000 PORTO  PORTUGAL      Fax:351 2 208 41 72