Received: from resy5.fzk.de (resy5.fzk.de [141.52.68.8]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9HFIvP47978 for ; Wed, 17 Oct 2001 11:18:58 -0400 (EDT) (envelope-from ch@rodos.fzk.de) Received: by rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id RAA03886 for ; Wed, 17 Oct 2001 17:20:36 +0200 (METDST) From: Haller Christoph Message-Id: <200110171517.RAA13051@rodos> Subject: Identifying obsolete values To: pgsql-sql@postgresql.org Date: Wed, 17 Oct 2001 17:17:44 METDST X-Mailer: Elm [revision: 212.4] X-Archive-Number: 200110/237 X-Sequence-Number: 5192 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