Re: *very* slow query to summarize data for a month ...

Dennis Bjorklund <db(at)zigo(dot)dhs(dot)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