MIME-Version: 1.0
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
 <CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com> <CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com>
In-Reply-To: <CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Thu, 3 Aug 2023 06:59:19 +0200
Message-ID: <CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com>
Subject: Re: Extract numeric filed in JSONB more effectively
To: Andy Fan <zhihui.fan1213@gmail.com>
Cc: jian he <jian.universality@gmail.com>, 
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c2838a0601fda635"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0%3D--%2BWgN_A%40mail.gmail.com>
Precedence: bulk

--000000000000c2838a0601fda635
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

=C4=8Dt 3. 8. 2023 v 2:51 odes=C3=ADlatel Andy Fan <zhihui.fan1213@gmail.co=
m> napsal:

> 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 =3D 2;   30.56ms
> select 1 from tb where (a->>'a')::numeric =3D 2; 29.43ms
> select 1 from tb where (a@->'a') =3D 2;              14.80ms
>
> [1] https://commitfest.postgresql.org/44/4476/
>
>
I don't like this solution because it is bloating  operators and it is not
extra readable. For completeness you should implement cast for date, int,
boolean too. Next, the same problem is with XML or hstore type (probably
with any types that are containers).

It is strange so only casting is 2x slower. I don't like the idea so using
a special operator is 2x faster than common syntax for casting. It is a
signal, so there is a space for optimization. Black magic with special
operators is not user friendly for relatively common problems.

Maybe we can introduce some *internal operator* "extract to type", and in
rewrite stage we can the pattern (x->'field')::type transform to OP(x,
'field', typid)

Regards

Pavel


--=20
> Best Regards
> Andy Fan
>

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

<div dir=3D"ltr"><div>Hi<br></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">=C4=8Dt 3. 8. 2023 v=C2=A02:51 odes=C3=ADlate=
l Andy Fan &lt;<a href=3D"mailto:zhihui.fan1213@gmail.com">zhihui.fan1213@g=
mail.com</a>&gt; napsal:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><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 0=
px 0.8ex;border-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/" target=3D"_blank">https://commitfest.postgresql.org/44=
/4476/</a><br></div><div><br></div></div></blockquote><div><br></div><div>I=
 don&#39;t like this solution because it is bloating=C2=A0 operators and it=
 is not extra readable. For completeness you should implement cast for date=
, int, boolean too. Next, the same problem is with XML or hstore type (prob=
ably with any types that are containers). <br></div><div><br></div><div>It =
is strange so only casting is 2x slower. I don&#39;t like the idea so using=
 a special operator is 2x faster than common syntax for casting. It is a si=
gnal, so there is a space for optimization. Black magic with special operat=
ors is not user friendly for relatively common problems.<br></div><div><br>=
</div><div>Maybe we can introduce some *internal operator* &quot;extract to=
 type&quot;, and in rewrite stage we can the pattern (x-&gt;&#39;field&#39;=
)::type transform to OP(x, &#39;field&#39;, typid)</div><div><br></div><div=
>Regards</div><div><br></div><div>Pavel</div><div><br></div><div><br></div>=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div></d=
iv><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" cl=
ass=3D"gmail_signature"><div dir=3D"ltr">Best Regards<div>Andy Fan</div></d=
iv></div></div>
</blockquote></div></div>

--000000000000c2838a0601fda635--