Re: Jsonb transform for pl/python

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