Re: Excessive memory used for INSERT

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=5e0e9e1bb06dce1d12c95662a9ee1c03

> 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