pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery planning on partitioned table causes postgres 13.4 to consume all memory
3+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2021-09-19 2a9a34c310 Doc: further tweaking of v14 release notes.
* Query planning on partitioned table causes postgres 13.4 to consume all memory
@ 2021-09-17 09:49 Duncan Sands <duncan.sands@deepbluecap.com>
0 siblings, 1 reply; 3+ messages in thread
From: Duncan Sands @ 2021-09-17 09:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Postgresql version: 13.4
O/S version: Ubuntu 21.04
How to reproduce:
(1) Create "data" schema and its tables by executing the commands from the
attached file d.sql. Each of the two created tables has less than 20 rows.
(2) Execute or EXPLAIN this query
DELETE FROM data.files
WHERE path=ANY(
SELECT path
FROM (
SELECT meta, path,
first_value(path)
OVER (PARTITION BY meta ORDER BY priority) AS first
FROM data.files
) f,
data.metadata m
WHERE f.path <> f.first
AND f.meta = m.id
AND m.syd = 667
);
(3) Observe that the EXPLAIN/query never completes, and memory usage goes up
and up.
For example:
duncan=> \i /tmp/d.sql
...
duncan=> DELETE FROM data.files
WHERE path=ANY(
SELECT path
FROM (
SELECT meta, path,
first_value(path)
OVER (PARTITION BY meta ORDER BY priority) AS first
FROM data.files
) f,
data.metadata m
WHERE f.path <> f.first
AND f.meta = m.id
AND m.syd = 667
);
... wailing and gnashing of teeth ...
Attachments:
d.sql (application/sql, 1008.3K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query planning on partitioned table causes postgres 13.4 to consume all memory
@ 2021-09-19 16:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Duncan Sands <duncan.sands@deepbluecap.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2021-09-19 16:03 UTC (permalink / raw)
To: Duncan Sands <duncan.sands@deepbluecap.com>; +Cc: pgsql-bugs@lists.postgresql.org
Duncan Sands <duncan.sands@deepbluecap.com> writes:
> [ planning DELETE on a thousand-partition table takes forever ]
FWIW, this situation has been very much improved for v14 [1].
In older branches, the best advice I can give you is "don't use
so many partitions". Especially not with hash partitioning,
where the query WHERE clause generally won't translate to any
useful pruning of the partitions.
(Personally, I think that hash partitioning is an evil that
we shouldn't have implemented at all. Or at least there
should be stronger warnings about it in the manual than there
are now.)
regards, tom lane
[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=86dc90056
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query planning on partitioned table causes postgres 13.4 to consume all memory
@ 2021-09-20 12:10 Duncan Sands <duncan.sands@deepbluecap.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Duncan Sands @ 2021-09-20 12:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Tom,
On 19/09/2021 18:03, Tom Lane wrote:
> Duncan Sands <duncan.sands@deepbluecap.com> writes:
>> [ planning DELETE on a thousand-partition table takes forever ]
>
> FWIW, this situation has been very much improved for v14 [1].
thanks, part (2) of that commit indeed looks like it should solve it.
Best wishes, Duncan.
> In older branches, the best advice I can give you is "don't use
> so many partitions". Especially not with hash partitioning,
> where the query WHERE clause generally won't translate to any
> useful pruning of the partitions.
>
> (Personally, I think that hash partitioning is an evil that
> we shouldn't have implemented at all. Or at least there
> should be stronger warnings about it in the manual than there
> are now.)
>
> regards, tom lane
>
> [1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=86dc90056
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-09-20 12:10 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-17 09:49 Query planning on partitioned table causes postgres 13.4 to consume all memory Duncan Sands <duncan.sands@deepbluecap.com>
2021-09-19 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-09-20 12:10 ` Duncan Sands <duncan.sands@deepbluecap.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox