public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Amit Langote <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: Erik Rijkers <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: remaining sql/json patches
Date: Mon, 8 Jan 2024 08:00:00 +0800
Message-ID: <CACJufxHB4840KXKfTV=cuNQE6VMyk40Pe-48m+J_L_34G4By_w@mail.gmail.com> (raw)
In-Reply-To: <CACJufxHstSGwRLqfea7NWgUO2Hzv=0OCvjFNyRndb0-UO71A3w@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CA+HiwqFKhM=6fN3Np61uYGCsOGdBKk1K1MbCQ0Q6LMNWGShQ0A@mail.gmail.com>
<[email protected]>
<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>
<CACJufxHstSGwRLqfea7NWgUO2Hzv=0OCvjFNyRndb0-UO71A3w@mail.gmail.com>
On Sat, Jan 6, 2024 at 8:44 AM jian he <[email protected]> wrote:
>
> 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;
>
same as before, v33 plus my 4 minor changes (dot no-cfbot in previous
thread).
I realized my previous tests were wrong.
because I use build type=debug and also add a bunch of c_args.
so the following test results have no c_args, just -Dbuildtype=release.
I actually tested several times.
----------------------------------------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
56.487 ms
explain(costs off,analyze) select json_query(js, '$.a' returning int4range
omit quotes) from test_scalar1 \watch count=5
27.272 ms
explain(costs off,analyze) select json_value(js,'$.a' returning int4range)
from test_scalar1 \watch count=5
22.775 ms
explain(costs off,analyze) select (js->>'a')::int4range from test_scalar1
\watch count=5
17.520 ms
explain(costs off,analyze) select trim(both '"' from
jsonb_path_query_first(js,'$.a')::text)::int4range from test_scalar1 \watch
count=5
36.946 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
20.197 ms
explain(costs off,analyze) SELECT json_query(js, '$.a' returning numeric[])
from test_array1 \watch count=5
69.759 ms
explain(costs off,analyze) SELECT
replace(replace(js->>'a','[','{'),']','}')::numeric[] from test_array1
\watch count=5
62.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
18.770 ms
explain(costs off,analyze) SELECT json_query(js,'$.a' returning numeric[]
omit quotes) from test_array2 \watch count=5
46.373 ms
explain(costs off,analyze) SELECT trim(both '"'
from(jsonb_path_query(js,'$.a')::text))::numeric[] from test_array2 \watch
count=5
71.901 ms
explain(costs off,analyze) SELECT (js->>'a')::numeric[] from test_array2
\watch count=5
35.572 ms
explain(costs off,analyze) SELECT trim(both '"' from (json_query(js,'$.a'
returning text)))::numeric[] from test_array2 \watch count=5
58.755 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
18.723 ms
explain(costs off,analyze) select json_query(js,'$.a' returning numeric)
from test_scalar2 \watch count=5
18.234 ms
explain(costs off,analyze) select json_value(js,'$.a' returning numeric)
from test_scalar2 \watch count=5
11.667 ms
explain(costs off,analyze) select jsonb_path_query_first(js,'$.a')::numeric
from test_scalar2 \watch count=5
17.691 ms
explain(costs off,analyze) select jsonb_path_query(js,'$.a')::numeric from
test_scalar2 \watch count=5
31.596 ms
explain(costs off,analyze) select (js->>'a')::numeric from test_scalar2
\watch count=5
13.887 ms
----------------------------return two scalar numeric
explain(costs off,analyze) select (js->>'a')::numeric, (js->>'a')::numeric
from test_scalar2 \watch count=5
22.201 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
19.108 ms
explain(costs off,analyze) select json_value(js,'$.a' returning numeric),
json_value(js,'$.a' returning numeric) from test_scalar2 \watch
count=5
17.915 ms
view thread (9+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: remaining sql/json patches
In-Reply-To: <CACJufxHB4840KXKfTV=cuNQE6VMyk40Pe-48m+J_L_34G4By_w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox