Re: Optimization of some jsonb functions
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 Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/src/backend/utils/adt/jsonb_op.c b/src/backend/utils/adt/jsonb_op.c
index a64206eeb1..82c4b0b2cb 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(>root,
JB_FOBJECT | JB_FARRAY,
- );
+ , );
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(>root,
JB_FOBJECT | JB_FARRAY,
- ) != NULL)
+ , ) != 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(>root,
JB_FOBJECT | JB_FARRAY,
- ) == NULL)
+ , ) == NULL)
PG_RETURN_BOOL(false);
}
diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index 7e0d9de7f0..7ec33cebf2 100644
--- a/src/backend/utils/adt/jsonb_util.c
+++ b/src/backend/utils/adt/jsonb_util.c
@@ -33,6 +33,8 @@
#define JSONB_MAX_ELEMS (Min(MaxAllocSize / sizeof(JsonbValue), JB_CMASK))
#define JSONB_MAX_PAIRS (Min(MaxAllocSize / sizeof(JsonbPair), JB_CMASK))
+static JsonbValue *findJsonbElementInArray(JsonbContainer *container,
+ JsonbValue *elem, JsonbValue *res);
static void fillJsonbValue(JsonbContainer *container, int index,
char *base_addr, uint32 offset,
JsonbValue *result);
@@ -327,38 +329,19 @@ compareJsonbContainers(JsonbContainer *a, JsonbContainer *b)
*/
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))
{
@@ -366,13 +349,48 @@ findJsonbValueFromContainer(JsonbContainer *container, uint32 flags,
Assert(key->type == jbvString);
return getKeyJsonValueFromContainer(container, key->val.string.val,
- key->val.string.len, NULL);
+ key->val.string.len, res);
}
/* Not found */
return NULL;
}
+/*
+ * Subroutine for findJsonbValueFromContainer
+ *
+ * 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
Re: Optimization of some jsonb functions
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 Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From d4fcb682356f21652e6adfdf8df741a56e745377 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov
Date: Thu, 21 Feb 2019 03:04:13 +0300
Subject: [PATCH v4 1/3] Optimize jsonb operator #>> using extracted
JsonbValueAsText()
---
src/backend/utils/adt/jsonfuncs.c | 180 ++
1 file changed, 57 insertions(+), 123 deletions(-)
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 667f9d9563..64bcf61daa 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -747,6 +747,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();
+(void) JsonbToCString(, 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)
{
@@ -761,39 +802,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();
}
@@ -878,39 +889,9 @@ jsonb_array_element_text(PG_FUNCTION_ARGS)
}
v = getIthJsonbValueFromContainer(>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();
}
@@ -1388,7 +1369,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;
@@ -1396,7 +1376,6 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
bool have_object = false,
have_array = false;
JsonbValue *jbvp = NULL;
- JsonbValue tv;
JsonbContainer *container;
/*
@@ -1508,41 +1487,30 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
if (jbvp->type == jbvBinary)
{
- JsonbIterator *it =
Re: Optimization of some jsonb functions
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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Optimization of some jsonb functions
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 Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 26ef2d6940dbba84b5e027f7d3a19f9dce948c7e Mon Sep 17 00:00:00 2001
From: Nikita Glukhov
Date: Thu, 21 Feb 2019 02:52:24 +0300
Subject: [PATCH v3 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 69f41ab455..9e1ad0a097 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1873,9 +1873,7 @@ jsonb_object_agg_finalfn(PG_FUNCTION_ARGS)
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))
{
@@ -1884,25 +1882,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(, , true);
- Assert(tok == WJB_BEGIN_ARRAY);
- Assert(tmp.val.array.nElems == 1 && tmp.val.array.rawScalar);
-
- tok = JsonbIteratorNext(, res, true);
- Assert(tok == WJB_ELEM);
- Assert(IsAJsonbScalar(res));
-
- tok = JsonbIteratorNext(, , true);
- Assert(tok == WJB_END_ARRAY);
-
- tok = JsonbIteratorNext(, , true);
- Assert(tok == WJB_DONE);
+ scalar = getIthJsonbValueFromContainer(jbc, 0, res);
+ Assert(scalar);
return true;
}
--
2.17.1
>From 7010862a80420f6407badbd814ef1378b0bca290 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov
Date: Thu, 21 Feb 2019 03:04:13 +0300
Subject: [PATCH v3 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 667f9d9563..c7f71408d5 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -747,6 +747,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();
+(void) JsonbToCString(, 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)
{
@@ -761,39 +802,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();
}
@@ -878,39 +889,9 @@ jsonb_array_element_text(PG_FUNCTION_ARGS)
}
v = getIthJsonbValueFromContainer(>root, element);
- if (v != NULL)
- {
- text *result =
Re: Optimization of some jsonb functions
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
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 69f41ab455..8dced4ef6c 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1873,9 +1873,7 @@ jsonb_object_agg_finalfn(PG_FUNCTION_ARGS)
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))
{
@@ -1884,25 +1882,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(, , true);
- Assert(tok == WJB_BEGIN_ARRAY);
- Assert(tmp.val.array.nElems == 1 && tmp.val.array.rawScalar);
-
- tok = JsonbIteratorNext(, res, true);
- Assert(tok == WJB_ELEM);
- Assert(IsAJsonbScalar(res));
-
- tok = JsonbIteratorNext(, , true);
- Assert(tok == WJB_END_ARRAY);
-
- tok = JsonbIteratorNext(, , true);
- Assert(tok == WJB_DONE);
+ scalar = getIthJsonbValueFromContainer(jbc, 0, res);
+ Assert(scalar);
return true;
}
diff --git a/src/backend/utils/adt/jsonb_op.c b/src/backend/utils/adt/jsonb_op.c
index a64206eeb1..82c4b0b2cb 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(>root,
JB_FOBJECT | JB_FARRAY,
- );
+ , );
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(>root,
JB_FOBJECT | JB_FARRAY,
- ) != NULL)
+ , ) != 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(>root,
JB_FOBJECT | JB_FARRAY,
- ) == NULL)
+ , ) == NULL)
PG_RETURN_BOOL(false);
}
diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index ac04c4a57b..05e1c18472 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,102 @@ 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,
+ JsonbValue *res)
+{
+ JEntry *children = container->children;
+ JsonbValue *result = res;
+ int count = JsonContainerSize(container);
+ /* Since this is an object, account for *Pairs* of
Re: Optimization of some jsonb functions
> >> 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
Re: Optimization of some jsonb functions
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 da...@pgmasters.net
Re: Optimization of some jsonb functions
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 Dunstanhttps://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Optimization of some jsonb functions
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 da...@pgmasters.net
Optimization of some jsonb functions
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, 300) 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
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
