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

Re: query efficiency - Can I speed it up?

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: pgsql-novice(at)postgresql(dot)org
Cc: ann hedley <ann(dot)hedley(at)ed(dot)ac(dot)uk>, Duncan Garland <duncan(dot)garland(at)ntlworld(dot)com>
Subject: Re: query efficiency - Can I speed it up?
Date: 2007-02-01 17:33:31
Message-ID: 200702011233.31654.sdavis2@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
On Thursday 01 February 2007 12:01, ann hedley wrote:
> Thanks but no.  I think because it's description~ (like) rather than
> description= therefore it has to search the whole string in each
> description.
>
>  From the output below it seems to search the 'go' table once, cost 266,
> then for each record found searches the 'gotcha' table (cost 46809). So
> it's the gotcha part of the query I need to speed up, yes?
>
> Maybe I just need a clever bit of PHP that checks the cost and if it's
> excessive asks for a more specific search string?
>
> Thanks
>
> nemdb3=# explain analyse select pept_id,description,confidence from
> gotcha inner join go on (gotcha.go_term=go.go_term) where go.description
> like 'tRNA acetylation' and ( spid='ALP' or spid='ASP' or spid='DIP' or
> spid='GPP' );

Ann,

You might think about full-text searching of the descriptions.  It may be 
quite a bit faster than a "like" search.  It may be worth looking.

Sean

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2007-02-01 17:57:46
Subject: Re: Newbie Developer Question
Previous:From: ann hedleyDate: 2007-02-01 17:13:41
Subject: Re: [Fwd: query efficiency - Can I speed it up?]

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