pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Identifying obsolete values
2+ messages / 2 participants
[nested] [flat]

* Identifying obsolete values
@ 1970-01-01 00:00 Haller Christoph <ch@rodos.fzk.de>
  2001-10-22 15:19 ` Re: Identifying obsolete values Masaru Sugawara <rk73@echna.ne.jp>
  0 siblings, 1 reply; 2+ messages in thread

From: Haller Christoph @ 1970-01-01 00:00 UTC (permalink / raw)
  To: pgsql-sql

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 



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Identifying obsolete values
  1970-01-01 00:00 Identifying obsolete values Haller Christoph <ch@rodos.fzk.de>
@ 2001-10-22 15:19 ` Masaru Sugawara <rk73@echna.ne.jp>
  0 siblings, 0 replies; 2+ messages in thread

From: Masaru Sugawara @ 2001-10-22 15:19 UTC (permalink / raw)
  To: pgsql-sql

On Wed, 17 Oct 2001 17:17:44 METDST
Haller Christoph wrote:

> 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 
> ) ; 


Hi,

It seems that a series of the operations can be unified.


SELECT o.sid,o.timepoint,o.lid,o.mid,o.value
   FROM advncd_onfvalue as o
   WHERE EXISTS 
        (SELECT t.timepoint, t.mid, t.lid, t.sid
            FROM advncd_onfvalue as t
            GROUP BY t.timepoint, t.mid, t.lid, t.sid
            HAVING o.timepoint    = t.timepoint    AND
                   o.mid          = t.mid          AND
                   o.lid          = t.lid          AND
                   o.sid          = t.sid          AND
                   o.entrancetime = MAX(t.entrancetime)
        )
;


By the way, a mail server have been downed ?


Regards,
Masaru Sugawara




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-10-22 15:19 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 Identifying obsolete values Haller Christoph <ch@rodos.fzk.de>
2001-10-22 15:19 ` Masaru Sugawara <rk73@echna.ne.jp>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox