Re: [PERFORM] Excessive memory used for INSERT

[Torsten Zuehlsdorff]

Hello Alessandro,

2014-12-15 17:54:07 GMT DETAIL: Failed process was running: WITH upsert
AS (update MSG set
(slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE)
= (to_timestamp('201212032145',
'YYYYMMDDHH24MI'),2,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_020')
where slot=to_timestamp('201212032145', 'YYYYMMDDHH24MI') and MSG=2
RETURNING *) insert into MSG
(slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE)
select to_timestamp('201212032145',
'YYYYMMDDHH24MI'),2,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_020'
WHERE NOT EXISTS (SELECT * FROM upsert);

How many rows is "(SELECT * FROM upsert)" returning? Without knowing 
more i would guess, that the result-set is very big and that could be 
the reason for the memory usage.

I would add an WHERE clause to reduce the result-set (an correct index 
can fasten this method even more).

Greetings,
Torsten


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance