From: Dmitry Dolgov <9erthalion6@gmail.com>
To: Nikita Glukhov <n.gluhov@postgrespro.ru>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: David Fetter <david@fetter.org>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Oleksandr Shulgin <oleksandr.shulgin@zalando.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: Oleg Bartunov <obartunov@gmail.com>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: David Steele <david@pgmasters.net>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] [PATCH] Generic type subscripting
Date: Sun, 10 Nov 2019 13:32:08 +0100
Message-ID: <20191110123208.r4kn5k34wsey5zik@localhost> (raw)
In-Reply-To: <099309f0-4b41-2c78-aac8-0835fe9a9ee7@postgrespro.ru>
References: <CA+q6zcVFue814UirpoMhTP-EKMSJpKULWziYCRyhbpZ0=iztLw@mail.gmail.com>
<20190925202205.GA23893@alvherre.pgsql>
<CA+q6zcXrAL+EDW4CDnjVzycTHPce9uKwC77PvmF4Xd_FY6iK=w@mail.gmail.com>
<099309f0-4b41-2c78-aac8-0835fe9a9ee7@postgrespro.ru>
> 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?
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: 9erthalion6@gmail.com, n.gluhov@postgrespro.ru, alvherre@2ndquadrant.com, david@fetter.org, thomas.munro@gmail.com, pavel.stehule@gmail.com, thomas.munro@enterprisedb.com, oleksandr.shulgin@zalando.de, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, andres@anarazel.de, michael.paquier@gmail.com, obartunov@gmail.com, peter.eisentraut@2ndquadrant.com, david@pgmasters.net
Subject: Re: [HACKERS] [PATCH] Generic type subscripting
In-Reply-To: <20191110123208.r4kn5k34wsey5zik@localhost>
* 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