From: Greg Stark <gsstark@mit.edu>
To: Dennis Bjorklund <db@zigo.dhs.org>
Cc: Marc G. Fournier <scrappy@postgresql.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: *very* slow query to summarize data for a month ...
Date: 11 Nov 2003 12:47:06 -0500
Message-ID: <878ymmkdb9.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <Pine.LNX.4.44.0311110747090.26313-100000@zigo.dhs.org>
References: <Pine.LNX.4.44.0311110747090.26313-100000@zigo.dhs.org>
Dennis Bjorklund <db@zigo.dhs.org> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsstark@mit.edu, db@zigo.dhs.org, scrappy@postgresql.org
Subject: Re: *very* slow query to summarize data for a month ...
In-Reply-To: <878ymmkdb9.fsf@stark.dyndns.tv>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox