Re: postgresql 12 runs out of memory when updating a partitioned table with subquery

On Sun, 12 Jul 2020 at 09:03, Max Vikharev <bm(dot)kinder(at)gmail(dot)com> wrote:
> I have 'out of mem' on postgresql 12 when updating a partitioned table with subquery.

It looks like you have about 400 partitions. I imagine you're getting
the OOM from the query planner. You can verify this by confirming that
you still get OOM by running EXPLAIN <update command here>; (don't
use EXPLAIN ANALYZE).

If you still get the OOM, then keep reading:

https://www.postgresql.org/docs/12/ddl-partitioning.html mentions:

"The query planner is generally able to handle partition hierarchies
with up to a few thousand partitions fairly well, provided that
typical queries allow the query planner to prune all but a small
number of partitions. Planning times become longer and memory
consumption becomes higher when more partitions remain after the
planner performs partition pruning. This is particularly true for the
UPDATE and DELETE commands."

You could try making the query a bit more simple. I think the
following is the same, but please check.

UPDATE "content"
SET "value" = '1'
WHERE "content"."type_id" = 0
AND "content"."service_id" = 2
AND EXISTS(SELECT 1 FROM "project" U2 WHERE "content"."project_id" =
U2."id" AND U2."user_id" = 1);

This has 1 less join which should reduce the amount of memory required
by the query planner a bit. If that works, then the planner is likely
still using quite a bit of memory. There's a chance you'd still get an
OOM if you got a few of these running at once. You'd still be living
dangerously until you add more memory or reduce the number of
partitions.

David