Friday, January 6, 2012, 4:21:06 PM you wrote:
Every 5 minutes, a process have to insert a few thousand of rows in this
table, but sometime, the process have to insert an already existing row
(based on values in the triplet (t_value, t_record, output_id). In this
case, the row must be updated
It's a fairly tricky problem. I have a number of sensors producing
energy data about every 5 minutes, but at random times between 1 and
15 minutes. I can't change that as that's the way the hardware of the
sensors works. These feed into another unit, which accumulates them
and forwards them in
Yes, but it should become a bit slower if you fix your code :-)
where t_imp.id is null and test.id=t_imp.id;
=
where t_imp.id is not null and test.id=t_imp.id;
and a partial index on matching rows might help (should be tested):
(after the first updat)
create index t_imp_ix on
If solution with temp table is acceptable - i think steps could be
reduced...
• copy to temp_imp ( temp table does not have id column)
• update live set count = temp_imp.count from temp_imp using (
col1,col2,col3)
• insert into live from temp where col1, col2 and col3 not exists in
live
Kind
Saturday, January 7, 2012, 3:02:10 PM you wrote:
• insert into live from temp where col1, col2 and col3 not exists in
live
'not exists' is something I'm trying to avoid, even if the optimizer is
able to handle it.
--
Jochen Erwied | home: joc...@erwied.eu +49-208-38800-18, FAX:
It's a fairly tricky problem. I have a number of sensors producing
energy data about every 5 minutes, but at random times between 1 and
15 minutes. I can't change that as that's the way the hardware of the
sensors works. These feed into another unit, which accumulates them
and forwards them in
On Fri, Jan 6, 2012 at 6:35 AM, anto...@inaps.org wrote:
Hello,
I've a table with approximately 50 million rows with a schema like this:
id bigint NOT NULL DEFAULT nextval('stats_5mn'::regclass),
t_value integer NOT NULL DEFAULT 0,
t_record integer NOT NULL DEFAULT 0,
It was not query... Just sentence where some index values in one table
not exist in another...
So query could be with:
• WHERE (col1,col2,col2) NOT IN
• WHERE NOT EXISTS
• LEFT JOIN live USING (col1,col2,col2) WHERE live.id IS NULL
what ever whoever prefer more or what gives better results...
Hi Pierre!
On 7 January 2012 12:20, Pierre C li...@peufeu.com wrote:
I'm stuck home with flu, so I'm happy to help ;)
[...]
I'll build an example setup to make it clearer...
[...]
That's almost identical to my tables. :-)
Note that the distance field represents the distance (in time) between
