Chris Mair <chris@1006.org> writes:
> ...
> Interestingly, if you combine these, it quickly blows up! The following query with a limit 1000 already
> has a RES of well over 1GB. With larger limits it quickly thrashes my machine.
> enrico=# explain analyze
> SELECT substring((field_id ->'comment')::text,1,1),
> jsonb_array_elements(field_id ->'comment')->>'id'
> FROM stage.fbcomment limit 1000;
Yeah. The key point here is that jsonb_array_elements() returns a set,
that is one tuple per array element. We've semi-deprecated use of
set-returning functions in SELECT output lists, and one of the reasons
is that memory management in the presence of set-returning functions
is a mess: we can't clear the SELECT's expression-evaluation context
until the SRF is done returning rows. Some of the rows in this dataset
contain 'comment' arrays with over 1000 elements; that means that the
"substring()" expression gets executed over 1000 times without any
opportunity to reclaim memory. And that expression will leak a pretty
fair amount of memory when dealing with a large field_id value.
Multiply that by the fact that the original query does this half a
dozen times, and you have a fairly large peak memory consumption.
The space does get reclaimed after each original table row, but that's
no comfort if you ran out before that.
It would probably be possible to go through the JSONB code and reduce
(though not eliminate) its appetite for memory in this sort of situation
--- for instance, freeing detoasted input arguments would help a lot.
But that's not likely to happen overnight. In any case, you're right
that this is a very inefficiently expressed query; refactoring things
so that the jsonb_array_elements() call is done just once in a subquery
would probably help a great deal.
regards, tom lane
