Horton

Thread

Commits

  1. Split out code into new getKeyJsonValueFromContainer()

  2. Optimize get_jsonb_path_all avoiding an iterator

  3. Refactor code into new JsonbValueAsText, and use it more

  1. Optimization of some jsonb functions

    Nikita Glukhov <n.gluhov@postgrespro.ru> — 2019-02-22T00:05:33Z

    Attached set of patches 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.

Patches #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

  2. Re: Optimization of some jsonb functions

    David Steele <david@pgmasters.net> — 2019-03-05T10:24:23Z

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

This patch 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@pgmasters.net

  3. Re: Optimization of some jsonb functions

    Andrew Dunstan <andrew.dunstan@2ndquadrant.com> — 2019-03-06T19:50:57Z

    On 3/5/19 5:24 AM, David Steele wrote:
> On 2/22/19 2:05 AM, Nikita Glukhov wrote:
>> Attached set of patches with some jsonb optimizations that were made
>> during
>> comparison of performance of ordinal jsonb operators and jsonpath
>> operators.
>
> This patch 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

  4. Re: Optimization of some jsonb functions

    David Steele <david@pgmasters.net> — 2019-03-07T12:23:53Z

    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 patches with some jsonb optimizations that were made
>>> during
>>> comparison of performance of ordinal jsonb operators and jsonpath
>>> operators.
>>
>> This patch 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@pgmasters.net

  5. Re: Optimization of some jsonb functions

    Thomas Munro <thomas.munro@gmail.com> — 2019-07-01T10:50:35Z

    > >> On 2/22/19 2:05 AM, Nikita Glukhov wrote:
> >>> Attached set of patches 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

  6. Re: Optimization of some jsonb functions

    Joe Nelson <joe@begriffs.com> — 2019-07-26T19:34:22Z

    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 patch 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 patch, sorry if that makes it
harder to review than the original set of five patch files...

--
Joe Nelson      https://begriffs.com

  7. Re: Optimization of some jsonb functions

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-09-18T22:18:13Z

    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 patch 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 patch, sorry if that makes it
> harder to review than the original set of five patch 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

  8. Re: Optimization of some jsonb functions

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-09-19T03:09:06Z

    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 patch 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

  9. Re: Optimization of some jsonb functions

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-09-19T03:47:15Z

    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 patch 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 patch, it all works locally.

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

  10. Re: Optimization of some jsonb functions

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-09-21T00:09:31Z

    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 patches 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 patch 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 patchset 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