Re: 2 simple SQL questions: optimizing aggegate query

On Mon, May 05, 2003 at 12:59:47 -0600,
Alex Rice <alrice(at)ARCplanning(dot)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.)