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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-02-01 17:57:46 | Re: Newbie Developer Question |
Previous Message | ann hedley | 2007-02-01 17:13:41 | Re: [Fwd: query efficiency - Can I speed it up?] |