Re: remaining sql/json patches

On 3/7/24 08:26, Amit Langote wrote:
> On Wed, Mar 6, 2024 at 1:07 PM Amit Langote <amitlangote09@gmail.com> wrote:
>> Hi Tomas,
>>
>> On Wed, Mar 6, 2024 at 6:30 AM Tomas Vondra
>> <tomas.vondra@enterprisedb.com> wrote:
>>> 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.
> 
> I tracked this memory-hogging down to a bug in the query functions
> patch (0001) after all.  The problem was with a query-lifetime cache
> variable that was never set to point to the allocated memory.  So a
> struct was allocated and then not freed for every row where it should
> have only been allocated once.
> 

Thanks! I can confirm the query works with the new patches.

Exporting the 7GB table takes ~250 seconds (the result is ~10.6GB). That
seems maybe a bit much, but I'm not sure it's the fault of this patch.
Attached is a flamegraph for the export, and clearly most of the time is
spent in jsonpath. I wonder if there's a way to improve this, but I
don't think it's up to this patch.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company