Skip site navigation (1) Skip section navigation (2)

Re: [Fwd: binary tree query]

From: Jodi Kanter <jkanter(at)virginia(dot)edu>
To: Yuji Shinozaki <ys2n(at)virginia(dot)edu>
Cc: Postgres Admin List <pgsql-admin(at)postgresql(dot)org>
Subject: Re: [Fwd: binary tree query]
Date: 2004-01-29 14:10:41
Message-ID: 40191461.9030800@virginia.edu (view raw or flat)
Thread:
Lists: pgsql-admin
Thanks for the article. It did help some but I am still not sure if 
representing a tree is best served using a self referential table. 
Wouldn't it be better to separate it into several tables?
Can anyone else comment? Anyone have experience storing data that is 
hierarchical in nature (e.g. a tree formation).
Jodi

Yuji Shinozaki wrote:

>Hi Jodi,
>
>I believe the technique they are using is representing a tree as nested
>sets.  It requires that the database is built with properly nested
>left_id's and right_id's, and this technique is often regarded as a
>efficient means for retrieving hierachical information.
>
>Here is one reference about it:
>
>http://www.geocrawler.com/archives/3/6/2001/10/0/6961775/
>
>As for the inner join clauses they are in effect analogous to
>where's but the optimizer handles them differently.  That is where
>(pardon the pun) my understanding dwindles.  Perhaps someone else has
>better insight about inner join's vs where's.
>
>Hope this sheds some light and not too many shadows,
>
>yuji
>----
>
>
>On Mon, 26 Jan 2004, Jodi Kanter wrote:
>
>  
>
>>I have a biochemist telling me that this query below is a typical one
>>for crawling through a taxonomic tree and that this is how I should
>>represent some peptide information we have. Is there anyone on this list
>>familiar with such data? I am weak in the science department but this
>>query looks like it might not be the most efficient approach.
>>I have not been able to run an explain analyze yet as the database
>>structure and data are not in place yet. We are just in the planning
>>stages right now.
>>
>>Any comments, suggestions, concerns, etc. would be much appreciated.
>>Would an experienced DBA recommend a different approach? Can anyone
>>offer some insight into the usefulness of INNER joins and the use of
>>BETWEEN? I am concernec about performance as well since I expect this
>>table to get large.
>>
>>SELECT count(*)
>>  FROM taxon_name
>>       INNER JOIN taxon AS tax_b USING(taxon_id)
>>       INNER JOIN taxon AS tax_v ON (tax_v.left_id BETWEEN
>>tax_b.left_id AND tax_b.right_id )
>>       INNER JOIN annot ON (tax_v.taxon_id = annot.taxon_id)
>>       INNER JOIN protein ON (protein.prot_id= annot.prot_id)
>> WHERE annot.pref = 1
>>   AND taxon_name.taxon_id=207245
>>;
>>
>>
>>
>>Thanks,
>>Jodi
>>
>>--
>>
>>/_______________________________
>>//Jodi L Kanter
>>BioInformatics Database Administrator
>>University of Virginia
>>(434) 924-2846
>>jkanter(at)virginia(dot)edu <mailto:jkanter(at)virginia(dot)edu>/
>>
>>
>>
>>/ /
>>
>>/ /
>>
>>
>>    
>>
>
>Yuji Shinozaki	                        Computer Systems Senior Engineer
>ys2n(at)virginia(dot)edu			Advanced Technologies Group
>(434)924-7171				Information Technology & Communication
>http://www.people.virginia.edu/~ys2n    University of Virginia
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>  
>

-- 

/_______________________________
//Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter(at)virginia(dot)edu <mailto:jkanter(at)virginia(dot)edu>/

 

/ /

/ /

In response to

pgsql-admin by date

Next:From: Rick van DijkDate: 2004-01-29 14:35:33
Subject: Re: Are there programs to graph database schema?
Previous:From: Frank WayDate: 2004-01-29 14:09:34
Subject: Re: How to determine which file contains which block

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group