Collected from the PG bugs email list.
|OS||Ubuntu 18.04.2 LTS|
|Reported by||Feike Steenbergen|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 15847 Logged by: Feike Steenbergen Email address: (redacted) 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) ;
|2019-06-12 09:51:07+00||PG Bug reporting form||BUG #15847: Running out of memory when planning full outer joins involving many partitions|
|2019-06-13 17:14:16+00||Tom Lane||Re: BUG #15847: Running out of memory when planning full outer joins involving many partitions|
|2019-06-14 08:26:35+00||Feike Steenbergen||Re: BUG #15847: Running out of memory when planning full outer joins involving many partitions|