Re: Excessive memory used for INSERT

От: Alessandro Ipe
Тема: Re: Excessive memory used for INSERT
Дата: ,
Msg-id: 35486133.28l5a59Zd7@snow.oma.be
(см: обсуждение, исходный текст)
Ответ на: Re: Excessive memory used for INSERT  (Torsten Zuehlsdorff)
Список: pgsql-performance

Скрыть дерево обсуждения

Excessive memory used for INSERT  (Alessandro Ipe, )
 Re: Excessive memory used for INSERT  (Torsten Zuehlsdorff, )
  Re: Excessive memory used for INSERT  (Tom Lane, )
   Re: Excessive memory used for INSERT  (Alessandro Ipe, )
    Re: Excessive memory used for INSERT  (Tom Lane, )
     Re: Excessive memory used for INSERT  (Alessandro Ipe, )
      Re: Excessive memory used for INSERT  (Alessandro Ipe, )
       Re: Excessive memory used for INSERT  (Tom Lane, )
        Re: Excessive memory used for INSERT  (Alessandro Ipe, )
        Re: Excessive memory used for INSERT  (Alessandro Ipe, )
         Re: Excessive memory used for INSERT  (Tom Lane, )
          Re: Excessive memory used for INSERT  (Alessandro Ipe, )
  Re: Excessive memory used for INSERT  (Alessandro Ipe, )
 Re: Excessive memory used for INSERT  (Torsten Förtsch, )
  Re: Excessive memory used for INSERT  (Alessandro Ipe, )

Hi Torsten,


Thanks for your answer.

I have modified
(SELECT * FROM upsert)
to
(SELECT * FROM upsert WHERE slot=to_timestamp('201212032145', 'YYYYMMDDHH24MI') and MSG=2)
according to your suggestion to reduce the result-set to a single row. However, the INSERT process is still consuming
thesame amount of RAM. 


Regards,


Alessandro.


On Wednesday 17 December 2014 16:26:32 Torsten Zuehlsdorff wrote:
> 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_1
> > 08,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_1
> > 08,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




В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: Excessive memory used for INSERT
От: Torsten Förtsch
Дата:
Сообщение: Re: Excessive memory used for INSERT