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 20:28:18 -0500
Message-ID: <20030506012818.GA8384@wolff.to> (raw)
In-Reply-To: <9D0F6008-7F34-11D7-977E-000393529642@ARCplanning.com>
References: <20030505194518.GA5200@wolff.to>
<9D0F6008-7F34-11D7-977E-000393529642@ARCplanning.com>
On Mon, May 05, 2003 at 14:03:18 -0600,
Alex Rice <alrice@ARCplanning.com> wrote:
>
> On Monday, May 5, 2003, at 01:45 PM, Bruno Wolff III wrote:
>
> >>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.)
>
> Can you give an example of what you mean use coalesce and how to use it
> to produce a count of the matched words, the rank?
You don't really need coalesce. I mentioned that because if you take
the sum of zero rows (the case where no words match for one of the returned
rows) then you get NULL instead of 0 (which may or may not be a problem
for you).
> I am converting some existing query examples that use the form
> SUM( dict.word IN ( word list...))
> which doesn't work with pgsql.
You want to do this outside the sum, not inside (and change sum to
count). For example:
select count(dict.word) from dict where word in (word list ...);
(If word list is a select rather than a list of constants, you currently
want to use exists instead for performance reasons. In 7.4 that won't
be a problem.)
By putting things in the where close you don't have to examine all of
the rows for each word - url pair. This may cut out a lot of rows that
need to be examined if there is an index on dict.word and the number of
words in a url is large relative to the number being searched on.
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: <20030506012818.GA8384@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