MIME-Version: 1.0
References: <d9b19c25-7ea8-1525-15c8-9f756b8e5bad@xs4all.nl>
 <202312071051.cpxujwos4tlj@alvherre.pgsql> <CA+HiwqFKhM=6fN3Np61uYGCsOGdBKk1K1MbCQ0Q6LMNWGShQ0A@mail.gmail.com>
 <20231208173014.2qycwc6kjqf6zlck@awork3.anarazel.de> <CA+HiwqFm4X39O6aGf6E-tSaSrd+p-Sg_S450fHZkspHdneO5qQ@mail.gmail.com>
 <CA+HiwqFTBYa+BipE3K=4--OC9f0-bK_n8Kh78Ez4fwrT78v=zQ@mail.gmail.com>
 <CACJufxEDfV97Kox67vYgkS5iuSd47j==Qt9n=TLRedDjYDJgsg@mail.gmail.com> <CACJufxEY+yGb-sEMryoHesx++gEmWpXCYKti+ctjF1WAKmPh+g@mail.gmail.com>
In-Reply-To: <CACJufxEY+yGb-sEMryoHesx++gEmWpXCYKti+ctjF1WAKmPh+g@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Sat, 6 Jan 2024 08:44:00 +0800
Message-ID: <CACJufxHstSGwRLqfea7NWgUO2Hzv=0OCvjFNyRndb0-UO71A3w@mail.gmail.com>
Subject: Re: remaining sql/json patches
To: Amit Langote <amitlangote09@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, Alvaro Herrera <alvherre@alvh.no-ip.org>, 
	Erik Rijkers <er@xs4all.nl>, Andrew Dunstan <andrew@dunslane.net>, 
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CACJufxHstSGwRLqfea7NWgUO2Hzv%3D0OCvjFNyRndb0-UO71A3w%40mail.gmail.com>
Precedence: bulk

some tests after applying V33 and my small changes.
setup:
create table test_scalar1(js jsonb);
insert into test_scalar1 select jsonb '{"a":"[12,13]"}' FROM
generate_series(1,1e5) g;
create table test_scalar2(js jsonb);
insert into test_scalar2 select jsonb '{"a":12}' FROM generate_series(1,1e5) g;
create table test_array1(js jsonb);
insert into test_array1 select jsonb '{"a":[1,2,3,4,5]}' FROM
generate_series(1,1e5) g;
create table test_array2(js jsonb);
insert into test_array2 select jsonb '{"a": "{1,2,3,4,5}"}' FROM
generate_series(1,1e5) g;

tests:
----------------------------------------return a scalar int4range
explain(costs off,analyze) SELECT item FROM test_scalar1,
JSON_TABLE(js, '$.a' COLUMNS (item int4range PATH '$' omit quotes))
\watch count=5
237.753 ms

explain(costs off,analyze) select json_query(js, '$.a' returning
int4range omit quotes) from test_scalar1  \watch count=5
462.379 ms

explain(costs off,analyze) select json_value(js,'$.a' returning
int4range) from test_scalar1 \watch count=5
362.148 ms

explain(costs off,analyze) select (js->>'a')::int4range from
test_scalar1 \watch count=5
301.089 ms

explain(costs off,analyze) select trim(both '"' from
jsonb_path_query_first(js,'$.a')::text)::int4range from test_scalar1
\watch count=5
643.337 ms

----------------------------return a numeric array from jsonb array.
explain(costs off,analyze) SELECT item FROM test_array1,
JSON_TABLE(js, '$.a' COLUMNS (item numeric[] PATH '$')) \watch count=5
727.807 ms

explain(costs off,analyze) SELECT json_query(js, '$.a' returning
numeric[]) from test_array1 \watch count=5
2995.909 ms

explain(costs off,analyze) SELECT
replace(replace(js->>'a','[','{'),']','}')::numeric[] from test_array1
\watch count=5
2990.114 ms

----------------------------return a numeric array from jsonb string
explain(costs off,analyze) SELECT item FROM test_array2,
JSON_TABLE(js, '$.a' COLUMNS (item numeric[] PATH '$' omit quotes))
\watch count=5
237.863 ms

explain(costs off,analyze) SELECT json_query(js,'$.a' returning
numeric[] omit quotes) from test_array2 \watch count=5
893.888 ms

explain(costs off,analyze) SELECT trim(both '"'
from(jsonb_path_query(js,'$.a')::text))::numeric[] from test_array2
\watch count=5
1329.713 ms

explain(costs off,analyze) SELECT (js->>'a')::numeric[] from
test_array2 \watch count=5
740.645 ms

explain(costs off,analyze) SELECT trim(both '"' from
(json_query(js,'$.a' returning text)))::numeric[]  from test_array2
\watch count=5
1085.230 ms
----------------------------return a scalar numeric
explain(costs off,analyze) SELECT item FROM test_scalar2,
JSON_TABLE(js, '$.a' COLUMNS (item numeric PATH '$' omit quotes)) \watch count=5
238.036 ms

explain(costs off,analyze) select json_query(js,'$.a' returning
numeric) from test_scalar2 \watch count=5
300.862 ms

explain(costs off,analyze) select json_value(js,'$.a' returning
numeric) from test_scalar2 \watch count=5
160.035 ms

explain(costs off,analyze) select
jsonb_path_query_first(js,'$.a')::numeric from test_scalar2 \watch
count=5
294.666 ms

explain(costs off,analyze) select jsonb_path_query(js,'$.a')::numeric
from test_scalar2 \watch count=5
547.130 ms

explain(costs off,analyze) select (js->>'a')::numeric from
test_scalar2 \watch count=5
243.652 ms

explain(costs off,analyze) select (js->>'a')::numeric,
(js->>'a')::numeric from test_scalar2 \watch count=5
403.183 ms

explain(costs off,analyze) select json_value(js,'$.a' returning numeric),
        json_value(js,'$.a' returning numeric) from test_scalar2 \watch count=5
246.405 ms

explain(costs off,analyze) select json_query(js,'$.a' returning numeric),
        json_query(js,'$.a' returning numeric) from test_scalar2 \watch count=5
520.754 ms

explain(costs off,analyze) SELECT item, item1 FROM test_scalar2,
JSON_TABLE(js, '$.a' COLUMNS (item numeric PATH '$' omit quotes,
                            item1 numeric PATH '$' omit quotes)) \watch count=5
242.586 ms
---------------------------------
overall, json_value is faster than json_query. but json_value can not
deal with arrays in some cases.
but as you can see, in some cases, json_value and json_query are not
as fast as our current implementation.

Here I only test simple nested levels. if you extra multiple values
from jsonb to sql type, then json_table is faster.
In almost all cases, json_table is faster.

json_table is actually called json_value_op, json_query_op under the hood.
Without json_value and json_query related code, json_table cannot be
implemented.