Re: Excessive memory usage in multi-statement queries w/ partitioning

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Andreas Seltenreich <andreas(dot)seltenreich(at)credativ(dot)de>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Excessive memory usage in multi-statement queries w/ partitioning
Date: 2019-05-24 14:28:23
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Joe Conway <mail(at)joeconway(dot)com> writes:
> On 5/24/19 9:33 AM, David Rowley wrote:
>> For it to have regressed it would have had to once have been better,
>> but where was that mentioned? The only thing I saw was
>> non-partitioned tables compared to partitioned tables, but you can't
>> really say it's a regression if you're comparing apples to oranges.

> I have very successfully used multiple hundreds and even low thousands
> of partitions without running out of memory under the older inheritance
> based "partitioning", and declarative partitioning is supposed to be
> (and we have advertised it to be) better, not worse, isn't it?

Have you done the exact thing described in the test case? I think
that's going to be quite unpleasantly memory-intensive in any version.

The real issue here is that we have designed around the assumption
that MessageContext will be used to parse and plan one single statement
before being reset. The described usage breaks that assumption.
No matter how memory-efficient any one statement is or isn't,
if you throw enough of them at the backend without giving it a chance
to reset MessageContext, it won't end well.

So my thought, if we want to do something about this, is not "find
some things we can pfree at the end of planning" but "find a way
to use a separate context for each statement in the query string".
Maybe multi-query strings could be handled by setting up a child
context of MessageContext (after we've done the raw parsing there
and determined that indeed there are multiple queries), running
parse analysis and planning in that context, and resetting that
context after each query.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2019-05-24 14:33:43 Re: BUG #15819: wrong expression in document of pgbench
Previous Message Heikki Linnakangas 2019-05-24 14:26:01 Re: initdb recommendations