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

Hi,

On 2019/05/23 4:15, Andreas Seltenreich wrote:
> …but when doing it on the parent relation, even 100 statements are
> enough to exceed the limit:
>
> ,----
> | $ psql -c "$(yes update t set c=c where c=6 \; | head -n 100)"
> | FEHLER: Speicher aufgebraucht
> | DETAIL: Failed on request of size 200 in memory context "MessageContext".
> `----
>
> The memory context dump shows plausible values except for the MessageContext:
>
> TopMemoryContext: 124336 total in 8 blocks; 18456 free (11 chunks); 105880 used
> [...]
> MessageContext: 264241152 total in 42 blocks; 264 free (0 chunks); 264240888 used
> [...]

As David Rowley said, planning that query hundreds of times under a single
MessageContext is not something that will end well on 11.3, because even a
single instance takes up tons of memory that's only released when
MessageContext is reset.

> Maybe some tactically placed pfrees or avoiding putting redundant stuff
> into MessageContext can relax the situation?

I too have had similar thoughts on the matter. If the planner had built
all its subsidiary data structures in its own private context (or tree of
contexts) which is reset once a plan for a given query is built and passed
on, then there wouldn't be an issue of all of that subsidiary memory
leaking into MessageContext. However, the problem may really be that
we're subjecting the planner to use cases that it wasn't perhaps designed
to perform equally well under -- running it many times while handling the
same message. It is worsened by the fact that the query in question is
something that ought to have been documented as not well supported by the
planner; David has posted a documentation patch for that [1]. PG 12 has
alleviated the situation to a large degree, so you won't see the OOM
occurring for this query, but not for all queries unfortunately.

With that said, we may want to look into the planner sometimes hoarding
memory, especially when planning complex queries involving partitions.
AFAIK, one of the reasons for partition-wise join, aggregate to be turned
off by default is that its planning consumes a lot of CPU and memory,
partly because of the fact that planner doesn't actively release the
memory of its subsidiary structures, or maybe because of inferior ways in
which partitions and partitioning properties are represented in the
planner. Though if there's evidence that it's the latter, maybe we should
fix that before pondering any sophisticated planner memory management.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/CAKJS1f-2rx%2BE9mG3xrCVHupefMjAp1%2BtpczQa9SEOZWyU7fjEA%40mail.gmail.com