First of all, thanks to Philip Hallstrom for the quick reply.
Consider the following tables
CREATE TABLE advncd_onfvalue
(timepoint DATETIME NOT NULL,
mid INTEGER NOT NULL,/* measurement id */
lid INTEGER NOT NULL,/* location id */
sid INTEGER NOT NULL,/* source id */
entrancetime DATETIME NOT NULL DEFAULT NOW(),
value FLOAT NOT NULL /* float value, not unique */
) ;
CREATE TABLE advncd_tempreftime
(timepoint DATETIME NOT NULL,
mid INTEGER NOT NULL,/* measurement id */
lid INTEGER NOT NULL,/* location id */
sid INTEGER NOT NULL,/* source id */
entrancetime DATETIME NOT NULL
) ;
I use the second table to identify the actual resp. obsolete ones within the first
table.
DELETE FROM advncd_tempreftime;
INSERT INTO advncd_tempreftime
SELECT timepoint,mid,lid,sid,MAX(entrancetime) FROM advncd_onfvalue
GROUP BY timepoint,mid,lid,sid ;
SELECT o.sid,o.timepoint,o.lid,o.mid,o.value FROM advncd_onfvalue o
WHERE EXISTS
(SELECT * FROM advncd_tempreftime t WHERE
o.timepoint = t.timepoint AND
o.mid = t.mid AND
o.lid = t.lid AND
o.sid = t.sid AND
o.entrancetime = t.entrancetime
) ;
SELECT o.sid,o.timepoint,o.lid,o.mid,o.value FROM advncd_onfvalue o
WHERE NOT EXISTS
(SELECT * FROM advncd_tempreftime t WHERE
o.timepoint = t.timepoint AND
o.mid = t.mid AND
o.lid = t.lid AND
o.sid = t.sid AND
o.entrancetime = t.entrancetime
) ;
It works fine, but it's a pain how long it takes.
I tried to improve the speed by
CREATE /* NOT UNIQUE */ INDEX advncd_onfvalue_idx_tmlse ON advncd_onfvalue
(timepoint, mid, lid, sid, entrancetime) ;
CREATE /* NOT UNIQUE */ INDEX advncd_tempreftime_idx_tmlse ON advncd_tempreftime
(timepoint, mid, lid, sid, entrancetime) ;
vacuum advncd_onfvalue \g
vacuum advncd_tempreftime \g
Some effect, but still too slow.
Does anybody know alternatives?
What about
SELECT DISTINCT ON (sid,timepoint,lid,mid)
sid,timepoint,lid,mid,value FROM advncd_onfvalue
ORDER BY sid,timepoint,lid,mid,entrancetime DESC ;
My bad luck is, I cannot test DISTINCT ON (multiple columns) at the moment,
because my system admin did not yet install the up-to-date postgres version.
Regards, Christoph
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
