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