Performance: Sum() - Mailing list pgsql-general

Hi.

I'm trying to understanding the PG Planner better.
No joins. here.  Just a lot of adding:

Query 1: summary for one week.

  select
    s.channel,
    sum(s.units)
  from
    summary s
  where
    s.media = 'M' and
    s.week = 200111
  group by
    s.channel
  ;

  Aggregate  (cost=2489.34..2493.03 rows=74 width=24)
    ->  Group  (cost=2489.34..2491.18 rows=739 width=24)
          ->  Sort  (cost=2489.34..2489.34 rows=739 width=24)
                ->  Index Scan using summary_media_week_channel on
summary s  (cost=0.00..2454.10 rows=739 width=24)

It finds an index and it uses it for 739 rows.  The sort stage has
little cost: 35 or ~1.5% of total query cost.

Query 2: for 10 weeks:

  select
    s.channel,
    sum(s.units)
  from
    summary s
  where
    s.media = 'M' and
    s.week > 200100 and
    s.week <= 200111
  group by
    s.channel
  ;

  Aggregate  (cost=17398.11..17765.44 rows=7347 width=24)
    ->  Group  (cost=17398.11..17581.77 rows=73466 width=24)
          ->  Sort  (cost=17398.11..17398.11 rows=73466 width=24)
                ->  Seq Scan on summary s  (cost=0.00..10608.64
rows=73466 width=24)

Now it does not want to use any of my fine indexes (see below) to
return 73,466 rows (about 1/5th the table).  Also note, the Sort stage
has a considerable cost: 6,790 or ~40% total query cost.

Questions:

1. Couldn't one of the indexes speed up the fetching of the rows?

2. What is the sort stage doing?

3. How to optimize this querey 2?

   a. add column to table with precalculated ytd figure in leu of
      weeks 01 .. 11.

   b. add rows to table with precalculated totals for the other
      columns we are not slicing on: dma, genre, price_point.

   c. speed up the query as it is some how.


The table looks like this:

              Table "summary"
    Attribute  |     Type     | Modifier
  -------------+--------------+----------
   media       | varchar(2)   | not null
   channel     | varchar(2)   | not null
   dma         | varchar(10)  | not null
   genre       | varchar(2)   | not null
   week        | numeric(6,0) | not null
   price_point | varchar(2)   |
   units       | numeric(8,0) |
  Indices: summary_channel,
           summary_channel_media_week,
           summary_channel_week,
           summary_media_channel_week,
           summary_media_week_channel,
           summary_week,
           summary_week_channel

And there are this many rows in it.

  bobcat=# select count(*) from summary;
   count
  --------
   347065
  (1 row)

Thanks, David