Optimization of some jsonb functions

Attached set of es with some jsonb optimizations that were made during
comparison of performance of ordinal jsonb operators and jsonpath operators.

1. Optimize JsonbExtractScalar():
It is better to use getIthJsonbValueFromContainer(cont, 0) instead of
JsonIterator to get 0th element of raw-scalar pseudoarray.
JsonbExtractScalar() is used in jsonb casts, so they speed up a bit.

2. Optimize operator #>>, jsonb_each_text(), jsonb_array_elements_text():
These functions have direct conversion (JsonbValue => text) only for
jbvString scalars, but indirect conversion of other types of scalars
(JsonbValue => jsonb => text) is obviously too slow. Extracted common
subroutine JsonbValueAsText() and used in all suitable places.

3. Optimize JsonbContainer type recognition in get_jsonb_path_all():
Fetching of the first token from JsonbIterator is replaced with lightweight
JsonbContainerIsXxx() macros.

4. Extract findJsonbKeyInObject():
Extracted findJsonbKeyInObject() from findJsonbValueFromContainer(),
which is slightly easier to use (key string and its length is passed instead
of filled string JsonbValue).

5. Optimize resulting value allocation in findJsonbValueFromContainer() and
getIthJsonbValueFromContainer():
Added ability to pass stack-allocated JsonbValue that will be filled with
the result of operation instead of returning unconditionally palloc()ated
JsonbValue.

es #4 and #5 are mostly refactorings, but they can give small speedup
(up to 5% for upcoming jsonpath operators) due to elimination of unnecessary
palloc()s. The whole interface of findJsonbValueFromContainer() with JB_OBJECT
and JB_ARRAY flags always seemed a bit strange to me, so I think it is worth to
have separate functions for searching keys in objects and elements in arrays.

Performance tests:
- Test data for {"x": {"y": {"z": i}}}:
CREATE TABLE t AS
SELECT jsonb_build_object('x',
jsonb_build_object('y',
jsonb_build_object('z', i))) js
FROM generate_series(1, 3000000) i;

- Sample query:
EXPLAIN (ANALYZE) SELECT js -> 'x' -> 'y' -> 'z' FROM t;

