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