[Fwd: binary tree query]

Поиск
Список
Период
Сортировка
От Jodi Kanter
Тема [Fwd: binary tree query]
Дата
Msg-id 4015677D.3090808@virginia.edu
обсуждение исходный текст
Ответы Re: [Fwd: binary tree query]  (Yuji Shinozaki <ys2n@virginia.edu>)
Список 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
;


Thanks,
Jodi

--

_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu


 

 

 

В списке pgsql-admin по дате отправления:

Предыдущее
От: Randolf Richardson
Дата:
Сообщение: Re: I want to quit
Следующее
От: Stefan Holzheu
Дата:
Сообщение: Re: Problems with pg_dump