- Results:
| execution time, ms
query | master | optimized
-------------------------------------------------------------------------------
{"x": {"y": {"z": i}}}
js #> '{x,y,z}' | 1148.632 | 1005.578 -10%
js #>> '{x,y,z}' | 1520.160 | 849.991 -40%
(js #> '{x,y,z}')::numeric | 1310.881 | 1067.752 -20%
(js #>> '{x,y,z}')::numeric | 1757.179 | 1109.495 -30%

js -> 'x' -> 'y' -> 'z' | 1030.211 | 977.267
js -> 'x' -> 'y' ->> 'z' | 887.101 | 838.745
(js -> 'x' -> 'y' -> 'z')::numeric | 1184.086 | 1050.462
(js -> 'x' -> 'y' -> 'z')::int4 | 1279.315 | 1133.032
(js -> 'x' -> 'y' ->> 'z')::numeric | 1134.003 | 1100.047
(js -> 'x' -> 'y' ->> 'z')::int4 | 1077.216 | 991.995

js ? 'x' | 523.111 | 495.387
js ?| '{x,y,z}' | 612.880 | 607.455
js ?& '{x,y,z}' | 674.786 | 643.987
js -> 'x' -> 'y' ? 'z' | 712.623 | 698.588
js @> '{"x": {"y": {"z": 1}}}' | 1154.926 | 1149.069

jsonpath:
js @@ '$.x.y.z == 123' | 973,444 | 912,08 -5%

{"x": i, "y": i, "z": i}
jsonb_each(js) | 2281.577 | 2262.660
jsonb_each_text(js) | 2603.539 | 2112.200 -20%

[i, i, i]
jsonb_array_elements(js) | 1255.210 | 1205.939
jsonb_array_elements(js)::numeric | 1662.550 | 1576.227 -5%
jsonb_array_elements_text(js) | 1555.021 | 1067.031 -30%

js @> '1' | 798.858 | 768.664 -4%
js <@ '[1,2,3]' | 820.795 | 785.086 -5%
js <@ '[0,1,2,3,4,5,6,7,8,9]' | 1214.170 | 1165.289 -5%

As it can be seen, #> operators are always slower than equivalent series of ->.
I think it is caused by array deconstruction in "jsonb #> text[]".

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

On 2/22/19 2:05 AM, Nikita Glukhov wrote:
> Attached set of es with some jsonb optimizations that were made during
> comparison of performance of ordinal jsonb operators and jsonpath operators.

This was submitted just before the last commitfest for PG12 and
seems to have potential for breakage.

I have updated the target to PG13.

Regards,
--
-David
david(at)pgmasters(dot)net

On 3/5/19 5:24 AM, David Steele wrote:
> On 2/22/19 2:05 AM, Nikita Glukhov wrote:
>> Attached set of es with some jsonb optimizations that were made
>> during
>> comparison of performance of ordinal jsonb operators and jsonpath
>> operators.
>
> This was submitted just before the last commitfest for PG12 and
> seems to have potential for breakage.
>
> I have updated the target to PG13.
>
>

I think that's overly cautious. The first one I looked at, to optimize
JsonbExtractScalar, is very small, self-contained, and I think low risk.
I haven't looked at the others in detail, but I think at least some part
of this is reasonably committable.

I'll try to look at the others fairly shortly.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hi Andrew,

On 3/6/19 9:50 PM, Andrew Dunstan wrote:
>
> On 3/5/19 5:24 AM, David Steele wrote:
>> On 2/22/19 2:05 AM, Nikita Glukhov wrote:
>>> Attached set of es with some jsonb optimizations that were made
>>> during
>>> comparison of performance of ordinal jsonb operators and jsonpath
>>> operators.
>>
>> This was submitted just before the last commitfest for PG12 and
>> seems to have potential for breakage.
>>
>> I have updated the target to PG13.
>>
>>
>
> I think that's overly cautious. The first one I looked at, to optimize
> JsonbExtractScalar, is very small, self-contained, and I think low risk.
> I haven't looked at the others in detail, but I think at least some part
> of this is reasonably committable.
>
>
> I'll try to look at the others fairly shortly.

If you decide all or part of this can be committed then feel free to
update the target version.

Regards,
--
-David
david(at)pgmasters(dot)net

> >> On 2/22/19 2:05 AM, Nikita Glukhov wrote:
> >>> Attached set of es with some jsonb optimizations that were made
> >>> during
> >>> comparison of performance of ordinal jsonb operators and jsonpath
> >>> operators.

Hi Nikita,

This doesn't apply -- to attract reviewers, could we please have a rebase?

Thanks,

--
Thomas Munro
https://enterprisedb.com

Thomas Munro wrote:
> This doesn't apply -- to attract reviewers, could we please have a rebase?

To help the review go forward, I have rebased the on 27cd521e6e.
It passes `make check` for me, but that's as far as I've verified the
correctness.

I squashed the changes into a single , sorry if that makes it
harder to review than the original set of five files...

--
Joe Nelson https://begriffs.com

On 2019-Jul-26, Joe Nelson wrote:

> Thomas Munro wrote:
> > This doesn't apply -- to attract reviewers, could we please have a rebase?
>
> To help the review go forward, I have rebased the on 27cd521e6e.
> It passes `make check` for me, but that's as far as I've verified the
> correctness.
>
> I squashed the changes into a single , sorry if that makes it
> harder to review than the original set of five files...

Well, I think that was useless, so I rebased again -- attached.
(Thanks, git-imerge).

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2019-Sep-18, Alvaro Herrera wrote:

> Well, I think that was useless, so I rebased again -- attached.

... which is how you find out that 0001 as an independent is not
really a valid one, since it depends on an API change that does not
happen until 0005.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2019-Sep-19, Alvaro Herrera wrote:

> On 2019-Sep-18, Alvaro Herrera wrote:
>
> > Well, I think that was useless, so I rebased again -- attached.
>
> ... which is how you find out that 0001 as an independent is not
> really a valid one, since it depends on an API change that does not
> happen until 0005.

... and there were other compilation problems too, presumably fixed
silently by Joe in his rebase, but which I fixed again for this series
which now seems more credible. I tested compile and regression tests
after each , it all works locally.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

I pushed the first few parts. The attached is a rebased copy of the
last remaining piece. However, I didn't quite understand what this was
doing, so I refrained from pushing. I think there are two es here:
one that adapts the API of findJsonbValueFromContainer and
getIthJsonbValueFromContainer to take the output result pointer as an
argument, allowing to save palloc cycles just like the newly added
getKeyJsonValueFromContainer(); and the other changes JsonbDeepContains
so that it uses a new function (which is a function with a weird API
that would be extracted from findJsonbValueFromContainer).

Also, the current just passes NULL into the routines from
jsonpath_exec.c but I think it would be useful to pass pointers into
stack-allocated result structs instead, at least in getJsonPathVariable.

Since the majority of this set got pushed, I'll leave this for
Nikita to handle for the next commitfest if he wants to, and mark this
CF entry as committed.

Thanks!

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services