Re: jsonb subscripting assignment performance

st 14. 4. 2021 v 7:39 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

> Hi,
>
> commit 676887a3 added support for jsonb subscripting.
>
> Many thanks for working on this. I really like the improved syntax.
>
> I was also hoping for some performance benefits,
> but my testing shows that
>
> jsonb_value['existing_key'] = new_value;
>
> takes just as long time as
>
> jsonb_value := jsonb_set(jsonb_value, ARRAY['existing_key'], new_value);
>
> which is a bit surprising to me. Shouldn't subscripting be a lot faster,
> since it could modify the existing data structure in-place? What am I
> missing here?
>

no - it doesn't support in-place modification. Only arrays and records
support it.

> I came to think of the this new functionality when trying to optimize some
> PL/pgSQL code where the bottle-neck turned out to be lots of calls
> to jsonb_set() for large jsonb objects.
>

sure - there is big room for optimization. But this patch was big enough
without its optimization. And it was not clean, if I will be committed or
not (it waited in commitfest application for 4 years). So I accepted
implemented behaviour (without inplace update). Now, this patch is in core,
and anybody can work on others possible optimizations.

Regards

Pavel

>
> Here is the output from attached bench:
>
> n=10000
> 00:00:00.002628 jsonb := jsonb_set(jsonb, ARRAY[existing key], value);
> 00:00:00.002778 jsonb := jsonb_set(jsonb, ARRAY[new key], value);
> 00:00:00.002332 jsonb[existing key] := value;
> 00:00:00.002794 jsonb[new key] := value;
> n=100000
> 00:00:00.042843 jsonb := jsonb_set(jsonb, ARRAY[existing key], value);
> 00:00:00.046515 jsonb := jsonb_set(jsonb, ARRAY[new key], value);
> 00:00:00.044974 jsonb[existing key] := value;
> 00:00:00.075429 jsonb[new key] := value;
> n=1000000
> 00:00:00.420808 jsonb := jsonb_set(jsonb, ARRAY[existing key], value);
> 00:00:00.449622 jsonb := jsonb_set(jsonb, ARRAY[new key], value);
> 00:00:00.31834 jsonb[existing key] := value;
> 00:00:00.527904 jsonb[new key] := value;
>
> Many thanks for clarifying.
>
> Best regards,
>
> Joel
>