From: Duncan Sands <duncan.sands@deepbluecap.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Query planning on partitioned table causes postgres 13.4 to consume all memory
Date: Fri, 17 Sep 2021 11:49:06 +0200
Message-ID: <4a3115d4-0fb2-e214-93e3-9a9d0974b883@deepbluecap.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: duncan.sands@deepbluecap.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Query planning on partitioned table causes postgres 13.4 to consume all memory
In-Reply-To: <4a3115d4-0fb2-e214-93e3-9a9d0974b883@deepbluecap.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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