Re: remaining sql/json patches

Hi Tomas,

On Wed, Mar 6, 2024 at 6:30 AM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
> Hi,
>
> I know very little about sql/json and all the json internals, but I
> decided to do some black box testing. I built a large JSONB table
> (single column, ~7GB of data after loading). And then I did a query
> transforming the data into tabular form using JSON_TABLE.
>
> The JSON_TABLE query looks like this:
>
> SELECT jt.* FROM
> title_jsonb t,
> json_table(t.info, '$'
> COLUMNS (
> "id" text path '$."id"',
> "type" text path '$."type"',
> "title" text path '$."title"',
> "original_title" text path '$."original_title"',
> "is_adult" text path '$."is_adult"',
> "start_year" text path '$."start_year"',
> "end_year" text path '$."end_year"',
> "minutes" text path '$."minutes"',
> "genres" text path '$."genres"',
> "aliases" text path '$."aliases"',
> "directors" text path '$."directors"',
> "writers" text path '$."writers"',
> "ratings" text path '$."ratings"',
> NESTED PATH '$."aliases"[*]'
> COLUMNS (
> "alias_title" text path '$."title"',
> "alias_region" text path '$."region"'
> ),
> NESTED PATH '$."directors"[*]'
> COLUMNS (
> "director_name" text path '$."name"',
> "director_birth_year" text path '$."birth_year"',
> "director_death_year" text path '$."death_year"'
> ),
> NESTED PATH '$."writers"[*]'
> COLUMNS (
> "writer_name" text path '$."name"',
> "writer_birth_year" text path '$."birth_year"',
> "writer_death_year" text path '$."death_year"'
> ),
> NESTED PATH '$."ratings"[*]'
> COLUMNS (
> "rating_average" text path '$."average"',
> "rating_votes" text path '$."votes"'
> )
> )
> ) as jt;
>
> again, not particularly complex. But if I run this, it consumes multiple
> gigabytes of memory, before it gets killed by OOM killer. This happens
> even when ran using
>
> COPY (...) TO '/dev/null'
>
> so there's nothing sent to the client. I did catch memory context info,
> where it looks like this (complete stats attached):
>
> ------
> TopMemoryContext: 97696 total in 5 blocks; 13056 free (11 chunks);
> 84640 used
> ...
> TopPortalContext: 8192 total in 1 blocks; 7680 free (0 chunks); ...
> PortalContext: 1024 total in 1 blocks; 560 free (0 chunks); ...
> ExecutorState: 2541764672 total in 314 blocks; 6528176 free
> (1208 chunks); 2535236496 used
> printtup: 8192 total in 1 blocks; 7952 free (0 chunks); ...
> ...
> ...
> Grand total: 2544132336 bytes in 528 blocks; 7484504 free
> (1340 chunks); 2536647832 used
> ------
>
> I'd say 2.5GB in ExecutorState seems a bit excessive ... Seems there's
> some memory management issue? My guess is we're not releasing memory
> allocated while parsing the JSON or building JSON output.
>
> I'm not attaching the data, but I can provide that if needed - it's
> about 600MB compressed. The structure is not particularly complex, it's
> movie info from [1] combined into a JSON document (one per movie).

Thanks for the report.

Yeah, I'd like to see the data to try to drill down into what's piling
up in ExecutorState. I want to be sure of if the 1st, query functions
patch, is not implicated in this, because I'd like to get that one out
of the way sooner than later.

--
Thanks, Amit Langote