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