On Sat, Jan 6, 2024 at 8:44 AM jian he <jian.universal...@gmail.com> 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