Re: *very* slow query to summarize data for a month ... - Mailing list pgsql-performance

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