Thread: Performance: Sum()

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

> 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?

Probably not right now.  The tuples still need to be read in order
to get the mvcc information, so after a certain point the index
scan becomes more expensive as you have to read the index and the
heap rows -- if you're reading almost all of the heap pages,
sequence scan is most likely faster.  One question is, is 73466 a
reasonable estimate of the number of rows?

> 2. What is the sort stage doing?

Ordering the input for the group step.  IIRC, the group by
implementation expects its inputs in sorted order.

> 3. How to optimize this querey 2?

I guess a starting point would be to see how it runs with
set enable_seqscan=off
What does explain show and does it run faster?