From: Bruno Wolff III <bruno@wolff.to>
To: Alex Rice <alrice@ARCplanning.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: 2 simple SQL questions: optimizing aggegate query
Date: Mon, 5 May 2003 14:45:18 -0500
Message-ID: <20030505194518.GA5200@wolff.to> (raw)
In-Reply-To: <BE0640A2-7F2B-11D7-969D-000393529642@ARCplanning.com>
References: <BE0640A2-7F2B-11D7-969D-000393529642@ARCplanning.com>
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.)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, alrice@ARCplanning.com
Subject: Re: 2 simple SQL questions: optimizing aggegate query
In-Reply-To: <20030505194518.GA5200@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox