Re: Excessive memory used for INSERT

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(dot)Ipe(at)meteo(dot)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