Re: postgresql 12 runs out of memory 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).

Yes, running EXPLAIN without ANALYZE also hangs. So the planner goes out of
mem.

> 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 query does not hang.

It is very sad that postgresql can be killed with such a small amount of
partitions.
Looks like partitioning by project is not an option for my case (query is
generated by Django ORM, there are many others).
I'll use indexing.

Thanks for the explanation.

вс, 12 июл. 2020 г. в 08:50, David Rowley <dgrowleyml(at)gmail(dot)com>:

> 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
>