From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: Alexander Korotkov <a.korotkov@postgrespro.ru>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Aleksander Alekseev <a.alekseev@postgrespro.ru>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Anthony Bykov <a.bykov@postgrespro.ru>
Subject: Re: Jsonb transform for pl/python
Date: Sat, 23 Jun 2018 02:44:29 +0300
Message-ID: <16593cd6-4200-e2ab-8d2e-58b456986d69@postgrespro.ru> (raw)
In-Reply-To: <CAPpHfdtw51jzqyF9GDjL=iCdh2gGALJQ8YygS7uaBxFBoVbdHQ@mail.gmail.com>
References: <20171025145100.3a19933f@anthony-24-g082ur>
<20180112140425.41174bd3@anthony-24-g082ur>
<151577178706.20515.9599911730425998398.pgcf@coridan.postgresql.org>
<b6edf1ab-5440-8798-de08-eb816baf58ba@2ndquadrant.com>
<f07b3955-e3e7-b410-b4fc-dae290774cf9@postgrespro.ru>
<84a3eaf4-32f6-d2dd-7139-e9c5dd3fec2d@2ndquadrant.com>
<c02881c2-26ca-dc53-137c-a1239aa9b8c0@2ndquadrant.com>
<6001af16-b242-2527-bc7e-84b8a959163b@postgrespro.ru>
<CAPpHfdtw51jzqyF9GDjL=iCdh2gGALJQ8YygS7uaBxFBoVbdHQ@mail.gmail.com>
Hi!
We have found another performance problem in this transform -- very slow
conversion via I/O from PostgreSQL numerics (which are used for the
representation of jsonb numbers) to Python Decimals.
Attached patch with fix.
We are simply trying first to convert numeric to int64 if is does not have
digits after the decimal point, and then construct Python Int instead of
Decimal. Standard Python json.loads() does the same for exact integers.
A special function numeric_to_exact_int64() was added to numeric.c. Existing
numeric_int8() can't be used here because it rounds input numeric.
Performance results (see the second attached file jsonb_plplython_tests.sql
for the function definitions):
- calculating the length of the passed jsonb object (input transformation):
py_jsonb_length_trans opt 2761,873 ms
py_jsonb_length_trans 10419,230 ms
py_jsonb_length_json 8691,201 ms
- returning integer arrays (output transformation):
py_jsonb_ret_int_array_trans opt 3284,810 ms
py_jsonb_ret_int_array_trans 4540,063 ms
py_jsonb_ret_int_array_raw 5100,793 ms
py_jsonb_ret_int_array_json 9887,821 ms
- returning float arrays (output transformation):
py_jsonb_ret_float_array_trans opt 5699,360 ms
py_jsonb_ret_float_array_trans 5735,854 ms
py_jsonb_ret_float_array_raw 6516,514 ms
py_jsonb_ret_float_array_json 10869,213 ms
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:0001-Optimize-numeric-conversions-in-jsonb_plpython.patch (text/x-patch, 3.2K) download
From ad090568f4eedf4cf0177f6ea5b7bb282f583bfb Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Thu, 21 Jun 2018 18:11:57 +0300
Subject: [PATCH] Optimize numeric conversions in jsonb_plpython
---
contrib/jsonb_plpython/jsonb_plpython.c | 29 +++++++++++++++++++++++++++--
src/backend/utils/adt/numeric.c | 21 +++++++++++++++++++++
src/include/utils/numeric.h | 1 +
3 files changed, 49 insertions(+), 2 deletions(-)
diff --git a/contrib/jsonb_plpython/jsonb_plpython.c b/contrib/jsonb_plpython/jsonb_plpython.cindex d6d6eeb..33ef15a 100644--- a/contrib/jsonb_plpython/jsonb_plpython.c+++ b/contrib/jsonb_plpython/jsonb_plpython.c@@ -110,6 +110,13 @@ PLyObject_FromJsonbValue(JsonbValue *jsonbValue)
{
Datum num;
char *str;
+ int64 intval;++ if (numeric_to_exact_int64(jsonbValue->val.numeric, &intval))+#ifndef HAVE_LONG_INT_64+ if ((long) intval == intval)+#endif+ return PyInt_FromLong((long) intval);
num = NumericGetDatum(jsonbValue->val.numeric);
str = DatumGetCString(DirectFunctionCall1(numeric_out, num));
@@ -324,7 +331,26 @@ static JsonbValue *
PLyNumber_ToJsonbValue(PyObject *obj, JsonbValue *jbvNum)
{
Numeric num;
- char *str = PLyObject_AsString(obj);+ char *str;++ jbvNum->type = jbvNumeric;++ if (PyInt_Check(obj))+ {+ long val = PyInt_AsLong(obj);++ if (val != -1 || !PyErr_Occurred())+ {+ jbvNum->val.numeric =+ DatumGetNumeric(DirectFunctionCall1(int8_numeric,+ Int64GetDatum((int64) val)));+ return jbvNum;+ }++ PyErr_Clear();+ }++ str = PLyObject_AsString(obj);
PG_TRY();
{
@@ -356,7 +382,6 @@ PLyNumber_ToJsonbValue(PyObject *obj, JsonbValue *jbvNum)
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
(errmsg("cannot convert NaN to jsonb"))));
- jbvNum->type = jbvNumeric;
jbvNum->val.numeric = num;
return jbvNum;
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.cindex 82a1429..eefaa6d 100644--- a/src/backend/utils/adt/numeric.c+++ b/src/backend/utils/adt/numeric.c@@ -3348,6 +3348,27 @@ numeric_float4(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(result);
}
+/*+ * numeric_get_int64() -+ *+ * Try to convert numeric to int64 if it is an exact integer (i.e. it does+ * not have digits after the decimal point). Return true if okay.+ */+bool+numeric_to_exact_int64(Numeric num, int64 *result)+{+ NumericVar var;++ if (NUMERIC_IS_NAN(num))+ return false;++ if (NUMERIC_DSCALE(num) != 0)+ return false; /* digits after the decimal point are not allowed */++ init_var_from_num(num, &var);++ return numericvar_to_int64(&var, result);+}
/* ----------------------------------------------------------------------
*
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.hindex cd8da8b..ab49c0e 100644--- a/src/include/utils/numeric.h+++ b/src/include/utils/numeric.h@@ -60,5 +60,6 @@ extern bool numeric_is_nan(Numeric num);
int32 numeric_maximum_size(int32 typmod);
extern char *numeric_out_sci(Numeric num, int scale);
extern char *numeric_normalize(Numeric num);
+extern bool numeric_to_exact_int64(Numeric value, int64 *out);
#endif /* _PG_NUMERIC_H_ */
--
2.7.4
jsonb_plpython_tests.sql (application/sql, 2.2K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: n.gluhov@postgrespro.ru, a.korotkov@postgrespro.ru, peter.eisentraut@2ndquadrant.com, a.alekseev@postgrespro.ru, pgsql-hackers@lists.postgresql.org, a.bykov@postgrespro.ru
Subject: Re: Jsonb transform for pl/python
In-Reply-To: <16593cd6-4200-e2ab-8d2e-58b456986d69@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox