2 simple SQL questions: optimizing aggegate query

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

Responses

Browse pgsql-sql by date

  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