Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gwyKu-0007VY-Tr for pgsql-hackers@arkaria.postgresql.org; Fri, 22 Feb 2019 00:05:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gwyKt-0003rb-Kz for pgsql-hackers@arkaria.postgresql.org; Fri, 22 Feb 2019 00:05:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gwyKt-0003rU-7A for pgsql-hackers@lists.postgresql.org; Fri, 22 Feb 2019 00:05:03 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gwyKp-0005dT-Nu for pgsql-hackers@postgresql.org; Fri, 22 Feb 2019 00:05:02 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 35B1B21CCB16 for ; Fri, 22 Feb 2019 03:04:58 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [10.100.236.31] (unknown [195.82.154.27]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id E7C4821CCAEE for ; Fri, 22 Feb 2019 03:04:57 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1550793898; bh=9eo8MgZxuMKvaUtLSmXZ4P2er/UFf9llEZBkPwIQgwo=; h=To:From:Subject:Date; b=F1/Pyujf/RIV0MM2k61qJuoi1K8L1Yl3kNDywuYqmuvZllXg9JfcKBnAswNmOZ0D8 Rzt/qZU4l7OtxqXHXWhxxsL3TsN6igQEMaFTr2+TTbvze3hWZMBPXvawRqf7ovzwjk uVeUPq3GgdMN3IqQFpPJugQMo0o12lD5u2PdPTQk= To: PostgreSQL mailing lists From: Nikita Glukhov Organization: Postgres Professional Subject: Optimization of some jsonb functions Message-ID: <7c417f90-f95f-247e-ba63-d95e39c0ad14@postgrespro.ru> Date: Fri, 22 Feb 2019 03:05:33 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.4.0 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="------------55D1241744B27FF04177252F" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------55D1241744B27FF04177252F Content-Type: multipart/alternative; boundary="------------489D291128FCBA59805073B1" --------------489D291128FCBA59805073B1 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------489D291128FCBA59805073B1 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit 
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
--------------489D291128FCBA59805073B1-- --------------55D1241744B27FF04177252F Content-Type: text/x-patch; name="0001-Optimize-JsonbExtractScalar-v01.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0001-Optimize-JsonbExtractScalar-v01.patch" From 031c74c2c8de9f38cc484b55fb2a5f11279c9bb8 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Thu, 21 Feb 2019 02:52:24 +0300 Subject: [PATCH 1/5] Optimize JsonbExtractScalar() --- src/backend/utils/adt/jsonb.c | 25 +++---------------------- 1 file changed, 3 insertions(+), 22 deletions(-) diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index c02c856..7e9e99f 100644 --- a/src/backend/utils/adt/jsonb.c +++ b/src/backend/utils/adt/jsonb.c @@ -1860,9 +1860,7 @@ jsonb_object_agg_finalfn(PG_FUNCTION_ARGS) static bool JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res) { - JsonbIterator *it; - JsonbIteratorToken tok PG_USED_FOR_ASSERTS_ONLY; - JsonbValue tmp; + JsonbValue *scalar PG_USED_FOR_ASSERTS_ONLY; if (!JsonContainerIsArray(jbc) || !JsonContainerIsScalar(jbc)) { @@ -1871,25 +1869,8 @@ JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res) return false; } - /* - * A root scalar is stored as an array of one element, so we get the array - * and then its first (and only) member. - */ - it = JsonbIteratorInit(jbc); - - tok = JsonbIteratorNext(&it, &tmp, true); - Assert(tok == WJB_BEGIN_ARRAY); - Assert(tmp.val.array.nElems == 1 && tmp.val.array.rawScalar); - - tok = JsonbIteratorNext(&it, res, true); - Assert(tok == WJB_ELEM); - Assert(IsAJsonbScalar(res)); - - tok = JsonbIteratorNext(&it, &tmp, true); - Assert(tok == WJB_END_ARRAY); - - tok = JsonbIteratorNext(&it, &tmp, true); - Assert(tok == WJB_DONE); + scalar = getIthJsonbValueFromContainer(jbc, 0, res); + Assert(scalar); return true; } -- 2.7.4 --------------55D1241744B27FF04177252F Content-Type: text/x-patch; name="0002-Optimize-jsonb_xxx_text-functions-v01.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0002-Optimize-jsonb_xxx_text-functions-v01.patch" From ebb568e7085d1c8a61b544fa73688b59304d4c30 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Thu, 21 Feb 2019 03:04:13 +0300 Subject: [PATCH 2/5] Optimize jsonb operator #>> using extracted JsonbValueAsText() --- src/backend/utils/adt/jsonfuncs.c | 164 ++++++++++++-------------------------- 1 file changed, 50 insertions(+), 114 deletions(-) diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index dd88c09..162dffa 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -748,6 +748,47 @@ json_object_field_text(PG_FUNCTION_ARGS) PG_RETURN_NULL(); } +static text * +JsonbValueAsText(JsonbValue *v) +{ + switch (v->type) + { + case jbvNull: + return NULL; + + case jbvBool: + return v->val.boolean ? + cstring_to_text_with_len("true", 4) : + cstring_to_text_with_len("false", 5); + + case jbvString: + return cstring_to_text_with_len(v->val.string.val, + v->val.string.len); + + case jbvNumeric: + { + Datum cstr = DirectFunctionCall1(numeric_out, + PointerGetDatum(v->val.numeric)); + + return cstring_to_text(DatumGetCString(cstr)); + } + + case jbvBinary: + { + StringInfoData jtext; + + initStringInfo(&jtext); + (void) JsonbToCString(&jtext, v->val.binary.data, -1); + + return cstring_to_text_with_len(jtext.data, jtext.len); + } + + default: + elog(ERROR, "unrecognized jsonb type: %d", (int) v->type); + return NULL; + } +} + Datum jsonb_object_field_text(PG_FUNCTION_ARGS) { @@ -762,39 +803,9 @@ jsonb_object_field_text(PG_FUNCTION_ARGS) VARDATA_ANY(key), VARSIZE_ANY_EXHDR(key)); - if (v != NULL) - { - text *result = NULL; - - switch (v->type) - { - case jbvNull: - break; - case jbvBool: - result = cstring_to_text(v->val.boolean ? "true" : "false"); - break; - case jbvString: - result = cstring_to_text_with_len(v->val.string.val, v->val.string.len); - break; - case jbvNumeric: - result = cstring_to_text(DatumGetCString(DirectFunctionCall1(numeric_out, - PointerGetDatum(v->val.numeric)))); - break; - case jbvBinary: - { - StringInfo jtext = makeStringInfo(); - - (void) JsonbToCString(jtext, v->val.binary.data, -1); - result = cstring_to_text_with_len(jtext->data, jtext->len); - } - break; - default: - elog(ERROR, "unrecognized jsonb type: %d", (int) v->type); - } - if (result) - PG_RETURN_TEXT_P(result); - } + if (v != NULL && v->type != jbvNull) + PG_RETURN_TEXT_P(JsonbValueAsText(v)); PG_RETURN_NULL(); } @@ -879,39 +890,9 @@ jsonb_array_element_text(PG_FUNCTION_ARGS) } v = getIthJsonbValueFromContainer(&jb->root, element); - if (v != NULL) - { - text *result = NULL; - - switch (v->type) - { - case jbvNull: - break; - case jbvBool: - result = cstring_to_text(v->val.boolean ? "true" : "false"); - break; - case jbvString: - result = cstring_to_text_with_len(v->val.string.val, v->val.string.len); - break; - case jbvNumeric: - result = cstring_to_text(DatumGetCString(DirectFunctionCall1(numeric_out, - PointerGetDatum(v->val.numeric)))); - break; - case jbvBinary: - { - StringInfo jtext = makeStringInfo(); - - (void) JsonbToCString(jtext, v->val.binary.data, -1); - result = cstring_to_text_with_len(jtext->data, jtext->len); - } - break; - default: - elog(ERROR, "unrecognized jsonb type: %d", (int) v->type); - } - if (result) - PG_RETURN_TEXT_P(result); - } + if (v != NULL && v->type != jbvNull) + PG_RETURN_TEXT_P(JsonbValueAsText(v)); PG_RETURN_NULL(); } @@ -1389,7 +1370,6 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) { Jsonb *jb = PG_GETARG_JSONB_P(0); ArrayType *path = PG_GETARG_ARRAYTYPE_P(1); - Jsonb *res; Datum *pathtext; bool *pathnulls; int npath; @@ -1397,7 +1377,6 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) bool have_object = false, have_array = false; JsonbValue *jbvp = NULL; - JsonbValue tv; JsonbContainer *container; /* @@ -1526,24 +1505,15 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) if (as_text) { - /* special-case outputs for string and null values */ - if (jbvp->type == jbvString) - PG_RETURN_TEXT_P(cstring_to_text_with_len(jbvp->val.string.val, - jbvp->val.string.len)); if (jbvp->type == jbvNull) PG_RETURN_NULL(); - } - res = JsonbValueToJsonb(jbvp); - - if (as_text) - { - PG_RETURN_TEXT_P(cstring_to_text(JsonbToCString(NULL, - &res->root, - VARSIZE(res)))); + PG_RETURN_TEXT_P(JsonbValueAsText(jbvp)); } else { + Jsonb *res = JsonbValueToJsonb(jbvp); + /* not text mode - just hand back the jsonb */ PG_RETURN_JSONB_P(res); } @@ -1760,24 +1730,7 @@ each_worker_jsonb(FunctionCallInfo fcinfo, const char *funcname, bool as_text) } else { - text *sv; - - if (v.type == jbvString) - { - /* In text mode, scalar strings should be dequoted */ - sv = cstring_to_text_with_len(v.val.string.val, v.val.string.len); - } - else - { - /* Turn anything else into a json string */ - StringInfo jtext = makeStringInfo(); - Jsonb *jb = JsonbValueToJsonb(&v); - - (void) JsonbToCString(jtext, &jb->root, 0); - sv = cstring_to_text_with_len(jtext->data, jtext->len); - } - - values[1] = PointerGetDatum(sv); + values[1] = PointerGetDatum(JsonbValueAsText(&v)); } } else @@ -2070,24 +2023,7 @@ elements_worker_jsonb(FunctionCallInfo fcinfo, const char *funcname, } else { - text *sv; - - if (v.type == jbvString) - { - /* in text mode scalar strings should be dequoted */ - sv = cstring_to_text_with_len(v.val.string.val, v.val.string.len); - } - else - { - /* turn anything else into a json string */ - StringInfo jtext = makeStringInfo(); - Jsonb *jb = JsonbValueToJsonb(&v); - - (void) JsonbToCString(jtext, &jb->root, 0); - sv = cstring_to_text_with_len(jtext->data, jtext->len); - } - - values[0] = PointerGetDatum(sv); + values[0] = PointerGetDatum(JsonbValueAsText(&v)); } } -- 2.7.4 --------------55D1241744B27FF04177252F Content-Type: text/x-patch; name="0003-Optimize-JsonbContainer-type-recognition-v01.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0003-Optimize-JsonbContainer-type-recognition-v01.patch" From ac39171a4f2f8cad485c9829b40c643e5a9282ab Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Thu, 21 Feb 2019 03:08:44 +0300 Subject: [PATCH 3/5] Optimize JsonbContainer type recognition in get_jsonb_path_all() --- src/backend/utils/adt/jsonfuncs.c | 16 +++++++--------- 1 file changed, 7 insertions(+), 9 deletions(-) diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index 162dffa..cfbd89b 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -1488,18 +1488,16 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) if (jbvp->type == jbvBinary) { - JsonbIterator *it = JsonbIteratorInit((JsonbContainer *) jbvp->val.binary.data); - JsonbIteratorToken r; - - r = JsonbIteratorNext(&it, &tv, true); - container = (JsonbContainer *) jbvp->val.binary.data; - have_object = r == WJB_BEGIN_OBJECT; - have_array = r == WJB_BEGIN_ARRAY; + container = jbvp->val.binary.data; + have_object = JsonContainerIsObject(container); + have_array = JsonContainerIsArray(container); + Assert(!JsonbContainerIsScalar(container)); } else { - have_object = jbvp->type == jbvObject; - have_array = jbvp->type == jbvArray; + Assert(IsAJsonbScalar(jbvp)); + have_object = false; + have_array = false; } } -- 2.7.4 --------------55D1241744B27FF04177252F Content-Type: text/x-patch; name="0004-Extract-findJsonbKeyInObject-v01.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0004-Extract-findJsonbKeyInObject-v01.patch" From 4e9a734191859c7a6f17acc0354d419c84b5e817 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Thu, 21 Feb 2019 19:17:38 +0300 Subject: [PATCH 4/5] Extract findJsonbKeyInObject() --- src/backend/utils/adt/jsonb_util.c | 147 ++++++++++++++++++++++--------------- src/backend/utils/adt/jsonfuncs.c | 51 ++++--------- src/include/utils/jsonb.h | 2 + 3 files changed, 107 insertions(+), 93 deletions(-) diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c index 6695363..732d715 100644 --- a/src/backend/utils/adt/jsonb_util.c +++ b/src/backend/utils/adt/jsonb_util.c @@ -57,6 +57,8 @@ static void appendValue(JsonbParseState *pstate, JsonbValue *scalarVal); static void appendElement(JsonbParseState *pstate, JsonbValue *scalarVal); static int lengthCompareJsonbStringValue(const void *a, const void *b); static int lengthCompareJsonbPair(const void *a, const void *b, void *arg); +static int lengthCompareJsonbString(const char *val1, int len1, + const char *val2, int len2); static void uniqueifyJsonbObject(JsonbValue *object); static JsonbValue *pushJsonbValueScalar(JsonbParseState **pstate, JsonbIteratorToken seq, @@ -297,6 +299,71 @@ compareJsonbContainers(JsonbContainer *a, JsonbContainer *b) return res; } +/* Find value by key in Jsonb object and fetch it into 'res'. */ +JsonbValue * +findJsonbKeyInObject(JsonbContainer *container, const char *keyVal, int keyLen, + JsonbValue *res) +{ + JEntry *children = container->children; + JsonbValue *result = res; + int count = JsonContainerSize(container); + /* Since this is an object, account for *Pairs* of Jentrys */ + char *baseAddr = (char *) (children + count * 2); + uint32 stopLow = 0, + stopHigh = count; + + Assert(JsonContainerIsObject(container)); + + /* Quick out without a palloc cycle if object is empty */ + if (count <= 0) + return NULL; + + if (!result) + result = palloc(sizeof(JsonbValue)); + + /* Binary search on object/pair keys *only* */ + while (stopLow < stopHigh) + { + uint32 stopMiddle; + int difference; + const char *candidateVal; + int candidateLen; + + stopMiddle = stopLow + (stopHigh - stopLow) / 2; + + candidateVal = baseAddr + getJsonbOffset(container, stopMiddle); + candidateLen = getJsonbLength(container, stopMiddle); + + difference = lengthCompareJsonbString(candidateVal, candidateLen, + keyVal, keyLen); + + if (difference == 0) + { + /* Found our key, return corresponding value */ + int index = stopMiddle + count; + + fillJsonbValue(container, index, baseAddr, + getJsonbOffset(container, index), + result); + + return result; + } + else + { + if (difference < 0) + stopLow = stopMiddle + 1; + else + stopHigh = stopMiddle; + } + } + + /* Not found */ + if (!res) + pfree(result); + + return NULL; +} + /* * Find value in object (i.e. the "value" part of some key/value pair in an * object), or find a matching element if we're looking through an array. Do @@ -329,7 +396,6 @@ findJsonbValueFromContainer(JsonbContainer *container, uint32 flags, { JEntry *children = container->children; int count = JsonContainerSize(container); - JsonbValue *result; Assert((flags & ~(JB_FARRAY | JB_FOBJECT)) == 0); @@ -337,10 +403,9 @@ findJsonbValueFromContainer(JsonbContainer *container, uint32 flags, if (count <= 0) return NULL; - result = palloc(sizeof(JsonbValue)); - if ((flags & JB_FARRAY) && JsonContainerIsArray(container)) { + JsonbValue *result = palloc(sizeof(JsonbValue)); char *base_addr = (char *) (children + count); uint32 offset = 0; int i; @@ -357,56 +422,19 @@ findJsonbValueFromContainer(JsonbContainer *container, uint32 flags, JBE_ADVANCE_OFFSET(offset, children[i]); } + + pfree(result); } else if ((flags & JB_FOBJECT) && JsonContainerIsObject(container)) { - /* Since this is an object, account for *Pairs* of Jentrys */ - char *base_addr = (char *) (children + count * 2); - uint32 stopLow = 0, - stopHigh = count; - /* Object key passed by caller must be a string */ Assert(key->type == jbvString); - /* Binary search on object/pair keys *only* */ - while (stopLow < stopHigh) - { - uint32 stopMiddle; - int difference; - JsonbValue candidate; - - stopMiddle = stopLow + (stopHigh - stopLow) / 2; - - candidate.type = jbvString; - candidate.val.string.val = - base_addr + getJsonbOffset(container, stopMiddle); - candidate.val.string.len = getJsonbLength(container, stopMiddle); - - difference = lengthCompareJsonbStringValue(&candidate, key); - - if (difference == 0) - { - /* Found our key, return corresponding value */ - int index = stopMiddle + count; - - fillJsonbValue(container, index, base_addr, - getJsonbOffset(container, index), - result); - - return result; - } - else - { - if (difference < 0) - stopLow = stopMiddle + 1; - else - stopHigh = stopMiddle; - } - } + return findJsonbKeyInObject(container, key->val.string.val, + key->val.string.len, NULL); } /* Not found */ - pfree(result); return NULL; } @@ -1009,6 +1037,7 @@ JsonbDeepContains(JsonbIterator **val, JsonbIterator **mContained) for (;;) { JsonbValue *lhsVal; /* lhsVal is from pair in lhs object */ + JsonbValue lhsValBuf; rcont = JsonbIteratorNext(mContained, &vcontained, false); @@ -1021,11 +1050,13 @@ JsonbDeepContains(JsonbIterator **val, JsonbIterator **mContained) return true; Assert(rcont == WJB_KEY); + Assert(vcontained.type == jbvString); /* First, find value by key... */ - lhsVal = findJsonbValueFromContainer((*val)->container, - JB_FOBJECT, - &vcontained); + lhsVal = findJsonbKeyInObject((*val)->container, + vcontained.val.string.val, + vcontained.val.string.len, + &lhsValBuf); if (!lhsVal) return false; @@ -1746,6 +1777,15 @@ convertJsonbScalar(StringInfo buffer, JEntry *jentry, JsonbValue *scalarVal) } } +static int +lengthCompareJsonbString(const char *val1, int len1, const char *val2, int len2) +{ + if (len1 == len2) + return memcmp(val1, val2, len1); + else + return len1 > len2 ? 1 : -1; +} + /* * Compare two jbvString JsonbValue values, a and b. * @@ -1763,21 +1803,12 @@ lengthCompareJsonbStringValue(const void *a, const void *b) { const JsonbValue *va = (const JsonbValue *) a; const JsonbValue *vb = (const JsonbValue *) b; - int res; Assert(va->type == jbvString); Assert(vb->type == jbvString); - if (va->val.string.len == vb->val.string.len) - { - res = memcmp(va->val.string.val, vb->val.string.val, va->val.string.len); - } - else - { - res = (va->val.string.len > vb->val.string.len) ? 1 : -1; - } - - return res; + return lengthCompareJsonbString(va->val.string.val, va->val.string.len, + vb->val.string.val, vb->val.string.len); } /* diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index cfbd89b..b71a848 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -454,12 +454,6 @@ static Datum populate_array(ArrayIOData *aio, const char *colname, static Datum populate_domain(DomainIOData *io, Oid typid, const char *colname, MemoryContext mcxt, JsValue *jsv, bool isnull); -/* Worker that takes care of common setup for us */ -static JsonbValue *findJsonbValueFromContainerLen(JsonbContainer *container, - uint32 flags, - char *key, - uint32 keylen); - /* functions supporting jsonb_delete, jsonb_set and jsonb_concat */ static JsonbValue *IteratorConcat(JsonbIterator **it1, JsonbIterator **it2, JsonbParseState **state); @@ -718,13 +712,15 @@ jsonb_object_field(PG_FUNCTION_ARGS) Jsonb *jb = PG_GETARG_JSONB_P(0); text *key = PG_GETARG_TEXT_PP(1); JsonbValue *v; + JsonbValue vbuf; if (!JB_ROOT_IS_OBJECT(jb)) PG_RETURN_NULL(); - v = findJsonbValueFromContainerLen(&jb->root, JB_FOBJECT, - VARDATA_ANY(key), - VARSIZE_ANY_EXHDR(key)); + v = findJsonbKeyInObject(&jb->root, + VARDATA_ANY(key), + VARSIZE_ANY_EXHDR(key), + &vbuf); if (v != NULL) PG_RETURN_JSONB_P(JsonbValueToJsonb(v)); @@ -795,14 +791,15 @@ jsonb_object_field_text(PG_FUNCTION_ARGS) Jsonb *jb = PG_GETARG_JSONB_P(0); text *key = PG_GETARG_TEXT_PP(1); JsonbValue *v; + JsonbValue vbuf; if (!JB_ROOT_IS_OBJECT(jb)) PG_RETURN_NULL(); - v = findJsonbValueFromContainerLen(&jb->root, JB_FOBJECT, - VARDATA_ANY(key), - VARSIZE_ANY_EXHDR(key)); - + v = findJsonbKeyInObject(&jb->root, + VARDATA_ANY(key), + VARSIZE_ANY_EXHDR(key), + &vbuf); if (v != NULL && v->type != jbvNull) PG_RETURN_TEXT_P(JsonbValueAsText(v)); @@ -1434,10 +1431,10 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) { if (have_object) { - jbvp = findJsonbValueFromContainerLen(container, - JB_FOBJECT, - VARDATA(pathtext[i]), - VARSIZE(pathtext[i]) - VARHDRSZ); + jbvp = findJsonbKeyInObject(container, + VARDATA(pathtext[i]), + VARSIZE(pathtext[i]) - VARHDRSZ, + &jbvbuf); } else if (have_array) { @@ -3025,8 +3022,8 @@ JsObjectGetField(JsObject *obj, char *field, JsValue *jsv) else { jsv->val.jsonb = !obj->val.jsonb_cont ? NULL : - findJsonbValueFromContainerLen(obj->val.jsonb_cont, JB_FOBJECT, - field, strlen(field)); + findJsonbKeyInObject(obj->val.jsonb_cont, field, strlen(field), + NULL); return jsv->val.jsonb != NULL; } @@ -3839,22 +3836,6 @@ populate_recordset_object_field_end(void *state, char *fname, bool isnull) } /* - * findJsonbValueFromContainer() wrapper that sets up JsonbValue key string. - */ -static JsonbValue * -findJsonbValueFromContainerLen(JsonbContainer *container, uint32 flags, - char *key, uint32 keylen) -{ - JsonbValue k; - - k.type = jbvString; - k.val.string.val = key; - k.val.string.len = keylen; - - return findJsonbValueFromContainer(container, flags, &k); -} - -/* * Semantic actions for json_strip_nulls. * * Simply repeat the input on the output unless we encounter diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index 6ccacf5..c98c6f4 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -359,6 +359,8 @@ extern int compareJsonbContainers(JsonbContainer *a, JsonbContainer *b); extern JsonbValue *findJsonbValueFromContainer(JsonbContainer *sheader, uint32 flags, JsonbValue *key); +extern JsonbValue *findJsonbKeyInObject(JsonbContainer *container, + const char *keyVal, int keyLen, JsonbValue *res); extern JsonbValue *getIthJsonbValueFromContainer(JsonbContainer *sheader, uint32 i); extern JsonbValue *pushJsonbValue(JsonbParseState **pstate, -- 2.7.4 --------------55D1241744B27FF04177252F Content-Type: text/x-patch; name="0005-Optimize-resulting-JsonbValue-allocation.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0005-Optimize-resulting-JsonbValue-allocation.patch" From 76dfc312b2926764fce46bc20e6981e9527eaa0a Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Thu, 21 Feb 2019 19:19:22 +0300 Subject: [PATCH 5/5] Optimize resulting JsonbValue allocation in findJsonbValueFromContainer() and getIthJsonbValueFromContainer() --- src/backend/utils/adt/jsonb_op.c | 9 +++-- src/backend/utils/adt/jsonb_util.c | 72 +++++++++++++++++++++++--------------- src/backend/utils/adt/jsonfuncs.c | 11 +++--- src/include/utils/jsonb.h | 5 ++- 4 files changed, 58 insertions(+), 39 deletions(-) diff --git a/src/backend/utils/adt/jsonb_op.c b/src/backend/utils/adt/jsonb_op.c index a64206e..82c4b0b 100644 --- a/src/backend/utils/adt/jsonb_op.c +++ b/src/backend/utils/adt/jsonb_op.c @@ -24,6 +24,7 @@ jsonb_exists(PG_FUNCTION_ARGS) Jsonb *jb = PG_GETARG_JSONB_P(0); text *key = PG_GETARG_TEXT_PP(1); JsonbValue kval; + JsonbValue vval; JsonbValue *v = NULL; /* @@ -38,7 +39,7 @@ jsonb_exists(PG_FUNCTION_ARGS) v = findJsonbValueFromContainer(&jb->root, JB_FOBJECT | JB_FARRAY, - &kval); + &kval, &vval); PG_RETURN_BOOL(v != NULL); } @@ -59,6 +60,7 @@ jsonb_exists_any(PG_FUNCTION_ARGS) for (i = 0; i < elem_count; i++) { JsonbValue strVal; + JsonbValue valVal; if (key_nulls[i]) continue; @@ -69,7 +71,7 @@ jsonb_exists_any(PG_FUNCTION_ARGS) if (findJsonbValueFromContainer(&jb->root, JB_FOBJECT | JB_FARRAY, - &strVal) != NULL) + &strVal, &valVal) != NULL) PG_RETURN_BOOL(true); } @@ -92,6 +94,7 @@ jsonb_exists_all(PG_FUNCTION_ARGS) for (i = 0; i < elem_count; i++) { JsonbValue strVal; + JsonbValue valVal; if (key_nulls[i]) continue; @@ -102,7 +105,7 @@ jsonb_exists_all(PG_FUNCTION_ARGS) if (findJsonbValueFromContainer(&jb->root, JB_FOBJECT | JB_FARRAY, - &strVal) == NULL) + &strVal, &valVal) == NULL) PG_RETURN_BOOL(false); } diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c index 732d715..0b96425 100644 --- a/src/backend/utils/adt/jsonb_util.c +++ b/src/backend/utils/adt/jsonb_util.c @@ -299,6 +299,37 @@ compareJsonbContainers(JsonbContainer *a, JsonbContainer *b) return res; } +/* Find scalar element in Jsonb array and return it. */ +static JsonbValue * +findJsonbElementInArray(JsonbContainer *container, JsonbValue *elem, + JsonbValue *res) +{ + JsonbValue *result; + JEntry *children = container->children; + int count = JsonContainerSize(container); + char *baseAddr = (char *) (children + count); + uint32 offset = 0; + int i; + + result = res ? res : palloc(sizeof(*result)); + + for (i = 0; i < count; i++) + { + fillJsonbValue(container, i, baseAddr, offset, result); + + if (elem->type == result->type && + equalsJsonbScalarValue(elem, result)) + return result; + + JBE_ADVANCE_OFFSET(offset, children[i]); + } + + if (!res) + pfree(result); + + return NULL; +} + /* Find value by key in Jsonb object and fetch it into 'res'. */ JsonbValue * findJsonbKeyInObject(JsonbContainer *container, const char *keyVal, int keyLen, @@ -392,38 +423,19 @@ findJsonbKeyInObject(JsonbContainer *container, const char *keyVal, int keyLen, */ JsonbValue * findJsonbValueFromContainer(JsonbContainer *container, uint32 flags, - JsonbValue *key) + JsonbValue *key, JsonbValue *res) { - JEntry *children = container->children; int count = JsonContainerSize(container); Assert((flags & ~(JB_FARRAY | JB_FOBJECT)) == 0); - /* Quick out without a palloc cycle if object/array is empty */ + /* Quick out if object/array is empty */ if (count <= 0) return NULL; if ((flags & JB_FARRAY) && JsonContainerIsArray(container)) { - JsonbValue *result = palloc(sizeof(JsonbValue)); - char *base_addr = (char *) (children + count); - uint32 offset = 0; - int i; - - for (i = 0; i < count; i++) - { - fillJsonbValue(container, i, base_addr, offset, result); - - if (key->type == result->type) - { - if (equalsJsonbScalarValue(key, result)) - return result; - } - - JBE_ADVANCE_OFFSET(offset, children[i]); - } - - pfree(result); + return findJsonbElementInArray(container, key, res); } else if ((flags & JB_FOBJECT) && JsonContainerIsObject(container)) { @@ -431,7 +443,7 @@ findJsonbValueFromContainer(JsonbContainer *container, uint32 flags, Assert(key->type == jbvString); return findJsonbKeyInObject(container, key->val.string.val, - key->val.string.len, NULL); + key->val.string.len, res); } /* Not found */ @@ -444,9 +456,9 @@ findJsonbValueFromContainer(JsonbContainer *container, uint32 flags, * Returns palloc()'d copy of the value, or NULL if it does not exist. */ JsonbValue * -getIthJsonbValueFromContainer(JsonbContainer *container, uint32 i) +getIthJsonbValueFromContainer(JsonbContainer *container, uint32 i, + JsonbValue *result) { - JsonbValue *result; char *base_addr; uint32 nelements; @@ -459,7 +471,8 @@ getIthJsonbValueFromContainer(JsonbContainer *container, uint32 i) if (i >= nelements) return NULL; - result = palloc(sizeof(JsonbValue)); + if (!result) + result = palloc(sizeof(JsonbValue)); fillJsonbValue(container, i, base_addr, getJsonbOffset(container, i), @@ -1157,9 +1170,10 @@ JsonbDeepContains(JsonbIterator **val, JsonbIterator **mContained) if (IsAJsonbScalar(&vcontained)) { - if (!findJsonbValueFromContainer((*val)->container, - JB_FARRAY, - &vcontained)) + JsonbValue elemBuf; + + if (!findJsonbElementInArray((*val)->container, &vcontained, + &elemBuf)) return false; } else diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index b71a848..7f8b2e396 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -828,6 +828,7 @@ jsonb_array_element(PG_FUNCTION_ARGS) Jsonb *jb = PG_GETARG_JSONB_P(0); int element = PG_GETARG_INT32(1); JsonbValue *v; + JsonbValue vbuf; if (!JB_ROOT_IS_ARRAY(jb)) PG_RETURN_NULL(); @@ -843,7 +844,7 @@ jsonb_array_element(PG_FUNCTION_ARGS) element += nelements; } - v = getIthJsonbValueFromContainer(&jb->root, element); + v = getIthJsonbValueFromContainer(&jb->root, element, &vbuf); if (v != NULL) PG_RETURN_JSONB_P(JsonbValueToJsonb(v)); @@ -871,6 +872,7 @@ jsonb_array_element_text(PG_FUNCTION_ARGS) Jsonb *jb = PG_GETARG_JSONB_P(0); int element = PG_GETARG_INT32(1); JsonbValue *v; + JsonbValue vbuf; if (!JB_ROOT_IS_ARRAY(jb)) PG_RETURN_NULL(); @@ -886,7 +888,7 @@ jsonb_array_element_text(PG_FUNCTION_ARGS) element += nelements; } - v = getIthJsonbValueFromContainer(&jb->root, element); + v = getIthJsonbValueFromContainer(&jb->root, element, &vbuf); if (v != NULL && v->type != jbvNull) PG_RETURN_TEXT_P(JsonbValueAsText(v)); @@ -1374,6 +1376,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) bool have_object = false, have_array = false; JsonbValue *jbvp = NULL; + JsonbValue jbvbuf; JsonbContainer *container; /* @@ -1401,7 +1404,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) Assert(JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb)); /* Extract the scalar value, if it is what we'll return */ if (npath <= 0) - jbvp = getIthJsonbValueFromContainer(container, 0); + jbvp = getIthJsonbValueFromContainer(container, 0, &jbvbuf); } /* @@ -1470,7 +1473,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) index = nelements + lindex; } - jbvp = getIthJsonbValueFromContainer(container, index); + jbvp = getIthJsonbValueFromContainer(container, index, &jbvbuf); } else { diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index c98c6f4..8c47ad1 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -357,12 +357,11 @@ extern uint32 getJsonbOffset(const JsonbContainer *jc, int index); extern uint32 getJsonbLength(const JsonbContainer *jc, int index); extern int compareJsonbContainers(JsonbContainer *a, JsonbContainer *b); extern JsonbValue *findJsonbValueFromContainer(JsonbContainer *sheader, - uint32 flags, - JsonbValue *key); + uint32 flags, JsonbValue *key, JsonbValue *res); extern JsonbValue *findJsonbKeyInObject(JsonbContainer *container, const char *keyVal, int keyLen, JsonbValue *res); extern JsonbValue *getIthJsonbValueFromContainer(JsonbContainer *sheader, - uint32 i); + uint32 i, JsonbValue *result); extern JsonbValue *pushJsonbValue(JsonbParseState **pstate, JsonbIteratorToken seq, JsonbValue *jbVal); extern JsonbIterator *JsonbIteratorInit(JsonbContainer *container); -- 2.7.4 --------------55D1241744B27FF04177252F--