Re: playing with timestamp entries

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.

> 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