Thread: 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/
Re: [GENERAL] storing a tree-like structure and selecting path from leaf to root
From
Herouth Maoz
Date:
At 09:44 +0300 on 15/08/1999, Jan Vicherek wrote: > 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? About a year and a half ago, there was a book recommendation about this issue. The book discusses advanced data structures representation with SQL. Now that PostgreSQL has subqueries and unions, it becomes more relevant. The book was: Joe Celko's SQL for Smarties Advanced SQL Programming The publisher: Morgan Kaufmann Publishers, Inc 340 Pine St 6th Floor San Francisco CA 94104-33205 USA 415-392-2665 mkp@mkp.com http://www.mkp.com The original poster of this recommendation was Terry Harple, and it was on the (now defunct) QUESTIONS list. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma