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@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=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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
