[SQL] Best database structure for timely ordered values
Hi! I am thinking about the best structure of a database to describe timely ordered values. The timely ordered values are the results of different sensors measuring environmental parameters like temperatur, pressure, humidity. The database has to define the kind of measurements, kind of sensors, place of them, ie., more or less static information. The main part will be the measurement values ordered by the time or epoch due to UTC. The primary index must be the epoch. As there will be no deletion from this data I fear - due to the internal representation of B-trees - the performance will degrade very soon. The expected number of measurements will easily reach some millions. Any comments or ideas? Reiner
Re: [SQL] Best database structure for timely ordered values
Bruce Momjian wrote: > > > Reiner Dassing <[EMAIL PROTECTED]> writes: > > > The primary index must be the epoch. > > > As there will be no deletion from this data I fear - due to the > > > internal representation of B-trees - the performance will degrade very > > > soon. > > > > Nonsense. btree should work just fine for that. Use a timestamp > > column for the primary key, and away you go. > > > > (Actually, time alone doesn't seem like it'd be necessarily unique, > > so maybe you don't want to call it a primary key. But certainly > > you can make a non-unique index on that column.) > > I assume the user is concerned about non-balanced btrees. That is correct! As I tested an example database with about 7 million entries on PostgreSQL V 6.5.2 and the result of select count(*) from table; tooks about 3 minutes I have this concern. May be version 7.0.3 will give a better result. > Ours are > auto-balancing. > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 -- Reiner Dassing
Re: [SQL] Best database structure for timely ordered values
Bruce Momjian wrote: > > The larger problem is that count(*) doesn't use the index at all. It > just does a sequential scan of the heap table. Here is another example, which is even worse: select value from table where id=1 order by epoch desc limit 1; to ask for the last stored value. This request needs about 5 minutes to complete. (But please consider that the postgres server has some other requests to serve. The machine running this server is an alpha server with Tru64) The explain statements results in: explain select * from table where id=1 order by epoche desc limit 1; NOTICE: QUERY PLAN: Sort (cost=12692.74 rows=202175 width=16) -> Index Scan using wetter_pkey on table (cost=12692.74 rows=202175 width=16) EXPLAIN -- Reiner Dassing
Re: [SQL] Index of a table is not used (in any case)
Hello all! Thank you for the answers I got. I would like to mention first, that I will use the [SQL] list for my answers, regarding the notice of Josh Berkus. Q: "did you use VACUUM ANALYZE"? A: This table was a test bed, just using INSERTS without ANY deletes or updates (See: vacuum verbose analyze wetter; NOTICE: --Relation wetter-- NOTICE: Pages 149752: Changed 0, reaped 194, Empty 0, New 0; Tup 2034: Vac 26169, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 52, MaxLen 52; Re-using: Free/Avail. Space 1467792/1467792; EndEmpty/Avail. Pages 0/194. CPU 6.10s/1.78u sec. ) Q: You should upgrade to 7.1.3? A: Can you tell me the specific the reason? Am afraid, that the real answer is not mentioned: Why is the index used in the SELECT: select * from wetter order by epoche desc; select * from wetter where epoche between '1970-01-01' and '1980-01-01' order by epoche asc; ? Any ideas? -- Mit freundlichen Gruessen / With best regards Reiner Dassing ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Index of a table is not used (in any case)
Hello PostgreSQl Users! PostSQL V 7.1.1: I have defined a table and the necessary indices. But the index is not used in every SELECT. (Therefore, the selects are *very* slow, due to seq scan on 20 million entries, which is a test setup up to now) The definitions can be seen in the annex. Does some body know the reason and how to circumvent the seq scan? Is the order of index creation relevant? I.e., should I create the indices before inserting entries or the other way around? Should a hashing index be used? (I tried this, but I got the known error "Out of overflow pages") (The docu on "create index" says : "Notes The Postgres query optimizer will consider using a btree index whenever an indexed attribute is involved in a comparison using one of: <, <=, =, >=, > The Postgres query optimizer will consider using an rtree index whenever an indexed attribute is involved in a comparison using one of: <<, &<, &>, >>, @, ~=, && The Postgres query optimizer will consider using a hash index whenever an indexed attribute is involved in a comparison using the = operator. " The table entry 'epoche' is used in two different indices. Should that be avoided? Any suggestions are welcome. Thank you in advance. Reiner -- Annex: == Table: -- \d wetter Table "wetter" Attribute | Type | Modifier ---+--+-- sensor_id | integer | not null epoche| timestamp with time zone | not null wert | real | not null Indices: wetter_epoche_idx, wetter_pkey \d wetter_epoche_idx Index "wetter_epoche_idx" Attribute | Type ---+-- epoche| timestamp with time zone btree \d wetter_pkey Index "wetter_pkey" Attribute | Type ---+-- sensor_id | integer epoche| timestamp with time zone unique btree (primary key) Select where index is used: explain select * from wetter order by epoche desc; NOTICE: QUERY PLAN: Index Scan Backward using wetter_epoche_idx on wetter (cost=0.00..3216018.59 rows=2034 width=16) EXPLAIN Select where the index is NOT used: === explain select * from wetter where epoche between '1970-01-01' and '1980-01-01' order by epoche asc; NOTICE: QUERY PLAN: Sort (cost=480705.74..480705.74 rows=203400 width=16) -> Seq Scan on wetter (cost=0.00..454852.00 rows=203400 width=16) EXPLAIN -- Mit freundlichen Gruessen / With best regards Reiner Dassing ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [SQL] Index of a table is not used (in any case)
Hello Tom! Tom Lane wrote: > > Reiner Dassing <[EMAIL PROTECTED]> writes: > > explain select * from wetter order by epoche desc; > > NOTICE: QUERY PLAN: > > > Index Scan Backward using wetter_epoche_idx on wetter > > (cost=0.00..3216018.59 rows=2034 width=16) > > > explain select * from wetter where epoche between '1970-01-01' and > > '1980-01-01' order by epoche asc; > > NOTICE: QUERY PLAN: > > > Sort (cost=480705.74..480705.74 rows=203400 width=16) > > -> Seq Scan on wetter (cost=0.00..454852.00 rows=203400 width=16) > > It's hard to believe that you've done a VACUUM ANALYZE on this table, > since you are getting a selectivity estimate of exactly 0.01, which > just happens to be the default selectivity estimate for range queries. > How many rows are there really in this date range? > Well, I did not claim that i made a VACUUM ANALYZE, I just set up a new table for testing purposes doing just INSERTs. After VACUUM ANALYSE the results look like: explain select * from wetter where epoche between '1970-01-01' and test_wetter-# '1980-01-01' order by epoche asc; NOTICE: QUERY PLAN: Index Scan using wetter_epoche_idx on wetter (cost=0.00..3313780.74 rows=20319660 width=16) EXPLAIN Now, the INDEX Scan is used and therefore, the query is very fast, as expected. For me, as a user not being involved in all the intrinsics of PostgreSQL, the question was "Why is this SELECT so slow?" (this question is asked a lot of times in this Mail lists) Now, I would like to say thank you! You have explained me and hopefully many more users what is going on behind the scene. > Anyway, the reason the planner is picking a seqscan+sort is that it > thinks that will be faster than an indexscan. It's not necessarily > wrong. Have you compared the explain output and actual timings both > ways? (Use "set enable_seqscan to off" to force it to pick an indexscan > for testing purposes.) > > regards, tom lane -- Mit freundlichen Gruessen / With best regards Reiner Dassing ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Triggers do not fire
Hallo! I have written a very small test procedure to show a possible error on PostgreSQL V7.1.1. A trigger does not fire the function on update but on inserts. Does someone have made the same experiences? Here is the short example which is doing nothing important, just showing the situation: DROP TABLE test; CREATE TABLE test ( "sensor_id" int4 NOT NULL, "epoch" datetime NOT NULL, "value" float4 NOT NULL, PRIMARY KEY (sensor_id,epoch)); DROP FUNCTION f_test(); CREATE FUNCTION f_test() RETURNS OPAQUE AS ' BEGIN RAISE NOTICE ''Fired %'',TG_OP; RETURN NULL; END; ' LANGUAGE 'plpgsql'; DROP TRIGGER t_test; CREATE TRIGGER t_test BEFORE INSERT OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE f_test(); INSERT INTO test VALUES(1,'2000-10-11 12:00:00',-20.2); UPDATE test SET value = 1000.0 WHERE epoch = '2000-10-11 12:10:00' AND sensor_id = 1; The result is as follows: INSERT INTO test VALUES(1,'2000-10-11 12:00:00',-20.2); NOTICE: Fired INSERT INSERT 0 0 UPDATE test SET value = 1000.0 WHERE epoch = '2000-10-11 12:10:00' AND sensor_id = 1; UPDATE 0 The insert notice can be shown! The update notice is not there! Why? -- Mit freundlichen Gruessen / With best regards Reiner Dassing ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Triggers do not fire
Hello Tom! Thank you for your help! Your hints did solve the asked problem. The update trigger is fired if there is a row in the table test to be updated. But, behind my question there was another design (philosophie) which I am trying to solve by the means of different triggers. This is off topic, but ... Maybe, you or somebody on the list can give a hint how solve the following task: I have a table which has a lot of entries (some x millions) of the kind (id, timestamp, value) The access (selects) is concentrated to the timely last some thousands entries. To adapt this fact I want to setup a "virtual" table - test in my example - which is accessed by the clients but in reality the entries are separated to different small tables. These table are dynamically created to hold the values distinguished by years. By the use of triggers I can redirect inserts from the table test to small tables, called test_x_y with x = id, y=year. To update an entry the value in the main table must exist, therefore, this approach does not work. Rules do also not work as I must dynamically build table names. And the execution of pgsql functions is not possible in rules. Correct? Another possible approach would be to make selects which give back results to be used as table names. I.e., it would be necessary to have something like create table f(NEW.val) as select where f(...) gives back a name of a table. Tom Lane wrote: > > Reiner Dassing <[EMAIL PROTECTED]> writes: > > I have written a very small test procedure to show a possible error > > on PostgreSQL V7.1.1. > > The error is yours: you set up the trigger function to return NULL, > which means it's telling the system not to allow the INSERT or UPDATE. > > > INSERT INTO test VALUES(1,'2000-10-11 12:00:00',-20.2); > > NOTICE: Fired INSERT > > INSERT 0 0 > > Note the summary line saying that zero rows were inserted. > > > UPDATE test SET value = 1000.0 WHERE epoch = '2000-10-11 12:10:00' AND > > sensor_id = 1; > > UPDATE 0 > > Here, zero rows were updated, so of course there was nothing to fire > the trigger on. > > regards, tom lane -- Mit freundlichen Gruessen / With best regards Reiner Dassing ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Upper / lower cases on table and column names
(PostgreSQL) 7.1.1: Hello all! I was trying to adopt a database application to PostgreSQL. (It is written for MySQL and Oracle using perl) During this process I recognized the phenomena that upper case letters of table names and column names are not preserved in PostgreSQL. Is this a "featue" of PostgreSQL or do I miss something? Example: Id and textId as a column name should conserve upper case letters 'I' but \d data converts it to lower case words: psql ... create table data ( Id int not null, textId int not null); \d data Table "data" Attribute | Type | Modifier ---+-+-- id| integer | not null textid| integer | not null and create table Data ( Id int not null, textId int not null); results in: ERROR: Relation 'data' already exists In the interpretation of my application table 'data' and 'Data' is something different. -- -- Mit freundlichen Gruessen / With best regards Reiner Dassing ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to check postgres running or not ?
Hello!
Our check procedure on TRU64 UNIX is:
checkmasterdaemon
if [ $? -eq 1 ]; then
# Try to connect to postgres by selecting tables
TABELLEN=`/pg/postgresql-7.4.3/bin/psql -h postgres -c 'select datname from
pg_database' postgres postgres 2>/dev/null`
if [[ "X$TABELLEN" = "X" ]];then
echo "check failed for postmaster"
exit 1
else
# echo "postmaster is running"
fi
else
echo "check failed for postmaster"
exit 1
fi
where :
checkmasterdaemon () {
MASTER_PID=$(getmasterpid)
if [ "X$MASTER_PID" = "X" ]; then
#echo "Postmaster is not running"
ret=0
else
PS_OUT=`ps -o comm,pid -p "${MASTER_PID}"`
PID=`echo $PS_OUT | awk '/postgres/ {print $4}'`
#echo $PID
if [ "X$MASTER_PID" != "X$PID" ]
then
#echo "Postmaster (${MASTER_PID}) does not exist (any more)"
ret=0
else
ret=1
fi
fi
return $ret
}
getmasterpid () {
if [[ -r ${PIDFILE} ]]
then
MASTER_PID=`head -n 1 ${PIDFILE}`
echo $MASTER_PID
else
echo ""
fi
}
--
Mit freundlichen Gruessen / With best regards
Reiner Dassing
---(end of broadcast)---
TIP 8: explain analyze is your friend
