pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (19+ messages)  latest in thread

Message-ID: <878ymmkdb9.fsf@stark.dyndns.tv>
Permalink:  ../878ymmkdb9.fsf@stark.dyndns.tv/
Also on:    postgresql.org/message-id/878ymmkdb9.fsf@stark.dyndns.tv

 · 

reply

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