MIME-Version: 1.0
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
 <CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com>
In-Reply-To: <CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com>
From: Andy Fan <zhihui.fan1213@gmail.com>
Date: Thu, 3 Aug 2023 08:50:47 +0800
Message-ID: <CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com>
Subject: Re: Extract numeric filed in JSONB more effectively
To: jian he <jian.universality@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="0000000000006e55830601fa2c03"
Archived-At: <https://www.postgresql.org/message-id/CAKU4AWrap1zpYqunJwWTN%3DCdP7E8e0U4mYmwn7hvTW3ERuENVg%40mail.gmail.com>
Precedence: bulk

--0000000000006e55830601fa2c03
Content-Type: multipart/alternative; boundary="0000000000006e55810601fa2c01"

--0000000000006e55810601fa2c01
Content-Type: text/plain; charset="UTF-8"

Hi Jian:


> return PointerGetDatum(v->val.numeric);
> should be something like
> PG_RETURN_NUMERIC(v->val.numeric);
> ?
>

Thanks for this reminder, a new patch is attached.  and commitfest
entry is added as well[1]. For recording purposes,  I compared the
new operator with all the existing operators.

select 1 from tb where (a->'a')::numeric = 2;   30.56ms
select 1 from tb where (a->>'a')::numeric = 2; 29.43ms
select 1 from tb where (a@->'a') = 2;              14.80ms

[1] https://commitfest.postgresql.org/44/4476/

-- 
Best Regards
Andy Fan

--0000000000006e55810601fa2c01
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_quote"><div>Hi Jian:</div><div>=C2=A0<=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex">
return PointerGetDatum(v-&gt;val.numeric);<br>
should be something like<br>
PG_RETURN_NUMERIC(v-&gt;val.numeric);<br>
?<br>
</blockquote></div><br clear=3D"all"><div>Thanks for this reminder, a new p=
atch is attached.=C2=A0 and commitfest</div><div>entry is added as well[1].=
 For recording purposes,=C2=A0 I compared the</div><div>new operator with a=
