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

On 2019/05/27 21:56, Tom Lane wrote:
> Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> writes:
>> On 2019/05/24 23:28, Tom Lane wrote:
>>> 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 like the attached? I'm not sure if we need to likewise be concerned
>> about exec_sql_string() being handed multi-query strings.
>
> Please add this to the upcoming CF so we don't forget about it.

Done; added to Performance for lack of a better topic for this.

https://commitfest.postgresql.org/23/2131/

> (I don't think there's anything very new about this behavior, so
> I don't feel that we should consider it an open item for v12 ---
> anyone think differently?)

Agree that there's nothing new about the behavior itself. What may be new
though is people getting increasingly bitten by it if they query tables
containing large numbers of partitions most of which need to be scanned
[1]. That is, provided they have use cases where a single client request
contains hundreds of such queries to begin with.

Thanks,
Amit

[1] AFAICT, that's the only class of queries where planner needs to keep a
lot of stuff around, the memory cost of which increases with the number of
partitions. I was thinking that the planning complex queries involving
going through tons of indexes, joins, etc. also hoards tons of memory, but
apparently not, because the planner seems fairly good at cleaning after
itself as it's doing the work.