Re: Excessive memory used for INSERT

[Torsten Zuehlsdorff <mailinglists@xxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance