FW: query efficiency - Can I speed it up?
От | Duncan Garland |
---|---|
Тема | FW: query efficiency - Can I speed it up? |
Дата | |
Msg-id | MBEPKEEDMKGCDODFKLPPAEHDDMAA.duncan.garland@ntlworld.com обсуждение исходный текст |
Список | pgsql-novice |
-----Original Message----- From: Duncan Garland [mailto:duncan.garland@ntlworld.com] Sent: 01 February 2007 12:53 To: ann hedley Cc: pgsql-novice-owner@postgresql.org Subject: RE: query efficiency - Can I speed it up? Does putting an index on description help? -----Original Message----- From: ann hedley [mailto:ann.hedley@ed.ac.uk] Sent: 31 January 2007 17:34 To: pgsql-novice-owner@postgresql.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)
В списке pgsql-novice по дате отправления: