Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id D4FE09FB254 for ; Tue, 3 Oct 2006 17:04:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 95934-05 for ; Tue, 3 Oct 2006 20:04:05 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 901129FB24C for ; Tue, 3 Oct 2006 17:04:04 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 75A5730DF1; Tue, 3 Oct 2006 22:04:03 +0200 (MET DST) From: Chris Browne X-Newsgroups: pgsql.performance Subject: Re: BUG #2658: Query not using index Date: Tue, 03 Oct 2006 15:18:36 -0400 Organization: cbbrowne Computing Inc Lines: 16 Message-ID: <60d599gqcz.fsf@dba2.int.libertyrms.com> References: <200609272056.k8RKuWjY040283@wwwmaster.postgresql.org> <20061003000147.GA31475@wolff.to> <4522AA01.4020709@refractions.net> <60irj1gr3r.fsf@dba2.int.libertyrms.com> <4522B5B9.2050206@refractions.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.19 (linux) Cancel-Lock: sha1:Jywn8I7xpsy3ONrIXH5K+gWnL3M= To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.273 tagged_above=0 required=5 tests=INFO_TLD X-Spam-Level: * X-Archive-Number: 200610/54 X-Sequence-Number: 21183 gdavis@refractions.net (Graham Davis) writes: > 40 seconds is much too slow for this query to run and I'm assuming > that the use of an index will make it much faster (as seen when I > removed the GROUP BY clause). Any tips? Assumptions are dangerous things. An aggregate like this has *got to* scan the entire table, and given that that is the case, an index scan is NOT optimal; a seq scan is. An index scan is just going to be slower. -- let name="cbbrowne" and tld="linuxdatabases.info" in String.concat "@" [name;tld];; http://cbbrowne.com/info/linux.html "The computer is the ultimate polluter: its feces are indistinguishable from the food it produces." -- Alan J. Perlis