X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 57A5D475E82 for ; Mon, 5 May 2003 15:44:16 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id 6C40847634B for ; Mon, 5 May 2003 15:44:11 -0400 (EDT) Received: (qmail 5310 invoked by uid 500); 5 May 2003 19:45:18 -0000 Date: Mon, 5 May 2003 14:45:18 -0500 From: Bruno Wolff III To: Alex Rice Cc: pgsql-sql@postgresql.org Subject: Re: 2 simple SQL questions: optimizing aggegate query Message-ID: <20030505194518.GA5200@wolff.to> Mail-Followup-To: Alex Rice , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-Spam-Status: No, hits=-38.0 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MUTT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/65 X-Sequence-Number: 13230 On Mon, May 05, 2003 at 12:59:47 -0600, Alex Rice 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.)