Re: Excessive memory used for INSERT - Mailing list pgsql-performance

From Alessandro Ipe
Subject Re: Excessive memory used for INSERT
Date
Msg-id 2913727.pH4N6BDu2g@snow.oma.be
Whole thread Raw
In response to Re: Excessive memory used for INSERT  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
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



pgsql-performance by date:

Previous
From: cesar
Date:
Subject: Re: trying to run pgbench-tools postgresql ubuntu ERROR: relation "branches" does not exist
Next
From: Steve Crawford
Date:
Subject: New server optimization advice