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
>From 031c74c2c8de9f38cc484b55fb2a5f11279c9bb8 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
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
>From ebb568e7085d1c8a61b544fa73688b59304d4c30 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
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
>From ac39171a4f2f8cad485c9829b40c643e5a9282ab Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
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
>From 4e9a734191859c7a6f17acc0354d419c84b5e817 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
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
>From 76dfc312b2926764fce46bc20e6981e9527eaa0a Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
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
