X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 16359D1B560; Wed, 12 Nov 2003 16:11:33 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 11295-10; Wed, 12 Nov 2003 12:11:02 -0400 (AST) Received: from ganymede.hub.org (u46n208.hfx.eastlink.ca [24.222.46.208]) by svr1.postgresql.org (Postfix) with ESMTP id 424CAD1B550; Wed, 12 Nov 2003 12:11:02 -0400 (AST) Received: by ganymede.hub.org (Postfix, from userid 1000) id 282C5372DC; Wed, 12 Nov 2003 12:08:56 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 274CC372DA; Wed, 12 Nov 2003 12:08:56 -0400 (AST) Date: Wed, 12 Nov 2003 12:08:56 -0400 (AST) From: "Marc G. Fournier" X-X-Sender: scrappy@ganymede.hub.org To: Greg Stark Cc: "Marc G. Fournier" , Dennis Bjorklund , pgsql-performance@postgresql.org Subject: Re: *very* slow query to summarize data for a month ... In-Reply-To: <87oevii15f.fsf@stark.dyndns.tv> Message-ID: <20031112120814.H56037@ganymede.hub.org> References: <20031111134925.L56037@ganymede.hub.org> <87oevii15f.fsf@stark.dyndns.tv> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/69 X-Sequence-Number: 4629 On Wed, 12 Nov 2003, Greg Stark wrote: > > "Marc G. Fournier" writes: > > > Just as a side note, just doing a straight scan for the records, with no > > SUM()/GROUP BY involved, with the month_trunc() index is still >8k msec: > > One of the other advantages of these aggregate tables was that we could > purge the old data much sooner with much less resistance from the > business. Since the reports were all still available and a lot of ad-hoc > queries could still be done without the raw data anyways. Actually, what I've done is do this at the 'load stage' ... but same concept ...