Re: [HACKERS] [PATCH] Generic type subscripting

> On Thu, Oct 31, 2019 at 05:35:28AM +0300, Nikita Glukhov wrote:
> Hi. I added new 5th patch to this patch set.

Thank you!

> Performance was compared on 4 tables with 10M rows:
>
> -- [ i ]
> CREATE TABLE arr_1 AS
> SELECT jsonb_build_array(i)::jsonb js
> FROM generate_series(1, 10000000) i;
>
> -- { "a": i }
> CREATE TABLE obj_1 AS
> SELECT jsonb_build_object('a', i) js
> FROM generate_series(1, 10000000) i;
>
> -- [[[[[[[[[[ i ]]]]]]]]]]
> CREATE TABLE arr_10 AS
> SELECT (repeat('[', 10) || i || repeat(']', 10))::jsonb js
> FROM generate_series(1, 10000000) i;
>
> -- {"a": {"a": ... {"a": {"a": i } } ... } }
> CREATE TABLE obj_10 AS
> SELECT (repeat('{"a":', 10) || i || repeat('}', 10))::jsonb js
> FROM generate_series(1, 10000000) i;
>
> Queries were like "SELECT FROM table WHERE expression IS [NOT] NULL".
>
> Compared previous v27 version (4 patches) with v28 version (5 patches).
> New patch #5 contains one small but important optimization -- elimination of
> unnecessary palloc() in getIthJsonbValueFromContainer() and jsonb_get_element().
> It should be posted separately, but for simplicity I included it the patch now.
> For the correctness of comparison, it was evaluated separately on top of v27
> (v27opt).
>
>
> Table | Expression | Query time, ms
> | | v27 | v27opt| v28
> --------+--------------------------------------+-------+-------+-------
> arr_1 | js->0 | 1811 | 1809 | 1813
> arr_1 | js[0] | 2273 | 2294 | 2028
> arr_1 | js['0'] | 2276 | 2286 | 2339
> arr_1 | js->1 | 808 | 844 | 809
> arr_1 | js[1] | 1180 | 1187 | 1008
> obj_1 | js->'a' | 1941 | 1935 | 1939
> obj_1 | js['a'] | 2079 | 2083 | 2102
> obj_1 | js->'b' | 917 | 915 | 902
> obj_1 | js['b'] | 960 | 961 | 1059
> |
> arr_10 | js->0->0 ... ->0->0 | 4530 | 4068 | 4052
> arr_10 | js[0][0] ... [0][0] | 6197 | 5513 | 3766
> arr_10 | js['0']['0'] ... ['0']['0'] | 6202 | 5519 | 5983
> arr_10 | js #> '{0,0,0,0,0,0,0,0,0,0}' | 6412 | 5850 | 5835
> arr_10 | js #>> '{0,0,0,0,0,0,0,0,0,0}' | 5904 | 5181 | 5192
>
> obj_10 | js->'a'->'a' ... ->'a'->'a' | 4970 | 4717 | 4704
> obj_10 | js['a']['a'] ... ['a']['a'] | 4331 | 3698 | 4032
> obj_10 | js #> '{a,a,a,a,a,a,a,a,a,a}' | 4570 | 3941 | 3949
> obj_10 | js #>> '{a,a,a,a,a,a,a,a,a,a}' | 4055 | 3395 | 3392
>
> As it can be seen, array access time reduced from 10% in single subscripts
> to 40% in 10-subscript chains, and subscripting event started to overtake
> chained "->" operators. But there is 10% slowdown of object key access that
> needs further investigation. The elimination of unnecessary palloc()s also
> gives good results.

I've tested 5th patch a bit and can confirm numbers in the last column
for v28 (I've got similar proportions). Let's see what is the reason for 10%
of slowdown for object key access.

> I had to write new assignment logic reusing only some parts of setPath(),
> because the loop in setPath() should be broken on every level. During this
> process, I decided to implement assignment behavior similar to PostgreSQL's
> array behavior and added two new features:
> - creation of jsonb arrays/objects container from NULL values
> - appending/prepending array elements on the specified position, gaps filled
> with nulls (JavaScript has similar behavior)

What is the reason for the last one?