From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alessandro Ipe <Alessandro.Ipe@meteo.be>
Cc: pgsql-performance@postgresql.org, Torsten Zuehlsdorff <mailinglists@toco-domains.de>
Subject: Re: Excessive memory used for INSERT
Date: Tue, 23 Dec 2014 15:27:41 -0500
Message-ID: <5141.1419366461@sss.pgh.pa.us> (raw)
In-Reply-To: <6006964.iXUoRzG9pV@snow.oma.be>
References: <2163931.4eVnXNjHa3@snow.oma.be>
<6053205.R7VxckQEVY@snow.oma.be>
<17790.1418922345@sss.pgh.pa.us>
<6006964.iXUoRzG9pV@snow.oma.be>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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=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@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: tgl@sss.pgh.pa.us, Alessandro.Ipe@meteo.be, mailinglists@toco-domains.de
Subject: Re: Excessive memory used for INSERT
In-Reply-To: <5141.1419366461@sss.pgh.pa.us>
* 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