json(b)_array_elements use causes very large memory usage when also referencing entire json document

From: Lucas Fairchild-Madar <lucas(dot)madar(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: json(b)_array_elements use causes very large memory usage when also referencing entire json document
Date: 2017-10-06 18:26:35
Message-ID: CAJmoq7OJ=ntoLRE0R8h+_Dt8KoNifDhQQua_w2y_d1e7T6nL-w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

This might be related to or a duplicate of: BUG #14843: CREATE TABLE churns
through all memory

PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
Ubuntu 16.04.3 LTS

Given the following json structure in a jsonb column called "data":
{"id":"foo","items":[{"id":1},{"id":2}, ... {"id":100000}]}

SELECT jsonb_array_elements(data->'items') from table;
This query returns very quickly and behaves as expected.
Planning time: 0.055 ms
Execution time: 4.746 ms

SELECT data->>'id', jsonb_array_elements(data->'items') from table;
This query either takes over 8gb of memory, causing the OOM killer to
terminate the database.

Reducing the number of items in the array to 10,000, we get:
Planning time: 0.048 ms
Execution time: 3706.880 ms

Here's the output of pg_stat_statements, if this is helpful.

query | SELECT jsonb_array_elements(data->?) from kaboom;
rows | 10000
shared_blks_hit | 9

query | SELECT data->>? as id, jsonb_array_elements(data->?)
from kaboom;
rows | 10000
shared_blks_hit | 80017

This also works with json_ variant functions and also happens in postgresql
9.5.

Please let me know if I can provide any additional information.

Thanks,
Lucas

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2017-10-06 18:27:15 Re: BUG #14843: CREATE TABLE churns through all memory, crashes db
Previous Message Tom Lane 2017-10-06 18:18:57 Re: BUG #14843: CREATE TABLE churns through all memory, crashes db