Re: jsonb subscripting assignment performance

On Wed, Apr 14, 2021 at 11:09 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> st 14. 4. 2021 v 9:57 odesílatel Dmitry Dolgov <9erthalion6(at)gmail(dot)com>
> napsal:
>
>> > On Wed, Apr 14, 2021 at 09:20:08AM +0200, Pavel Stehule wrote:
>> > 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.
>>
>> Right, jsonb subscripting deals mostly with the syntax part and doesn't
>> change internal jsonb behaviour. If I understand the original question
>> correctly, "in-place" here means updating of e.g. just one particular
>> key within a jsonb object, since jsonb_set looks like an overwrite of
>> the whole jsonb. If so, then update will still cause the whole jsonb to
>> be updated, there is no partial update functionality for the on-disk
>> format. Although there is work going on to optimize this in case when
>> jsonb is big enough to be put into a toast table (partial toast
>> decompression thread, or bytea appendable toast).
>>
>
> Almost all and almost everywhere Postgres's values are immutable. There is
> only one exception - runtime plpgsql. "local variables" can hold values of
> complex values unboxed. Then the repeated update is significantly cheaper.
> Normal non repeated updates have the same speed, because the value should
> be unboxed and boxed. Outside plpgsql the values are immutable. I think
> this is a very hard problem, how to update big toasted values effectively,
> and I am not sure if there is a solution. TOAST value is immutable. It
> needs to introduce some alternative to TOAST. The benefits are clear - it
> can be nice to have fast append arrays for time series. But this is a very
> different topic.
>

I and Nikita are working on OLTP jsonb
http://www.sai.msu.su/~megera/postgres/talks/jsonb-pgconfonline-2021.pdf

>
> Regards
>
> Pavel
>
>
>
>
>
>
>
>

--
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company