Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fWVkk-0008E3-Os for pgsql-hackers@arkaria.postgresql.org; Fri, 22 Jun 2018 23:46:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fWVkg-0000hB-8u for pgsql-hackers@arkaria.postgresql.org; Fri, 22 Jun 2018 23:46:02 +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_SHA384:256) (Exim 4.89) (envelope-from ) id 1fWVkf-0000h3-Uf for pgsql-hackers@lists.postgresql.org; Fri, 22 Jun 2018 23:46:02 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fWVkX-00061x-A5 for pgsql-hackers@lists.postgresql.org; Fri, 22 Jun 2018 23:46:01 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 7AE5F21C0935; Sat, 23 Jun 2018 02:45:51 +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 05ED821C063A; Sat, 23 Jun 2018 02:45:50 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1529711151; bh=p20lJTgIXaz/PjbdQxCuABn32AkSHQJl26AosfdBMko=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=EeFeouBSYO+e+SU9OzxDma1QBH1Ts12dW/zEae1c4rx7Cueu3Zpdam8ldVcyY43CH QZIHpxOT9ThWmPwN3pZbubjUkA/UO6Nn59jZ7oEMZHkl4jX7RYhuPxms6rbaWmH4Pr By+uO/+oFzi9Di61X/32SWWevBdm8A11pb5c07MQ= Subject: Re: Jsonb transform for pl/python To: Alexander Korotkov Cc: Peter Eisentraut , Aleksander Alekseev , PostgreSQL Hackers , Anthony Bykov References: <20171025145100.3a19933f@anthony-24-g082ur> <20180112140425.41174bd3@anthony-24-g082ur> <151577178706.20515.9599911730425998398.pgcf@coridan.postgresql.org> <84a3eaf4-32f6-d2dd-7139-e9c5dd3fec2d@2ndquadrant.com> <6001af16-b242-2527-bc7e-84b8a959163b@postgrespro.ru> From: Nikita Glukhov Organization: Postgres Professional Message-ID: <16593cd6-4200-e2ab-8d2e-58b456986d69@postgrespro.ru> Date: Sat, 23 Jun 2018 02:44:29 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------E5B09597E5167500E51A5B68" 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. --------------E5B09597E5167500E51A5B68 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------E5B09597E5167500E51A5B68 Content-Type: text/x-patch; name="0001-Optimize-numeric-conversions-in-jsonb_plpython.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0001-Optimize-numeric-conversions-in-jsonb_plpython.patch" From ad090568f4eedf4cf0177f6ea5b7bb282f583bfb Mon Sep 17 00:00:00 2001 From: Nikita Glukhov 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.c index 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.c index 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.h index 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 --------------E5B09597E5167500E51A5B68 Content-Type: application/sql; name="jsonb_plpython_tests.sql" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="jsonb_plpython_tests.sql" LS0ganNvbmIgZGVjb2RpbmcvdHJhbnNmb3JtYXRpb24KQ1JFQVRFIE9SIFJFUExBQ0UgRlVO Q1RJT04gcHlfanNvbmJfbGVuZ3RoX2pzb24odmFsX3N0ciBqc29uYikgUkVUVVJOUyBpbnQK TEFOR1VBR0UgcGxweXRob251CkFTICQkCiAgaW1wb3J0IGpzb24KICB2YWwgPSBqc29uLmxv YWRzKHZhbF9zdHIpCiAgcmV0dXJuIGxlbih2YWwpCiQkOwoKQ1JFQVRFIE9SIFJFUExBQ0Ug RlVOQ1RJT04gcHlfanNvbmJfbGVuZ3RoX3RyYW5zKHZhbCBqc29uYikgUkVUVVJOUyBpbnQK TEFOR1VBR0UgcGxweXRob251ClRSQU5TRk9STSBGT1IgVFlQRSBqc29uYgpBUyAkJAogIHJl dHVybiBsZW4odmFsKQokJDsKCi0tIHJldHVybmluZyBudW1lcmljIGFycmF5cwpDUkVBVEUg T1IgUkVQTEFDRSBGVU5DVElPTiBweV9qc29uYl9yZXRfaW50X2FycmF5X3RyYW5zKCkgUkVU VVJOUyBqc29uYgpMQU5HVUFHRSBwbHB5dGhvbnUKVFJBTlNGT1JNIEZPUiBUWVBFIGpzb25i CkFTICQkCiAgcmV0dXJuIFswLDEsMiwzLDQsNSw2LDcsOCw5XQokJDsKCkNSRUFURSBPUiBS RVBMQUNFIEZVTkNUSU9OIHB5X2pzb25iX3JldF9mbG9hdF9hcnJheV90cmFucygpIFJFVFVS TlMganNvbmIKTEFOR1VBR0UgcGxweXRob251ClRSQU5TRk9STSBGT1IgVFlQRSBqc29uYgpB UyAkJAogIHJldHVybiBbMC4wLDEuMCwyLjAsMy4wLDQuMCw1LjAsNi4wLDcuMCw4LjAsOS4w XQokJDsKCkNSRUFURSBPUiBSRVBMQUNFIEZVTkNUSU9OIHB5X2pzb25iX3JldF9pbnRfYXJy YXlfanNvbigpIFJFVFVSTlMganNvbmIKTEFOR1VBR0UgcGxweXRob251CkFTICQkCiAgaW1w b3J0IGpzb24KICByZXR1cm4ganNvbi5kdW1wcyhbMCwxLDIsMyw0LDUsNiw3LDgsOV0pCiQk OwoKQ1JFQVRFIE9SIFJFUExBQ0UgRlVOQ1RJT04gcHlfanNvbmJfcmV0X2ludF9hcnJheV9y YXcoKSBSRVRVUk5TIGpzb25iCkxBTkdVQUdFIHBscHl0aG9udQpBUyAkJAogIHJldHVybiBb MCwxLDIsMyw0LDUsNiw3LDgsOV0KJCQ7CgpDUkVBVEUgT1IgUkVQTEFDRSBGVU5DVElPTiBw eV9qc29uYl9yZXRfZmxvYXRfYXJyYXlfanNvbigpIFJFVFVSTlMganNvbmIKTEFOR1VBR0Ug cGxweXRob251CkFTICQkCiAgaW1wb3J0IGpzb24KICByZXR1cm4ganNvbi5kdW1wcyhbMC4w LDEuMCwyLjAsMy4wLDQuMCw1LjAsNi4wLDcuMCw4LjAsOS4wXSkKJCQ7CgpDUkVBVEUgT1Ig UkVQTEFDRSBGVU5DVElPTiBweV9qc29uYl9yZXRfZmxvYXRfYXJyYXlfcmF3KCkgUkVUVVJO UyBqc29uYgpMQU5HVUFHRSBwbHB5dGhvbnUKQVMgJCQKICByZXR1cm4gWzAuMCwxLjAsMi4w LDMuMCw0LjAsNS4wLDYuMCw3LjAsOC4wLDkuMF0KJCQ7CgotLSByZXR1cm5pbmcgb2JqZWN0 cwpDUkVBVEUgT1IgUkVQTEFDRSBGVU5DVElPTiBweV9qc29uYl9yZXRfb2JqX3RyYW5zKCkg UkVUVVJOUyBqc29uYgpMQU5HVUFHRSBwbHB5dGhvbnUKQVMgJCQKICBpbXBvcnQganNvbgog IHJldHVybiBqc29uLmR1bXBzKHsiYWFhIjogMTIzLCAiYmJiIjogNDU2fSkKJCQ7CgoKQ1JF QVRFIE9SIFJFUExBQ0UgRlVOQ1RJT04gcHlfanNvbmJfcmV0X29ial90cmFucygpIFJFVFVS TlMganNvbmIKTEFOR1VBR0UgcGxweXRob251ClRSQU5TRk9STSBGT1IgVFlQRSBqc29uYgpB UyAkJAogIHJldHVybiB7ImFhYSI6IDEyMywgImJiYiI6IDQ1Nn0KJCQ7CgoKLS0gVGVzdHMK RE8gJCQKQkVHSU4KICBGT1IgaSBJTiAxLi4xMDAwMDAwIExPT1AKICAgIFBFUkZPUk0gcHlf anNvbmJfbGVuZ3RoX3RyYW5zKCd7ImFhYSI6IDEyMywgImJiYiI6IDQ1Nn0nOjpqc29uYik7 CiAgRU5EIExPT1A7CkVORAokJDsKCkRPICQkCkJFR0lOCiAgRk9SIGkgSU4gMS4uMTAwMDAw MCBMT09QCiAgICBQRVJGT1JNIHB5X2pzb25iX2xlbmd0aF9qc29uKCd7ImFhYSI6IDEyMywg ImJiYiI6IDQ1Nn0nOjpqc29uYik7CiAgRU5EIExPT1A7CkVORAokJDsKCkRPICQkCkJFR0lO CiAgRk9SIGkgSU4gMS4uMTAwMDAwMCBMT09QCiAgICBQRVJGT1JNIHB5X2pzb25iX3JldF9p bnRfYXJyYXlfdHJhbnMoKTsKICBFTkQgTE9PUDsKRU5ECiQkOwoKRE8gJCQKQkVHSU4KICBG T1IgaSBJTiAxLi4xMDAwMDAwIExPT1AKICAgIFBFUkZPUk0gcHlfanNvbmJfcmV0X2ludF9h cnJheV9yYXcoKTsKICBFTkQgTE9PUDsKRU5ECiQkOwoKRE8gJCQKQkVHSU4KICBGT1IgaSBJ TiAxLi4xMDAwMDAwIExPT1AKICAgIFBFUkZPUk0gcHlfanNvbmJfcmV0X2ludF9hcnJheV9q c29uKCk7CiAgRU5EIExPT1A7CkVORAokJDsK --------------E5B09597E5167500E51A5B68--