Re: Jsonb transform for pl/python - Mailing list pgsql-hackers

From Nikita Glukhov
Subject Re: Jsonb transform for pl/python
Date
Msg-id 16593cd6-4200-e2ab-8d2e-58b456986d69@postgrespro.ru
Whole thread Raw
In response to Re: Jsonb transform for pl/python  (Alexander Korotkov <a.korotkov@postgrespro.ru>)
Responses Re: Jsonb transform for pl/python
List pgsql-hackers
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


Attachment

pgsql-hackers by date:

Previous
From: Nico Williams
Date:
Subject: Re: Using JSONB directly from application
Next
From: Michael Paquier
Date:
Subject: Re: Fix some error handling for read() and errno