postgresql 12 runs out of memory when updating a partitioned table with subquery
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.
Schema
https://gist.github.com/mvikharev/f770914a8bb7f814c5aab4fd1da109f7#file-schema-sh
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
https://gist.github.com/mvikharev/f770914a8bb7f814c5aab4fd1da109f7
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
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
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>:
Show quoted text
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
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