From: Alessandro Ipe <Alessandro.Ipe@meteo.be>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org,
 Torsten Zuehlsdorff <mailinglists@toco-domains.de>
Subject: Re: Excessive memory used for INSERT
Date: Tue, 06 Jan 2015 18:40:05 +0100
Message-ID: <2913727.pH4N6BDu2g@snow.oma.be>
Organization: Royal Meteorological Institute of Belgium
User-Agent: KMail/4.14.3 (Linux/3.7.10-1.1-desktop; KDE/4.14.3; x86_64; ; )
In-Reply-To: <5141.1419366461@sss.pgh.pa.us>
References: <2163931.4eVnXNjHa3@snow.oma.be> <6006964.iXUoRzG9pV@snow.oma.be>
 <5141.1419366461@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Transfer-Encoding: 7Bit
Content-Type: text/plain; charset="iso-8859-15"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,


Doing the UPDATE on the child table (provided that the table does exist) as 
you recommended solved all my memory consumption issue.


Thanks a lot,


Alessandro.


On Tuesday 23 December 2014 15:27:41 Tom Lane wrote:
> Alessandro Ipe <Alessandro.Ipe@meteo.be> writes:
> > I guess the memory consumption is depending on the size of my database, so
> > only giving a reduced version of it won't allow to hit the issue.
> > 
> > The pg_dumpall file of my database can be found at the address
> > https://gerb.oma.be/owncloud/public.php?service=files&t=5e0e9e1bb06dce1d12
> > c95662a9ee1c03
> > 
> > The queries causing the issue are given in files
> > -  tmp.OqOavPYbHa (with the new upsert_func function)
> > -  tmp.f60wlgEDWB (with WITH .. AS statement)
> 
> Well, the core of the problem here is that you've chosen to partition the
> MSG table at an unreasonably small grain: it's got 3711 child tables and
> it looks like you plan to add another one every day.  For forty-some
> megabytes worth of data, I'd have said you shouldn't be partitioning at
> all; for sure you shouldn't be partitioning like this.  PG's inheritance
> mechanisms are only meant to cope with order-of-a-hundred child tables at
> most.  Moreover, the only good reason to partition is if you want to do
> bulk data management by, say, dropping the oldest partition every so
> often.  It doesn't look like you're planning to do that at all, and I'm
> sure if you do, you don't need 1-day granularity of the drop.
> 
> I'd recommend you either dispense with partitioning entirely (which would
> simplify your life a great deal, since you'd not need all this hacky
> partition management code), or scale it back to something like one
> partition per year.
> 
> Having said that, it looks like the reason for the memory bloat is O(N^2)
> space consumption in inheritance_planner() while trying to plan the
> "UPDATE msg SET" commands.  We got rid of a leading term in that
> function's space consumption for many children awhile ago, but it looks
> like you've found the next largest term :-(.  I might be able to do
> something about that.  In the meantime, if you want to stick with this
> partitioning design, couldn't you improve that code so the UPDATE is
> only applied to the one child table it's needed for?
> 
> 			regards, tom lane


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