playing with timestamp entries - Mailing list pgsql-general

I record our radius logs in a firly basic table, schema is as follows:
----------------------------------------------------------
CREATE TABLE "history_new" (
        "username" character varying(50) NOT NULL,
        "time_stamp" int4 NOT NULL,
        "acctstatustype" character varying(8) NOT NULL ,
        "acctdelay" int2 NOT NULL,
        "acctinputoctets" int4 ,
        "acctoutputoctets" int4 ,
        "acctsessionid" character varying(30),
        "acctsessiontime" int4 ,
        "acctterminatecause" character varying(50),
        "nasidentifier" character varying(22),
        "nasport" character varying(4),
        "framedipaddress" character varying(16),
        "callingstationid" character varying(16),
        "ascenddatarate" character varying(16),
        "calledstationid" character varying(16)
);
-----------------------------------------------------------

I then create an index on the username column by:

---------------------------------------
CREATE INDEX "i_h_uh" on HISTORY using hash (username);
---------------------------------------

I use the 'hash' type as queries regarding usage will always be of the
form "select ...... where username='xxx';"

I also calculate a 'summary' in the form of a table I call sumlog:

-----------------------------------------------------------
CREATE TABLE "sumlog" (
        "username" character varying(8) NOT NULL,
        "period" character varying(8) NOT NULL,
        "sumtime" int4 DEFAULT '0' ,
        "mbup" float8 DEFAULT '0.0' ,
        "mbdn" float8 DEFAULT '0.0' );
------------------------------------------------------------

This table is populated by the following query:
-----------------------------------------------------
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...

I was thinking of doing the following:
    1. only update 'sumlog' for the current period (eg. 2001-04)
    2. adding an index on the timestamp column to speed-up the query for
the insert into sumlog.


My questions are:
    1. is it possible to create an index entry on the function applied to
the time_stamp.
        eg. something of the form [ create index "i_ts" on history
(to_char(timestamp(h.time_stamp),'YYYY-MM')) ]
    2. what is the best way to access the data from the history table for a
known period..
        eg. knowing period='2001-04' is there a better function to use than
'to_char' against the timestamp, any sort of indexing I should use,
etc...

I've been going around in circles, and I'm sure I've missed some
basic/common-sense sort of step, but now I'm running out of time to
spend on it....

Has anyone here done anything similar??


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