Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rMd4B-005YF3-Sg for pgsql-hackers@arkaria.postgresql.org; Mon, 08 Jan 2024 00:00:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rMd49-000umt-B3 for pgsql-hackers@arkaria.postgresql.org; Mon, 08 Jan 2024 00:00:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rMd48-000uml-Tm for pgsql-hackers@lists.postgresql.org; Mon, 08 Jan 2024 00:00:29 +0000 Received: from mail-oi1-x233.google.com ([2607:f8b0:4864:20::233]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rMd44-000Nuw-Ie for pgsql-hackers@postgresql.org; Mon, 08 Jan 2024 00:00:28 +0000 Received: by mail-oi1-x233.google.com with SMTP id 5614622812f47-3bbd1fab171so1196221b6e.1 for ; Sun, 07 Jan 2024 16:00:24 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1704672022; x=1705276822; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=KxnOAVt1qcEHO4diiasYJ/Hgv6mnWoOFiqTZ/5yHSZY=; b=aCWOJndpB5/CjO79Q36S3FvfgTJygtHLbzLk6YmHSPu2UWmHi68lfVpEpV4ncqvrd8 lEPsKOuBQC9OFs3FEjU3+rxJ8fLUm4+T0llhniDuiFgOoxHssHttT2gINw1MhHxmK+8S SmLj1Q/b39PsVN8Cc9Hd2g92M/Y2lMtAWE40ajNzAglOsqNOIEy5COg3HXVffBUV+3Et k6Dbd1nT2Iqhb1QSsiP5kmlKPebj4hyh3Lb09z3W5MSRqiHnCcBZPvAsQxM76tpYZvPE RCeUZKWojrHkqzBPZA8F1O7aDihkqs/ffMo8yGcUPTJMK3MkYD8LMAi6Hu402XYaLTqB WfgQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1704672022; x=1705276822; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=KxnOAVt1qcEHO4diiasYJ/Hgv6mnWoOFiqTZ/5yHSZY=; b=PR1PclpF8lAmwIdHsPmHx3oFTkCfQVYYEc/qYK6zKr7qDDZXyKbikTtaVARPAeeBud Py8Uqe4YTqnO0NXByJ/Tv2eILoFO7wik7SdKq43mm/BNyFdW+vg+PV63oFQ47q9gCnBj S2IT8RQJ5qVo2H6Xg+95v/mrxDK3ZpjMf9fuJBo1S1hdVv7DWEuXpjKn0JE1NJ+Lu4q/ qvyiuYGkEwQevFqZU/ZLFMabJYEqVivXVU9X92sIWS1JWw0ZxCY+OnrcavEcECqGwvCc U+rhIoTpdsSjDgYV3GFqJgNKCG0VYQX5Vr6xY80j+tG5k9PDJh9CkeRQXghrFMcxt0nA wLxQ== X-Gm-Message-State: AOJu0YznZS/pbTneTVmhXg+MNN0/VzwRIohhfr71bVopWtWWoXsHyRDQ GWCSvTTZgOfg/I7mFRSGHcVeraUjAkEuPP4a5EyXJRCykxu+QgZc X-Google-Smtp-Source: AGHT+IGaUlVKkOFN/qD4GrOZ66zo8sgZJzmyV1zJLzktWFdORTCzyLjDHgJPSWrP7FbMn3YqP8GTrDta6fh28mathdI= X-Received: by 2002:a05:6808:23d1:b0:3bd:2cca:b5af with SMTP id bq17-20020a05680823d100b003bd2ccab5afmr1004244oib.110.1704672022338; Sun, 07 Jan 2024 16:00:22 -0800 (PST) MIME-Version: 1.0 References: <202312071051.cpxujwos4tlj@alvherre.pgsql> <20231208173014.2qycwc6kjqf6zlck@awork3.anarazel.de> In-Reply-To: From: jian he Date: Mon, 8 Jan 2024 08:00:00 +0800 Message-ID: Subject: Re: remaining sql/json patches To: Amit Langote Cc: Andres Freund , Alvaro Herrera , Erik Rijkers , Andrew Dunstan , PostgreSQL-development Content-Type: multipart/alternative; boundary="00000000000059a0f9060e63e2d6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000059a0f9060e63e2d6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Jan 6, 2024 at 8:44=E2=80=AFAM jian he 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=3Ddebug and also add a bunch of c_args. so the following test results have no c_args, just -Dbuildtype=3Drelease. 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=3D5 56.487 ms explain(costs off,analyze) select json_query(js, '$.a' returning int4range omit quotes) from test_scalar1 \watch count=3D5 27.272 ms explain(costs off,analyze) select json_value(js,'$.a' returning int4range) from test_scalar1 \watch count=3D5 22.775 ms explain(costs off,analyze) select (js->>'a')::int4range from test_scalar1 \watch count=3D5 17.520 ms explain(costs off,analyze) select trim(both '"' from jsonb_path_query_first(js,'$.a')::text)::int4range from test_scalar1 \watch count=3D5 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=3D5 20.197 ms explain(costs off,analyze) SELECT json_query(js, '$.a' returning numeric[]) from test_array1 \watch count=3D5 69.759 ms explain(costs off,analyze) SELECT replace(replace(js->>'a','[','{'),']','}')::numeric[] from test_array1 \watch count=3D5 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=3D5 18.770 ms explain(costs off,analyze) SELECT json_query(js,'$.a' returning numeric[] omit quotes) from test_array2 \watch count=3D5 46.373 ms explain(costs off,analyze) SELECT trim(both '"' from(jsonb_path_query(js,'$.a')::text))::numeric[] from test_array2 \watch count=3D5 71.901 ms explain(costs off,analyze) SELECT (js->>'a')::numeric[] from test_array2 \watch count=3D5 35.572 ms explain(costs off,analyze) SELECT trim(both '"' from (json_query(js,'$.a' returning text)))::numeric[] from test_array2 \watch count=3D5 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=3D5 18.723 ms explain(costs off,analyze) select json_query(js,'$.a' returning numeric) from test_scalar2 \watch count=3D5 18.234 ms explain(costs off,analyze) select json_value(js,'$.a' returning numeric) from test_scalar2 \watch count=3D5 11.667 ms explain(costs off,analyze) select jsonb_path_query_first(js,'$.a')::numeric from test_scalar2 \watch count=3D5 17.691 ms explain(costs off,analyze) select jsonb_path_query(js,'$.a')::numeric from test_scalar2 \watch count=3D5 31.596 ms explain(costs off,analyze) select (js->>'a')::numeric from test_scalar2 \watch count=3D5 13.887 ms ----------------------------return two scalar numeric explain(costs off,analyze) select (js->>'a')::numeric, (js->>'a')::numeric from test_scalar2 \watch count=3D5 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=3D5 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=3D5 17.915 ms --00000000000059a0f9060e63e2d6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Sat, Jan 6, 2024 at 8:44=E2=80=AFAM jian he <= ;jian.universality@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]&q= uot;}' FROM
> generate_series(1,1e5) g;
> create table test= _scalar2(js jsonb);
> insert into test_scalar2 select jsonb '{&qu= ot;a":12}' FROM generate_series(1,1e5) g;
> create table tes= t_array1(js jsonb);
> insert into test_array1 select jsonb '{&quo= t;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
> gener= ate_series(1,1e5) g;
>
same as before, v33 plus my 4 minor changes (d= ot no-cfbot in previous thread).
I realized my previous tests were wro= ng.<= /span>
because I use build type=3Ddebug and also add a bunch of c= _args.
so the following test results have no c_args, just =C2=A0-Dbuildt= ype=3Drelease.
I actually tested several times.

-------------= ---------------------------return a scalar int4range
explain(costs off,a= nalyze) SELECT item FROM test_scalar1, JSON_TABLE(js, '$.a' COLUMNS= (item int4range PATH '$' omit quotes)) \watch count=3D5
56.487 = ms

explain(costs off,analyze) select json_query(js, '$.a' re= turning int4range omit quotes) from test_scalar1 =C2=A0\watch count=3D5
= 27.272 ms

explain(costs off,analyze) select json_value(js,'$.a&#= 39; returning int4range) from test_scalar1 \watch count=3D5
22.775 ms
explain(costs off,analyze) select (js->>'a')::int4range = from test_scalar1 \watch count=3D5
17.520 ms

explain(costs off,an= alyze) select trim(both '"' from jsonb_path_query_first(js,= 9;$.a')::text)::int4range from test_scalar1 \watch count=3D5
36.946 = ms

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

explain(costs off,analyze) SELECT json_query(js, &= #39;$.a' returning numeric[]) from test_array1 \watch count=3D5
69.7= 59 ms

explain(costs off,analyze) SELECT replace(replace(js->>&= #39;a','[','{'),']','}')::numeric[] fro= m test_array1 \watch count=3D5
62.114 ms

------------------------= ----return a numeric array from jsonb string
explain(costs off,analyze) = SELECT item FROM test_array2, =C2=A0JSON_TABLE(js, '$.a' COLUMNS (i= tem numeric[] PATH '$' omit quotes)) \watch count=3D5
18.770 ms<= br>
explain(costs off,analyze) SELECT json_query(js,'$.a' return= ing numeric[] omit quotes) from test_array2 \watch count=3D5
46.373 ms
explain(costs off,analyze) SELECT trim(both '"' from(jso= nb_path_query(js,'$.a')::text))::numeric[] from test_array2 \watch = count=3D5
71.901 ms

explain(costs off,analyze) SELECT (js->>= ;'a')::numeric[] from test_array2 \watch count=3D5
35.572 ms
=
explain(costs off,analyze) SELECT trim(both '"' from (json= _query(js,'$.a' returning text)))::numeric[] =C2=A0from test_array2= \watch count=3D5
58.755 ms

----------------------------return a = scalar numeric
explain(costs off,analyze) SELECT item FROM test_scalar2= ,
JSON_TABLE(js, '$.a' COLUMNS (item numeric PATH '$' om= it quotes)) \watch count=3D5
18.723 ms

explain(costs off,analyze)= select json_query(js,'$.a' returning numeric) from test_scalar2 \w= atch count=3D5
18.234 ms

explain(costs off,analyze) select json_v= alue(js,'$.a' returning numeric) from test_scalar2 \watch count=3D5=
11.667 ms

explain(costs off,analyze) select jsonb_path_query_fir= st(js,'$.a')::numeric from test_scalar2 \watch count=3D5
17.691 = ms

explain(costs off,analyze) select jsonb_path_query(js,'$.a= 9;)::numeric from test_scalar2 \watch count=3D5
31.596 ms

explain= (costs off,analyze) select (js->>'a')::numeric from test_scal= ar2 \watch count=3D5
13.887 ms

----------------------------return= two scalar numeric
explain(costs off,analyze) select (js->>'= a')::numeric, (js->>'a')::numeric from test_scalar2 \watc= h count=3D5
22.201 ms

explain(costs off,analyze) SELECT item, ite= m1 FROM test_scalar2, JSON_TABLE(js, '$.a' COLUMNS (item numeric PA= TH '$' omit quotes,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 item1 numeric PATH = '$' omit quotes)) \watch count=3D5
19.108 ms

explain(cost= s off,analyze) select json_value(js,'$.a' returning numeric),
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 json_value(js,'$.a' returning numeric) = from test_scalar2 \watch count=3D5
17.915 ms
--00000000000059a0f9060e63e2d6--