Dennis Bjorklund <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