ll the existing operators.</div><div><br></div>select 1 from tb where (a-&g=
t;&#39;a&#39;)::numeric =3D 2;=C2=A0 =C2=A030.56ms<br>select 1 from tb wher=
e (a-&gt;&gt;&#39;a&#39;)::numeric =3D 2; 29.43ms<br>select 1 from tb where=
 (a@-&gt;&#39;a&#39;) =3D 2;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 14.80ms=C2=A0<div><br></div><div>[1] <a href=3D"https://commitfest.post=
gresql.org/44/4476/">https://commitfest.postgresql.org/44/4476/</a><br></di=
v><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div =
dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Best Regards<div>And=
y Fan</div></div></div></div>

--0000000000006e55810601fa2c01--
--0000000000006e55830601fa2c03
Content-Type: application/octet-stream; 
	name="v2-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch"
Content-Disposition: attachment; 
	filename="v2-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_lkufwmy70>
X-Attachment-Id: f_lkufwmy70

RnJvbSAxNDE1ZTA5ZDZjYTg2MGQ0NjY5MzkyMjllNmQ2YTkwMTJiZjJiYmNmIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBBbmR5IEZhbiA8emhpaHVpLmZhbjEyMTNAZ21haWwuY29tPgpE
YXRlOiBUdWUsIDEgQXVnIDIwMjMgMTA6Mzg6MjkgKzA4MDAKU3ViamVjdDogW1BBVENIIHYxXSBB
ZGQganNvbmIgb3BlcmF0b3IgdG8gcmV0dXJuIGEgbnVtZXJpYyBkaXJlY3RseS4KClRoZSBiaW5h
cnkgZm9ybWF0IG9mIG51bWVyaWMgaW4gSk9TTkIgaXMgY29tcGF0aWJsZSB3aXRoIHRoZSBudW1l
cmljCmluIFNRTCwgc28gd2UgY2FuIGdldCB0aGUgbnVtZXJpYyBtb3JlIGVmZmVjdGl2ZWx5Lgot
LS0KIGRvYy9zcmMvc2dtbC9mdW5jLnNnbWwgICAgICAgICAgICAgICAgICAgICAgIHwgMTQgKysr
KysrKysrKysKIHNyYy9iYWNrZW5kL3V0aWxzL2FkdC9qc29uZnVuY3MuYyAgICAgICAgICAgIHwg
MjYgKysrKysrKysrKysrKysrKysrKysKIHNyYy9pbmNsdWRlL2NhdGFsb2cvY2F0dmVyc2lvbi5o
ICAgICAgICAgICAgIHwgIDIgKy0KIHNyYy9pbmNsdWRlL2NhdGFsb2cvcGdfb3BlcmF0b3IuZGF0
ICAgICAgICAgIHwgIDMgKysrCiBzcmMvaW5jbHVkZS9jYXRhbG9nL3BnX3Byb2MuZGF0ICAgICAg
ICAgICAgICB8ICA0ICsrKwogc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9qc29uYl9qc29ucGF0
aC5vdXQgfCAyMCArKysrKysrKysrKysrKysKIHNyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2pzb25iX2pz
b25wYXRoLnNxbCAgICAgIHwgIDQgKysrCiA3IGZpbGVzIGNoYW5nZWQsIDcyIGluc2VydGlvbnMo
KyksIDEgZGVsZXRpb24oLSkKCmRpZmYgLS1naXQgYS9kb2Mvc3JjL3NnbWwvZnVuYy5zZ21sIGIv
ZG9jL3NyYy9zZ21sL2Z1bmMuc2dtbAppbmRleCBiZTJmNTRjOTE0MS4uN2Y0Yjg5NzA0NzUgMTAw
NjQ0Ci0tLSBhL2RvYy9zcmMvc2dtbC9mdW5jLnNnbWwKKysrIGIvZG9jL3NyYy9zZ21sL2Z1bmMu
c2dtbApAQCAtMTU2OTksNiArMTU2OTksMjAgQEAgdGFibGUyLW1hcHBpbmcKICAgICAgICAgPHJl
dHVybnZhbHVlPnQ8L3JldHVybnZhbHVlPgogICAgICAgIDwvcGFyYT48L2VudHJ5PgogICAgICAg
PC9yb3c+CisgICAgICA8cm93PgorICAgICAgIDxlbnRyeSByb2xlPSJmdW5jX3RhYmxlX2VudHJ5
Ij48cGFyYSByb2xlPSJmdW5jX3NpZ25hdHVyZSI+CisgICAgICAgIDx0eXBlPmpzb25iPC90eXBl
PiA8bGl0ZXJhbD5ALT48L2xpdGVyYWw+IDx0eXBlPmpzb25wYXRoPC90eXBlPgorICAgICAgICA8
cmV0dXJudmFsdWU+bnVtZXJpYzwvcmV0dXJudmFsdWU+CisgICAgICAgPC9wYXJhPgorICAgICAg
IDxwYXJhPgorICAgICAgICBSZXR1cm5zIHRoZSByZXN1bHQgb2YgYSBKU09OIHZhbHVlIGF0IHRo
ZSBzcGVjaWZpZWQgcGF0aCBhcyBudW1lcmljLgorICAgICAgICBSYWlzZSBlcnJvciBpZiB0aGUg
SlNPTiB2YWx1ZSBpcyBub3QgYSBudW1lcmljLgorICAgICAgIDwvcGFyYT4KKyAgICAgICA8cGFy
YT4KKyAgICAgICAgPGxpdGVyYWw+J3siYSI6MX0nOjpqc29uYiBALT4gJ2EnIDwvbGl0ZXJhbD4K
KyAgICAgICAgPHJldHVybnZhbHVlPjE8L3JldHVybnZhbHVlPgorICAgICAgIDwvcGFyYT48L2Vu
dHJ5PgorICAgICAgPC9yb3c+CiAgICAgIDwvdGJvZHk+CiAgICAgPC90Z3JvdXA+CiAgICA8L3Rh
YmxlPgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L2pzb25mdW5jcy5jIGIvc3Jj
L2JhY2tlbmQvdXRpbHMvYWR0L2pzb25mdW5jcy5jCmluZGV4IGE0YmZhNWU0MDQwLi41MzkzZGYw
ZWQ3ZiAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L2pzb25mdW5jcy5jCisrKyBi
L3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9qc29uZnVuY3MuYwpAQCAtODg2LDYgKzg4NiwzMiBAQCBq
c29uX29iamVjdF9maWVsZF90ZXh0KFBHX0ZVTkNUSU9OX0FSR1MpCiAJCVBHX1JFVFVSTl9OVUxM
KCk7CiB9CiAKK0RhdHVtCitqc29uYl9vYmplY3RfZmllbGRfbnVtZXJpYyhQR19GVU5DVElPTl9B
UkdTKQoreworCUpzb25iCSAgICpqYiA9IFBHX0dFVEFSR19KU09OQl9QKDApOworCXRleHQJICAg
KmtleSA9IFBHX0dFVEFSR19URVhUX1BQKDEpOworCUpzb25iVmFsdWUgKnY7CisJSnNvbmJWYWx1
ZQl2YnVmOworCisJaWYgKCFKQl9ST09UX0lTX09CSkVDVChqYikpCisJCVBHX1JFVFVSTl9OVUxM
KCk7CisKKwl2ID0gZ2V0S2V5SnNvblZhbHVlRnJvbUNvbnRhaW5lcigmamItPnJvb3QsCisJCQkJ
CQkJCQkgVkFSREFUQV9BTlkoa2V5KSwKKwkJCQkJCQkJCSBWQVJTSVpFX0FOWV9FWEhEUihrZXkp
LAorCQkJCQkJCQkJICZ2YnVmKTsKKworCWlmICh2ID09IE5VTEwgfHwgdi0+dHlwZSA9PSBqYnZO
dWxsKQorCQlQR19SRVRVUk5fTlVMTCgpOworCisJaWYgKHYtPnR5cGUgIT0gamJ2TnVtZXJpYykK
KwkJZWxvZyhFUlJPUiwgImZpZWxkICclcycgaGFzIG5vbi1udW1lcmljIHZhbHVlLiIsIHRleHRf
dG9fY3N0cmluZyhrZXkpKTsKKworCXJldHVybiBQb2ludGVyR2V0RGF0dW0odi0+dmFsLm51bWVy
aWMpOworfTsKKworCiBEYXR1bQoganNvbmJfb2JqZWN0X2ZpZWxkX3RleHQoUEdfRlVOQ1RJT05f
QVJHUykKIHsKZGlmZiAtLWdpdCBhL3NyYy9pbmNsdWRlL2NhdGFsb2cvY2F0dmVyc2lvbi5oIGIv
c3JjL2luY2x1ZGUvY2F0YWxvZy9jYXR2ZXJzaW9uLmgKaW5kZXggZjUwN2I0OWJiMjguLjVhNTM0
NzcxZWRiIDEwMDY0NAotLS0gYS9zcmMvaW5jbHVkZS9jYXRhbG9nL2NhdHZlcnNpb24uaAorKysg
Yi9zcmMvaW5jbHVkZS9jYXRhbG9nL2NhdHZlcnNpb24uaApAQCAtNTcsNiArNTcsNiBAQAogICov
CiAKIC8qCQkJCQkJCXl5eXltbWRkTiAqLwotI2RlZmluZSBDQVRBTE9HX1ZFUlNJT05fTk8JMjAy
MzA3MjYxCisjZGVmaW5lIENBVEFMT0dfVkVSU0lPTl9OTwkyMDIzMDgwMTEKIAogI2VuZGlmCmRp
ZmYgLS1naXQgYS9zcmMvaW5jbHVkZS9jYXRhbG9nL3BnX29wZXJhdG9yLmRhdCBiL3NyYy9pbmNs
dWRlL2NhdGFsb2cvcGdfb3BlcmF0b3IuZGF0CmluZGV4IGIyY2RlYTY2YzRiLi5kNjNiOWY1MTg4
ZCAxMDA2NDQKLS0tIGEvc3JjL2luY2x1ZGUvY2F0YWxvZy9wZ19vcGVyYXRvci5kYXQKKysrIGIv
c3JjL2luY2x1ZGUvY2F0YWxvZy9wZ19vcGVyYXRvci5kYXQKQEAgLTMxNzgsNiArMzE3OCw5IEBA
CiB7IG9pZCA9PiAnMzQ3NycsIGRlc2NyID0+ICdnZXQganNvbmIgb2JqZWN0IGZpZWxkIGFzIHRl
eHQnLAogICBvcHJuYW1lID0+ICctPj4nLCBvcHJsZWZ0ID0+ICdqc29uYicsIG9wcnJpZ2h0ID0+
ICd0ZXh0Jywgb3BycmVzdWx0ID0+ICd0ZXh0JywKICAgb3ByY29kZSA9PiAnanNvbmJfb2JqZWN0
X2ZpZWxkX3RleHQnIH0sCit7IG9pZCA9PiAnMzgxNCcsIGRlc2NyID0+ICdnZXQganNvbmIgb2Jq
ZWN0IGZpZWxkIGFzIG51bWVyaWMnLAorICBvcHJuYW1lID0+ICdALT4nLCBvcHJsZWZ0ID0+ICdq
c29uYicsIG9wcnJpZ2h0ID0+ICd0ZXh0Jywgb3BycmVzdWx0ID0+ICdudW1lcmljJywKKyAgb3By
Y29kZSA9PiAnanNvbmJfb2JqZWN0X2ZpZWxkX251bWVyaWMnIH0sCiB7IG9pZCA9PiAnMzIxMics
IGRlc2NyID0+ICdnZXQganNvbmIgYXJyYXkgZWxlbWVudCcsCiAgIG9wcm5hbWUgPT4gJy0+Jywg
b3BybGVmdCA9PiAnanNvbmInLCBvcHJyaWdodCA9PiAnaW50NCcsIG9wcnJlc3VsdCA9PiAnanNv
bmInLAogICBvcHJjb2RlID0+ICdqc29uYl9hcnJheV9lbGVtZW50JyB9LApkaWZmIC0tZ2l0IGEv
c3JjL2luY2x1ZGUvY2F0YWxvZy9wZ19wcm9jLmRhdCBiL3NyYy9pbmNsdWRlL2NhdGFsb2cvcGdf
cHJvYy5kYXQKaW5kZXggNjk5NjA3Mzk4OWEuLmU5MzMwM2YzYmU3IDEwMDY0NAotLS0gYS9zcmMv
aW5jbHVkZS9jYXRhbG9nL3BnX3Byb2MuZGF0CisrKyBiL3NyYy9pbmNsdWRlL2NhdGFsb2cvcGdf
cHJvYy5kYXQKQEAgLTk5MjgsNiArOTkyOCwxMCBAQAogICBwcm9uYW1lID0+ICdqc29uYl9vYmpl
Y3RfZmllbGRfdGV4dCcsIHByb3JldHR5cGUgPT4gJ3RleHQnLAogICBwcm9hcmd0eXBlcyA9PiAn
anNvbmIgdGV4dCcsIHByb2FyZ25hbWVzID0+ICd7ZnJvbV9qc29uLCBmaWVsZF9uYW1lfScsCiAg
IHByb3NyYyA9PiAnanNvbmJfb2JqZWN0X2ZpZWxkX3RleHQnIH0sCit7IG9pZCA9PiAnMzgxMycs
CisgIHByb25hbWUgPT4gJ2pzb25iX29iamVjdF9maWVsZF9udW1lcmljJywgcHJvcmV0dHlwZSA9
PiAnbnVtZXJpYycsCisgIHByb2FyZ3R5cGVzID0+ICdqc29uYiB0ZXh0JywgcHJvYXJnbmFtZXMg
PT4gJ3tmcm9tX2pzb24sIGZpZWxkX25hbWV9JywKKyAgcHJvc3JjID0+ICdqc29uYl9vYmplY3Rf
ZmllbGRfbnVtZXJpYycgfSwKIHsgb2lkID0+ICczMjE1JywKICAgcHJvbmFtZSA9PiAnanNvbmJf
YXJyYXlfZWxlbWVudCcsIHByb3JldHR5cGUgPT4gJ2pzb25iJywKICAgcHJvYXJndHlwZXMgPT4g
J2pzb25iIGludDQnLCBwcm9hcmduYW1lcyA9PiAne2Zyb21fanNvbiwgZWxlbWVudF9pbmRleH0n
LApkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9qc29uYl9qc29ucGF0aC5v
dXQgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2pzb25iX2pzb25wYXRoLm91dAppbmRleCA2
NjU5YmM5MDkxYS4uYmYyMzUxY2Y5Y2YgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhw
ZWN0ZWQvanNvbmJfanNvbnBhdGgub3V0CisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQv
anNvbmJfanNvbnBhdGgub3V0CkBAIC0zNCw2ICszNCwyNiBAQCBzZWxlY3QganNvbmIgJ3siYSI6
IDEyfScgQD8gJyQuYiArIDInOwogIAogKDEgcm93KQogCitzZWxlY3QganNvbmIgJ3siYSI6IDEy
fScgQC0+ICdhJzsKKyA/Y29sdW1uPyAKKy0tLS0tLS0tLS0KKyAgICAgICAxMgorKDEgcm93KQor
CitzZWxlY3QgcGdfdHlwZW9mKGpzb25iICd7ImEiOiAxMn0nIEAtPiAnYScpOworIHBnX3R5cGVv
ZiAKKy0tLS0tLS0tLS0tCisgbnVtZXJpYworKDEgcm93KQorCitzZWxlY3QganNvbmIgJ3siYSI6
IDEyfScgQC0+ICdiJzsKKyA/Y29sdW1uPyAKKy0tLS0tLS0tLS0KKyAgICAgICAgIAorKDEgcm93
KQorCitzZWxlY3QganNvbmIgJ3siYSI6ICIxMmEifScgQC0+ICdhJzsKK0VSUk9SOiAgZmllbGQg
J2EnIGhhcyBub24tbnVtZXJpYyB2YWx1ZS4KIHNlbGVjdCBqc29uYiAneyJhIjogeyJhIjogMTJ9
fScgQD8gJyQuYS5hJzsKICA/Y29sdW1uPyAKIC0tLS0tLS0tLS0KZGlmZiAtLWdpdCBhL3NyYy90
ZXN0L3JlZ3Jlc3Mvc3FsL2pzb25iX2pzb25wYXRoLnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3Fs
L2pzb25iX2pzb25wYXRoLnNxbAppbmRleCBlMGNlNTA5MjY0YS4uMzI1NzY1NjZmMTEgMTAwNjQ0
Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2pzb25iX2pzb25wYXRoLnNxbAorKysgYi9zcmMv
dGVzdC9yZWdyZXNzL3NxbC9qc29uYl9qc29ucGF0aC5zcWwKQEAgLTQsNiArNCwxMCBAQCBzZWxl
Y3QganNvbmIgJ3siYSI6IDEyfScgQD8gJyQuYS5iJzsKIHNlbGVjdCBqc29uYiAneyJhIjogMTJ9
JyBAPyAnJC5iJzsKIHNlbGVjdCBqc29uYiAneyJhIjogMTJ9JyBAPyAnJC5hICsgMic7CiBzZWxl
Y3QganNvbmIgJ3siYSI6IDEyfScgQD8gJyQuYiArIDInOworc2VsZWN0IGpzb25iICd7ImEiOiAx
Mn0nIEAtPiAnYSc7CitzZWxlY3QgcGdfdHlwZW9mKGpzb25iICd7ImEiOiAxMn0nIEAtPiAnYScp
Oworc2VsZWN0IGpzb25iICd7ImEiOiAxMn0nIEAtPiAnYic7CitzZWxlY3QganNvbmIgJ3siYSI6
ICIxMmEifScgQC0+ICdhJzsKIHNlbGVjdCBqc29uYiAneyJhIjogeyJhIjogMTJ9fScgQD8gJyQu
YS5hJzsKIHNlbGVjdCBqc29uYiAneyJhIjogeyJhIjogMTJ9fScgQD8gJyQuKi5hJzsKIHNlbGVj
dCBqc29uYiAneyJiIjogeyJhIjogMTJ9fScgQD8gJyQuKi5hJzsKLS0gCjIuMjEuMAoK
--0000000000006e55830601fa2c03--