st 14. 4. 2021 v 7:39 odesÃlatel Joel Jacobson <j...@compiler.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 >
