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
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 b
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 t_imp(t_va
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 Re
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 b
On Fri, Jan 6, 2012 at 6:35 AM, 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,
> output_id integer
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... But
Hi Pierre!
On 7 January 2012 12:20, Pierre C 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 the
> inter
