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

Re: [Fwd: binary tree query]

From: Yuji Shinozaki <ys2n(at)virginia(dot)edu>
To: Jodi Kanter <jkanter(at)virginia(dot)edu>
Cc: Postgres Admin List <pgsql-admin(at)postgresql(dot)org>
Subject: Re: [Fwd: binary tree query]
Date: 2004-01-26 19:36:15
Message-ID: Pine.LNX.4.44.0401261422120.29962-100000@atg2000.itc.virginia.edu (view raw or flat)
Thread:
Lists: pgsql-admin
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


In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2004-01-26 22:48:35
Subject: Re: Problems with pg_dump
Previous:From: Stefan HolzheuDate: 2004-01-26 19:29:33
Subject: Re: Problems with pg_dump

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