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,/*
measurementid */ lid INTEGER NOT NULL,/* location id */ sid INTEGER NOT NULL,/*
sourceid */ 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
