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

slow SQL query

From: Qiu Yuan <qiu101(at)yahoo(dot)com>
To: adelaide-au-pug(at)postgresql(dot)org
Subject: slow SQL query
Date: 2009-07-31 20:22:19
Message-ID: 978030.46610.qm@web112504.mail.gq1.yahoo.com (view raw or flat)
Thread:
Lists: adelaide-au-pug
Could you please tell why this simple postgreSQL query takes over 10 min to give result?

select distinct protein_info_table.com_id, annotator, common_name, synonyms 
from protein_info_table, protein_feature_table 
where 
(protein_info_table.sim_id = protein_feature_table.sim_id and protein_feature_table.scaffold ilike '%Scaffoldi3148%') or protein_info_table.com_id ilike '%013015%';

The two table's structures are:

1. protein_info_table
 sim_id                        | character varying(100) | not null default 'none'::character varying
 com_id                          | character varying(100) | not null
 annotator                       | character varying(200) | not null default 'none'::character varying
 model               | character varying(50)  | not null default 'none'::character varying
 evidence             | character varying(50)  | not null default 'none'::character varying
 family                  | text                   | not null default 'none'::text
 common_name                     | character varying(100) | not null default 'none'::text
 synonyms                        | text                   | not null default 'none'::text

2. protein_feature_table
 scaffold   | character varying(50)  | not null default 'none'::character varying
 source     | character varying(50)  | not null default 'none'::character varying
 feature    | character varying(50)  | not null default 'none'::character varying
 gene_start | integer                | not null default -1
 gene_end   | integer                | not null default -1
 score      | character varying(50)  | not null default 'none'::character varying

Here is the EXPLAIN ANALYSIS result:
-------------------------------------------------------------------------
 Unique  (cost=679701663.74..679765485.18 rows=126661 width=886)
   ->  Sort  (cost=679701663.74..679714428.03 rows=5105715 width=886)
         Sort Key: protein_info_table.com_id, protein_info_table.annotator, protein_info_table.common_name, protein_info_table.synonyms
         ->  Nested Loop  (cost=0.00..664867718.11 rows=5105715 width=886)
               Join Filter: (((("outer".sim_id)::text = ("inner".sim_id)::text) AND (("inner".scaffold)::text ~~* '%Scaffoldi3148%'::text)) OR (("outer".com_id)::text ~~* '%013015%'::text))
               ->  Seq Scan on protein_info_table  (cost=0.00..5446.61 rows=126661 width=1104)
               ->  Seq Scan on protein_feature_table  (cost=0.00..3044.69 rows=125969 width=336)
(7 rows)
 
Thank you for any advice.
Qiul


      

adelaide-au-pug by date

Next:From: Douglas, JamesDate: 2010-03-16 22:47:31
Subject: ** PostgreSQL Developers NEEDED, SYDNEY Northern Beaches, $70K - 95K Package**

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