X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E932F476533 for ; Tue, 6 May 2003 15:28:09 -0400 (EDT) Received: from taka.swcp.com (taka.swcp.com [198.59.115.12]) by postgresql.org (Postfix) with ESMTP id 37257476531 for ; Tue, 6 May 2003 15:28:09 -0400 (EDT) Received: from ARCplanning.com (pcp02742825pcs.sntafe01.nm.comcast.net [68.35.10.62]) by taka.swcp.com (8.12.9/8.12.9) with ESMTP id h46JS7QF055025; Tue, 6 May 2003 13:28:08 -0600 (MDT) Date: Tue, 6 May 2003 13:28:05 -0600 Subject: Re: 2 simple SQL questions: optimizing aggegate query Content-Type: text/plain; charset=US-ASCII; format=flowed Mime-Version: 1.0 (Apple Message framework v552) Cc: pgsql-sql@postgresql.org To: Bruno Wolff III From: Alex Rice In-Reply-To: <20030506012818.GA8384@wolff.to> Message-Id: Content-Transfer-Encoding: 7bit X-Mailer: Apple Mail (2.552) X-Spam-Status: No, hits=-28.3 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REPLY_WITH_QUOTES,USER_AGENT_APPLEMAIL 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/103 X-Sequence-Number: 13268 On Monday, May 5, 2003, at 07:28 PM, Bruno Wolff III wrote: >> 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. > Bruno: Thanks! Using your advice, my queries are now < 1 sec! Using this form SELECT url_id, url.url, url.txt, url.content_type, url.docsize, count(dict.word) AS rank FROM url,dict WHERE word IN ('word1', 'word2', ...) AND url.rec_id = dict.url_id GROUP BY url_id, url, txt, content_type, docsize ORDER BY rank DESC; I have also upgraded to pgsql 7.3.2, and the query I posted yesterday "sum( case ... ) " is a lot faster as well (about 2 seconds now). So I think maybe there was also something wrong with my indices yesterday- in addition to there being something wrong with my SQL. :-) Thanks for the SQL advice. Alex Rice, Software Developer Architectural Research Consultants, Inc. alrice@ARCplanning.com alrice@swcp.com