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 79EEBD1B564; Tue, 11 Nov 2003 17:47: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 88841-08; Tue, 11 Nov 2003 13:47:23 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 23559D1B51E; Tue, 11 Nov 2003 13:47:22 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id C15FC36A82; Tue, 11 Nov 2003 12:47:06 -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 1AJcba-0007BP-00; Tue, 11 Nov 2003 12:47:06 -0500 To: Dennis Bjorklund Cc: "Marc G. Fournier" , pgsql-performance@postgresql.org Subject: Re: *very* slow query to summarize data for a month ... References: In-Reply-To: From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 11 Nov 2003 12:47:06 -0500 Message-ID: <878ymmkdb9.fsf@stark.dyndns.tv> Lines: 41 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/40 X-Sequence-Number: 4600 Dennis Bjorklund writes: > On Mon, 10 Nov 2003, Marc G. Fournier wrote: > > > > > explain analyze SELECT ts.company_id, company_name, SUM(ts.bytes) AS total_traffic > > FROM company c, traffic_logs ts > > WHERE c.company_id = ts.company_id > > AND month_trunc(ts.runtime) = '2003-10-01' > > GROUP BY company_name,ts.company_id; So depending on how much work you're willing to do there are some more dramatic speedups you could get: Use partial indexes like this (you'll need one for every month): create index i on traffic_log (company_id) where month_trunc(runtime) = '2003-10-01' then group by company_id only so it can use the index: select * from company join ( select company_id, sum(bytes) as total_traffic from traffic_log where month_trunc(runtime) = '2003-10-01' group by company_id ) as x using (company_id) order by company_name 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) -- greg