From: Alessandro Ipe <Alessandro.Ipe@meteo.be>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org, Torsten Zuehlsdorff <mailinglists@toco-domains.de>
Subject: Re: Excessive memory used for INSERT
Date: Tue, 06 Jan 2015 18:40:05 +0100
Message-ID: <2913727.pH4N6BDu2g@snow.oma.be> (raw)
In-Reply-To: <5141.1419366461@sss.pgh.pa.us>
References: <2163931.4eVnXNjHa3@snow.oma.be>
<6006964.iXUoRzG9pV@snow.oma.be>
<5141.1419366461@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Alessandro.Ipe@meteo.be, tgl@sss.pgh.pa.us, mailinglists@toco-domains.de
Subject: Re: Excessive memory used for INSERT
In-Reply-To: <2913727.pH4N6BDu2g@snow.oma.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox