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

[Fwd: binary tree query]

From: Jodi Kanter <jkanter(at)virginia(dot)edu>
To: Postgres Admin List <pgsql-admin(at)postgresql(dot)org>
Subject: [Fwd: binary tree query]
Date: 2004-01-26 19:16:13
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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



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


/ /

/ /


pgsql-admin by date

Next:From: Stefan HolzheuDate: 2004-01-26 19:29:33
Subject: Re: Problems with pg_dump
Previous:From: Randolf RichardsonDate: 2004-01-26 17:58:24
Subject: Re: I want to quit

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