From: Torsten Zuehlsdorff <mailinglists@toco-domains.de>
To: pgsql-performance@postgresql.org
Cc: Alessandro Ipe <Alessandro.Ipe@meteo.be>
Subject: Re: Excessive memory used for INSERT
Date: Wed, 17 Dec 2014 16:26:32 +0100
Message-ID: <5491A0A8.7010506@toco-domains.de> (raw)
In-Reply-To: <2163931.4eVnXNjHa3@snow.oma.be>
References: <2163931.4eVnXNjHa3@snow.oma.be>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mailinglists@toco-domains.de, Alessandro.Ipe@meteo.be
Subject: Re: Excessive memory used for INSERT
In-Reply-To: <5491A0A8.7010506@toco-domains.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox