Обсуждение: postgresql 12 runs out of memory when updating a partitioned table with subquery

Поиск
Список
Период
Сортировка

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

От
Max Vikharev
Дата:
Hi,

I have 'out of mem' on postgresql 12 when updating a partitioned table with subquery.
I have Project and Content relations. Content is partitioned by type and subpartitioned by project.

When I update content of some type of a single project with subquery - postgresql query hangs and runs out of mem. 

Test env has the following settings:
1. 4G RAM, 2 CPU.
2. Ubuntu 16.04
3. Default postgresql settings (details in link https://gist.github.com/mvikharev/f770914a8bb7f814c5aab4fd1da109f7#file-configuration)
4. 100 rows in 100 subpartitions for 2 partitions.

Scripts to reproduce the problem

Reproduce
1. Create schema 
createdb test
./schema.sh > schema.sql
./data.sh > data.sql
psql test < schema.sql
psql test < data.sql

2. Update with prepared ids - no problem
SELECT id into agg
FROM "content"
WHERE "content"."id" IN
    (SELECT U0."id" AS Col1
     FROM "content" U0
     INNER JOIN "project" U2 ON (U0."project_id" = U2."id")
     WHERE (U0."service_id" = 2
            AND U0."type_id" = 0
            AND U2."user_id" = 1));
SELECT 1000

UPDATE "content"
SET "value" = '1'
WHERE "content"."id" IN (select id from agg);
UPDATE 1000

3. Update with subquery - out of mem
UPDATE "content"
SET "value" = '1'
WHERE "content"."id" IN
    (SELECT U0."id" AS Col1
     FROM "content" U0
     INNER JOIN "project" U2 ON (U0."project_id" = U2."id")
     WHERE (U0."service_id" = 2
            AND U0."type_id" = 0
            AND U2."user_id" = 1));

^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
server closed the connection unexpectedly

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

От
David Rowley
Дата:
On Sun, 12 Jul 2020 at 09:03, Max Vikharev <bm.kinder@gmail.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

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

От
Max Vikharev
Дата:
> 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@gmail.com>:
On Sun, 12 Jul 2020 at 09:03, Max Vikharev <bm.kinder@gmail.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

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

От
David Rowley
Дата:
On Mon, 13 Jul 2020 at 00:05, Max Vikharev <bm.kinder@gmail.com> wrote:
> 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.

FWIW, this is part of the planner that there is work-in-progress to
improve. However, that's not going to appear until at least PG14.

The reason that PG12's planner is so slow and uses so much memory here
is that it's planning that subquery once for each partition.

David