Re: playing with timestamp entries

Tom Lane wrote:
>
> Dale Walker <dale@icr.com.au> writes:
> > I use the 'hash' type as queries regarding usage will always be of the
> > form "select ...... where username='xxx';"
>
> Use a btree anyway.  Postgres' btree implementation is much better than
> its hash index implementation.
>

OK, I'll give that a whirl...



> > insert into sumlog
> >       select  s.username,
> >                 to_char(timestamp(h.time_stamp),'YYYY-MM') as date,
> >                 sum(h.acctsessiontime),
> >                 sum(float8(h.acctinputoctets)/1000000),
> >                 sum(float8(h.acctoutputoctets)/1000000)
> >         from subscribers as s,history as h
> >       where s.username=h.username
> >       group by s.username,date;
>
> > This works fine, but as the database size is constantly growing the
> > summary table takes a while to calculate...
>
> What plan does EXPLAIN show for this query?
>
>                         regards, tom lane

psql:zz.sql:7: NOTICE:  QUERY PLAN:

Aggregate  (cost=349984.03..365862.83 rows=127030 width=40)
  ->  Group  (cost=349984.03..356335.55 rows=1270304 width=40)
        ->  Sort  (cost=349984.03..349984.03 rows=1270304 width=40)
              ->  Hash Join  (cost=27.35..87635.90 rows=1270304
width=40)
                    ->  Seq Scan on history h  (cost=0.00..36786.04
rows=1270304 width=28)
                    ->  Hash  (cost=25.28..25.28 rows=828 width=12)
                          ->  Seq Scan on subscribers s
(cost=0.00..25.28 rows=828 width=12)

EXPLAIN

----------

The way I read this, I think my biggest problem is in the
sorting/grouping...


--
Dale Walker < dale@icr.com.au >
Independent Computer Retailers (ICR) Pty Ltd
http://www.icr.com.au/