Re: 2 simple SQL questions: optimizing aggegate query

On Mon, May 05, 2003 at 12:59:47 -0600, Alex Rice <alrice@ARCplanning.com> wrote:
> Hello, I am using Mnogosearch with a PostgreSQL backend. I am writing 
> my own queries because I can't use the existing Perl or PHP front ends 
> to Mnogosearch.
> 
> 1) Is there a way to optimize this query? It takes ~6 seconds on my 
> workstation :-( I would like to get it under 2 seconds.
> 
> SELECT url.rec_id, url, title, content_type, txt,
> sum(
>   case
>    when dict.word = 'wordx' then 1
>    when dict.word = 'wordx' then 1
>    when dict.word = 'wordy' then 1
>    else 0
>   end
> ) as rank
> FROM dict, url
> WHERE url.rec_id = dict.url_id
> GROUP BY rec_id, url, title, content_type, txt
> ORDER BY rank DESC

Is there some reason you can't put the word list in the where clause?
(You can use coalesce to set NULL sums to 0.)