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 064C5D1B50D; Tue, 11 Nov 2003 19:51:53 +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 11750-10; Tue, 11 Nov 2003 15:51:23 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 29BF3D1B4FE; Tue, 11 Nov 2003 15:51:22 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 9826F36AD6; Tue, 11 Nov 2003 14:51:22 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1AJeXq-0000EA-00; Tue, 11 Nov 2003 14:51:22 -0500 To: "Marc G. Fournier" Cc: Greg Stark , Dennis Bjorklund , pgsql-performance@postgresql.org Subject: Re: *very* slow query to summarize data for a month ... References: <878ymmkdb9.fsf@stark.dyndns.tv> <20031111141328.X56037@ganymede.hub.org> In-Reply-To: <20031111141328.X56037@ganymede.hub.org> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 11 Nov 2003 14:51:22 -0500 Message-ID: <873ccuk7k5.fsf@stark.dyndns.tv> Lines: 21 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/46 X-Sequence-Number: 4606 "Marc G. Fournier" writes: > On Tue, 11 Nov 2003, Greg Stark wrote: > > > Actually you might be able to get the same effect using function indexes > > like: > > > > create index i on traffic_log (month_trunc(runtime), company_id) > > had actually thought of that one ... is it something that is only > available in v7.4? Hum, I thought you could do simple functional indexes like that in 7.3, but perhaps only single-column indexes. In any case, given your situation I would seriously consider putting a "month" integer column on your table anyways. Then your index would be a simple (month, company_id) index. -- greg