From: | Alex Rice <alrice(at)ARCplanning(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | 2 simple SQL questions: optimizing aggegate query |
Date: | 2003-05-05 18:59:47 |
Message-ID: | BE0640A2-7F2B-11D7-969D-000393529642@ARCplanning.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
2) In the above query, why can't I write "HAVING rank > 0"? instead of
repeating the whole entire sum() expression "HAVING sum(...)"
Thanks in advance,
Alex Rice, Software Developer
Architectural Research Consultants, Inc.
alrice(at)ARCplanning(dot)com
alrice(at)swcp(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Teter | 2003-05-05 19:19:42 | Re: Replication for a large database |
Previous Message | Alex Rice | 2003-05-05 18:59:41 | mnogosearch examples |