Re: [GENERAL] playing with timestamp entries
Tom Lane wrote: > > Dale Walker <[EMAIL PROTECTED]> 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),'-MM') as date, > > sum(h.acctsessiontime), > > sum(float8(h.acctinputoctets)/100), > > sum(float8(h.acctoutputoctets)/100) > > 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 < [EMAIL PROTECTED] > Independent Computer Retailers (ICR) Pty Ltd http://www.icr.com.au/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] 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),'-MM') as date,
sum(h.acctsessiontime),
sum(float8(h.acctinputoctets)/100),
sum(float8(h.acctoutputoctets)/100)
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),'-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 < [EMAIL PROTECTED] >
Independent Computer Retailers (ICR) Pty Ltd
http://www.icr.com.au/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Replication server
Just received the email on Replication server. Based on the threads this last week, the sooner it's out there the better.. Fantastic, yah yah yah!!! -- Dale Walker [EMAIL PROTECTED] Independent Computer Retailers (ICR) http://www.icr.com.au ICRnet http://www.icr.net.au
Re: [GENERAL] FreeBSD Softupdates??
Bruce Momjian wrote: > > > No, softupdates to not affect recovery. Standard PostgreSQL flushes all > stuff to disk on transaction commit, and this it not affected by the > file system softupdate status. > AAahhh... yes I see (light goes on in head), this was sort of aluded to in on of the responses, but it didn't click. Therefore, there would be little practical benefit?? I'll try a long series of insert/select/updates and compare. -- Dale Walker [EMAIL PROTECTED] Independent Computer Retailers (ICR) http://www.icr.com.au ICRnet http://www.icr.net.au
[GENERAL] FreeBSD Softupdates??
Just a quick q. Does anyone know what effects FreeBSD's 'softupdates' would have on the /usr/local/pgsql/data filesystem. Would this help performance, or would it be a shot in the foot?? -- Dale Walker [EMAIL PROTECTED] Independent Computer Retailers (ICR) http://www.icr.com.au ICRnet http://www.icr.net.au
Re: [GENERAL] Starting postmaster at boot
Adam Lang wrote: > > I didn't directly use your method, but you nonetheless solved my problem. > >From the beginning everyone was telling me to put this into my rc.local: > su postgres -c "/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data -i > /usr/local/pgsql/postgres.log 2>&1 &" > > It was never working. I noticed you had a tag "-l" in yours for su. I > looked up the reason for it and gave it a try. So the script: > su -l postgres -c "/usr/local/pgsql/bin/postmaster -D > /usr/local/pgsql/data -i /home/postgres/postgres.log 2>1 &" > does work. > > I still don't understand the point of the 1's and 2's in the command though. it's a method for redirecting STDERR --> STDOUT this is especially usefull in scripts normally I use [scriptname >/dev/null 2>&1] to send any unwanted output to /dev/null > > Why is everyone else's script working without the -l and mine wasn't? > '-l' uses the login profile for the su'd user... this is similar to a 'simuated login', without it , it was only referencing variables from your roo profile ... (ie. no PGDATA,etc..) -- Dale Walker [EMAIL PROTECTED] Independent Computer Retailers (ICR) http://www.icr.com.au ICRnet http://www.icr.net.au
[GENERAL] vacuum taking long time
I have a machine inadvertently reboot on me this morning, and I have been running a vacuumdb command to try and check it. The vacuum command has processed the indexes OK, but it hasn't returned to a prompt as yet (3hrs) The table has only taken about 5min to do a vacuum on in the past... any suggestions?? This is what I have on my screen so far... -- $ vacuumdb --dbname=icr --table=log --verbose --echo VACUUM VERBOSE log NOTICE: --Relation log-- NOTICE: Pages 17010: Changed 0, reaped 890, Empty 0, New 0; Tup 1031544: Vac 56920, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 114, MaxLen 126; Re-using: Free/Avail. Space 7048436/7048436; EndEmpty/Avail. Pages 0/890. CPU 1.95s/0.51u sec. NOTICE: Index idx_lacctterminatecause: Pages 2049; Tuples 1031544: Deleted 963. CPU 0.26s/1.76u sec. NOTICE: Index idx_lservicetype: Pages 2047; Tuples 1031544: Deleted 963. CPU 0.30s/1.71u sec. NOTICE: Index idx_lnasporttype: Pages 2047; Tuples 1031544: Deleted 962. CPU 0.30s/1.70u sec. NOTICE: Index idx_lnasport: Pages 2594; Tuples 1031544: Deleted 962. CPU 0.36s/1.72u sec. NOTICE: Index idx_lnasip: Pages 3595; Tuples 1031544: Deleted 962. CPU 0.39s/1.82u sec. NOTICE: Index idx_luser: Pages 3303; Tuples 1031544: Deleted 962. CPU 0.33s/1.90u sec. NOTICE: Index idx_loutdate: Pages 2569; Tuples 1031544: Deleted 962. CPU 0.35s/1.72u sec. NOTICE: Index log_pkey: Pages 2173; Tuples 1031544: Deleted 962. CPU 0.27s/1.75u sec. --- -- Dale Walker [EMAIL PROTECTED] Independent Computer Retailers (ICR) http://www.icr.com.au ICRnet http://www.icr.net.au
Re: [GENERAL] creating functions
Stephan Szabo wrote: > > > but I received this as the error: > > -- > > ERROR: Unrecognized language specified in a CREATE FUNCTION: > > 'plpgsql'. Recognized languages are sql, C, internal and the > > created > > procedural languages. > > -- > > By default the procedural languages are not loaded. There's a > script createlang which you can use to load the plpgsql language > into your database. (If you want it in all future databases, > I believe if you add it to template1, all later dbs have it > enabled) got it. and guess what the sample function worked! Thanks for that.. -- Dale Walker [EMAIL PROTECTED] Independent Computer Retailers (ICR) http://www.icr.com.au ICRnet http://www.icr.net.au
[GENERAL] creating functions
Hi All, I'm trying to create a function that takes an interval (in seconds) and returns HH:MM:SS similar to reltime() but not dividing down to num# days,months etc... anyway, I've been looking into the CREATE FUNCTION routines and here I struck a stumbling block. I tried(from the doco): -- CREATE FUNCTION concat_text (text, text) RETURNS text AS ' BEGIN RETURN $1 || $2; END; ' LANGUAGE 'plpgsql'; -- but I received this as the error: -- ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the created procedural languages. -- I'm running PostgreSQL 7.0.2 on FreeBSD-4.1. I'm new to postgres (just migrating from mysql). What I'm wondering: 1. Am I missing something basic and fundamental here?? 2. Am I looking at the right doco.. I recall on the list a few weeks back there was a coment about the wrong doco being used... Any help is appreciated.. -- Dale Walker [EMAIL PROTECTED] Independent Computer Retailers (ICR) http://www.icr.com.au ICRnet http://www.icr.net.au
[GENERAL] insert into table from select..
Hi all, I'm having some trouble with the insert function and would appreciate anyones woords of wisdom... What I'm trying to do in a nutshell: 1. populate a 'working' table from a file (this is fine) 2. run sanity checks on the data (this is fine) 3. merge 'working' table into the 'main' table. (AARRGGG - not fine) eg. raw datafile: --- 1996-12-12 12:12:12|bill 1997-03-02 23:12:45|ted ..etc... --- sql statements: (table log has already been created with same parameters as 'logtmp') - create table logtmp (outdate timestamp,luser char(8)); copy logtmp from '/tmp/file' using delimiters '|'; \echo various syntax checking to make sure data is good \echo \echo Update main table with new data \set fieldnames 'outdate,luser' insert into log (:fieldnames) select :fieldnames from logtmp; - Everything works OK except for the last line... I know the variable 'fieldnames' works though on 'select :fieldnames from logtmp' When I actually use this in the production environment, the main table will have _one_ extra field as a serial sequence (this is why I used the fieldnames variable, otherwise I have about 20 fields), I thought this was my problem at first, but when I simplified the data right down, it still fails. Any thoughts?? Thanks in Advance. -- Dale Walker [EMAIL PROTECTED]
[GENERAL] Table Design: Timestamp vs time/date
I'm currently setting up a table to contain user login/logout information. Just wondered what would be more scalable??better to index,etc... Having a 'timestamp' field 'CCYY-MM-DD HH:MM:SS.SS' or two separate fields one for time 'HH:MM:SS.SS' and one for Date 'CCYY-MM-DD'. most queries will be of the form: select from where username='xxx' and date<'CCYY-MM-DD' and date>'CCYY-MM-DD' so I was thinking that a separate 'Date only' field would index better.. Any Thoughts?? -- Dale Walker [EMAIL PROTECTED]
