FW: query efficiency - Can I speed it up?

-----Original Message-----
From: Duncan Garland [mailto:duncan(dot)garland(at)ntlworld(dot)com]
Sent: 01 February 2007 12:53
To: ann hedley
Cc: pgsql-novice-owner(at)postgresql(dot)org
Subject: RE: query efficiency - Can I speed it up?

Does putting an index on description help?

-----Original Message-----
From: ann hedley [mailto:ann(dot)hedley(at)ed(dot)ac(dot)uk]
Sent: 31 January 2007 17:34
To: pgsql-novice-owner(at)postgresql(dot)org
Subject: query efficiency - Can I speed it up?

Dear All

Can anyone tell me if/how I can speed up this query? You could just
point me at a good 'guide to building efficient queries' if I'm missing
something obvious. I've tried a few online sources and most of my
queries are much faster now but this one ???

Thanks

nemdb3=# explain select pept_id,description,confidence from gotcha inner
join go on (gotcha.go_term=go.go_term) where go.description~'oxygen' and
( spid='ALP' or spid='ASP' or spid='DIP' or spid='GPP' ) order by
gotcha.confidence;

QUERY PLAN
----------------------------------------------------------------------------
--------------------------------------------------------------------------
Sort (cost=187523.03..187523.54 rows=203 width=54)
Sort Key: gotcha.confidence
-> Nested Loop (cost=0.00..187515.25 rows=203 width=54)
-> Seq Scan on go (cost=0.00..266.50 rows=4 width=50)
Filter: (description ~ 'oxygen'::text)
-> Index Scan using gotcha_go_term_sp_id on gotcha
(cost=0.00..46809.29 rows=232 width=32)
Index Cond: ((gotcha.go_term)::text =
("outer".go_term)::text)
Filter: (((spid)::text = 'ALP'::text) OR ((spid)::text =
'ASP'::text) OR ((spid)::text = 'DIP'::text) OR ((spid)::text =
'GPP'::text))
(8 rows)

nemdb3=# \d go
Table "public.go"
Column | Type | Modifiers
-----------------+-----------------------+-----------
go_term | character varying(14) |
go_asp | character varying(1) |
description | text |
hierarchy_level | integer |
Indexes:
"go_go_term" btree (go_term)

nemdb3=# \d gotcha
Table "public.gotcha"
Column | Type | Modifiers
------------+-----------------------+-----------
run_no | character varying(20) |
pept_id | character varying(15) |
contig | integer |
confidence | integer |
go_term | character varying(14) |
hits | integer |
spid | character varying(5) |
Indexes:
"gotcha_go_term_sp_id" btree (go_term, spid)
"gotcha_pept_id" btree (pept_id)
"gotcha_spid" btree (spid)

nemdb3=# select relname, relkind, reltuples, relpages from pg_class
where relname like 'go%';
relname | relkind | reltuples | relpages
------------------------+---------+-------------+----------
gotcha | r | 2.70698e+07 | 695188
go | r | 10600 | 134
gotcha_pept_id | i | 2.70698e+07 | 104266
go_go_term | i | 10600 | 43
gotcha_spid | i | 2.70698e+07 | 74225
gotcha_go_term_sp_id | i | 2.70698e+07 | 134201
(11 rows)

nemdb3=#

--
Ann

"In a world without walls and fences - who needs Windows and Gates ?"
(unknown)