playing with timestamp entries
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/
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
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/