Alessandro Ipe <Alessandro.Ipe@xxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
