From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: feikesteenbergen@gmail.com
Subject: BUG #15847: Running out of memory when planning full outer joins involving many partitions
Date: Wed, 12 Jun 2019 09:51:07 +0000
Message-ID: <15847-ea3734094bf8ae61@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 15847
Logged by: Feike Steenbergen
Email address: feikesteenbergen@gmail.com
PostgreSQL version: 11.3
Operating system: Ubuntu 18.04.2 LTS
Description:
Hi all,
We've had a few reports recently that had a backend consume a lot of
memory
causing either an OOM-kill or kubernetes rescheduling their PostgreSQL
pod.
The actual report and some graphs and details can be found here:
https://github.com/timescale/timescaledb/issues/1274
Note: The behavior is not TimescaleDB specific, it also happens on a
vanilla
PostgreSQL installation without Timescale installed.
For this specific bug report there were two things that clearly stood out:
- a FULL OUTER JOIN is done
- many partitions (thousands) are involved
The problematic behavior is as follows:
While planning a query, the backend uses a full CPU and it's memory keeps
increasing until either:
- ERROR: 53200: out of memory for systems with overcommit disabled
- or killed by OOM
- or rescheduled (kubernetes)
The backend seems to be in add_child_rel_equivalences during this time and
does
not respond to SIGINT while it is in there.
I've encountered this problem on 11.3, 10.8 and 9.6.13 (with table
inheritance
instead of declarative partitioning).
regards,
Feike Steenbergen
/*
The below SQL should reproduce the issue on a machine with <= 16GB memory:
This is a surrogate test case to trigger the problematic behavior.
The actual report of the user involved multiple tables, but to simplify
things here I'm just reusing the same partitioned table with a lot of
partitions
*/
CREATE TABLE buggy(
inserted timestamptz not null
)
PARTITION BY RANGE (inserted);
-- Create some partitions
DO $BODY$
DECLARE
partname text;
start date := date_trunc('week', '1999-12-31'::date);
BEGIN
FOR i IN 0..1000 LOOP
partname := format('buggy_%s', to_char(start, 'IYYYIW'));
EXECUTE format( $$CREATE TABLE %I PARTITION OF %I
FOR VALUES FROM (%L) TO (%L)$$,
partname,
'buggy',
start,
start + 7
);
start := start + 7;
END LOOP;
END;
$BODY$;
-- This works fine
EXPLAIN
SELECT
inserted
FROM
buggy b1
LEFT JOIN
buggy b2 USING (inserted)
LEFT JOIN
buggy b3 USING (inserted)
LEFT JOIN
buggy b4 USING (inserted
);
/*
This will either do the following:
- `ERROR: 53200: out of memory` for systems with overcommit disabled, or
- an out-of-memory kill (kernel)
- rescheduling of the pod (k8s)
*/
EXPLAIN
SELECT
inserted
FROM
buggy b1
FULL OUTER JOIN
buggy b2 USING (inserted)
FULL OUTER JOIN
buggy b3 USING (inserted)
FULL OUTER JOIN
buggy b4 USING (inserted)
;
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, feikesteenbergen@gmail.com
Subject: Re: BUG #15847: Running out of memory when planning full outer joins involving many partitions
In-Reply-To: <15847-ea3734094bf8ae61@postgresql.org>
* 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