Re: playing with timestamp entries

Tom Lane wrote:
>
> Dale Walker <dale(at)icr(dot)com(dot)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(at)icr(dot)com(dot)au >
Independent Computer Retailers (ICR) Pty Ltd
http://www.icr.com.au/