storing a tree-like structure and selecting path from leaf to root

Hi,

( this msg has 2 questions )

I would like to create a db of all files (home + office + backups +
friends_backups from floppies, tapes, CDs, etc), and would like to store
it in pg. Now every row (a file or a directory) will have an node ID, name
(filename / dirname) and a parent node ID, except for root which will have
no parent, or be parent for self.

This will create a tree-like "filesystem" representation, with total
leaf count probably 10million, and depth up to 20 or 25.

In light of these estimates, I suspect that it will take a looooong
time to display all full paths from the root to the leaf for a set of
leafs (suppose that my WHERE clause will be satisfied by 50,000 files). I
expect that leafs' average depth will be 8.

Q1: What is a good way to store this tree in ? (This is somewhat generic
question, so it may be a good FAQ candidate.) I want SELECTs to be fast,
and INSERTs/UPDATEs I don't care. Would making custom datatype help ? How?

Q2: What would be a good (fast) SELECT statement to show these 50,000
selected rows, but have each row of the result contain *the full path* of
the found file instead of just the file's name.

Thanx a bunch,

Jan

-- Gospel of Jesus is the saving power of God for all who believe --
## To some, nothing is impossible. ##
http://Vicherek.Waterloo.on.ca/