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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Douglas, James | 2010-03-16 22:47:31 | ** PostgreSQL Developers NEEDED, SYDNEY Northern Beaches, $70K - 95K Package** |