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> <CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com>
In-Reply-To: <CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com>
From: Andy Fan <zhihui.fan1213@gmail.com>
Date: Thu, 3 Aug 2023 15:53:47 +0800
Message-ID: <CAKU4AWpDdFXAD+dMC1HeErXSKBUUBRGWkf=dAcX3wZgBNsWM=g@mail.gmail.com>
Subject: Re: Extract numeric filed in JSONB more effectively
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: jian he <jian.universality@gmail.com>, 
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000030deff06020015ae"
Archived-At: <https://www.postgresql.org/message-id/CAKU4AWpDdFXAD%2BdMC1HeErXSKBUUBRGWkf%3DdAcX3wZgBNsWM%3Dg%40mail.gmail.com>
Precedence: bulk

--00000000000030deff06020015ae
Content-Type: text/plain; charset="UTF-8"

Hi Pavel:

Thanks for the feedback.

I don't like this solution because it is bloating  operators and it is not
> extra readable.
>

If we support it with cast, could we say we are bloating CAST?  It is true
that it is not extra readable, if so how about  a->>'a'  return text?
Actually
I can't guess any meaning of the existing jsonb operations without
documentation.

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).
>

I am not sure completeness is a gold rule we should obey anytime,
like we have some function like int24le to avoid the unnecessary
cast, but we just avoid casting for special types for performance
reason, but not for all. At the same time,  `int2/int4/int8` doesn't
have a binary compatibility type in jsonb. and the serialization
/deserialization for boolean is pretty cheap.

I didn't realize timetime types are binary compatible with SQL,
so maybe we can have some similar optimization as well.
(It is a pity that timestamp(tz) are not binary, or else we may
just need one operator).


>
> 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.
>

I don't think "Black magic" is a proper word here, since it is not much
different from ->> return a text.  If you argue text can be cast to
most-of-types,  that would be a reason, but I doubt this difference
should generate a "black magic".


>
> 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)
>

Not sure what the OP should be?  If it is a function, what is the
return value?  It looks to me like it is hard to do in c language?

After all,  if we really care about the number of operators, I'm OK
with just let users use the function directly, like

jsonb_field_as_numeric(jsonb, 'filedname')
jsonb_field_as_timestamp(jsonb, 'filedname');
jsonb_field_as_timestamptz(jsonb, 'filedname');
jsonb_field_as_date(jsonb, 'filedname');

it can save an operator and sloves the readable issue.

-- 
Best Regards
Andy Fan

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

<div dir=3D"ltr"><div>Hi Pavel:</div><div><br></div><div>Thanks for the fee=
dback.=C2=A0</div><div><br></div><div class=3D"gmail_quote"><blockquote cla=
ss=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_quo=
te"><div>I don&#39;t like this solution because it is bloating=C2=A0 operat=
ors and it is not extra readable. </div></div></div></blockquote><div>=C2=
=A0</div><div><div>If we support it with cast, could we say we are bloating=
=C2=A0CAST?=C2=A0 It is true</div><div>that it is not extra readable, if so=
 how about=C2=A0 a-&gt;&gt;&#39;a&#39;=C2=A0 return text?=C2=A0 Actually</d=
iv></div><div>I can&#39;t guess any meaning of the existing jsonb operation=
s without</div><div>documentation.</div><div><br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><=
div>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).</div></div></div></blockquote><div><br></div><div>I a=
m not sure completeness is a gold rule we should obey anytime,</div><div>li=
ke we have some function like int24le to avoid the unnecessary</div><div>ca=
st, but we just avoid casting for special types for performance</div><div>r=
eason, but not for all. At the same time,=C2=A0 `int2/int4/int8` doesn&#39;=
t</div><div>have a binary compatibility type in jsonb. and the serializatio=
n</div><div>/deserialization for boolean is pretty cheap.</div><div><br></d=
iv><div>I didn&#39;t realize timetime=C2=A0types are binary compatible with=
 SQL,=C2=A0=C2=A0</div><div>so maybe we can have some similar optimization =
as well.=C2=A0</div><div>(It is a pity that timestamp(tz) are not binary, o=
r else we may=C2=A0</div><div>just need one operator).=C2=A0</div><div>=C2=
=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"=
><div class=3D"gmail_quote"><div><br></div><div>I don&#39;t like the idea s=
o 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.<br></div></=
div></div></blockquote><div><br></div><div>I don&#39;t think &quot;Black ma=
gic&quot; is a proper word here, since it is not much</div><div>different f=
rom -&gt;&gt; return a text.=C2=A0 If you argue text can be cast to=C2=A0</=
div><div>most-of-types,=C2=A0 that would be a reason, but I doubt this diff=
erence</div><div>should generate a &quot;black magic&quot;.=C2=A0</div><div=
>=C2=A0</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></div><div><br></div><div>Maybe we can=
 introduce some *internal operator* &quot;extract to type&quot;, and in rew=
rite stage we can the pattern (x-&gt;&#39;field&#39;)::type transform to OP=
(x, &#39;field&#39;, typid)</div></div></div></blockquote><div><br></div><d=
iv>Not sure what the OP should be?=C2=A0 If it is a function, what is the</=
div><div>return value?=C2=A0 It looks to me like it is hard to do in c lang=
uage?</div><div><br></div><div>After all,=C2=A0 if we really care about the=
 number of operators, I&#39;m OK</div><div>with just let users use the func=
tion directly, like</div><div><br></div><div>jsonb_field_as_numeric(jsonb, =
&#39;filedname&#39;)=C2=A0</div><div>jsonb_field_as_timestamp(jsonb, &#39;f=
iledname&#39;);=C2=A0</div><div>jsonb_field_as_timestamptz(jsonb, &#39;file=
dname&#39;);=C2=A0<br></div><div>jsonb_field_as_date(jsonb, &#39;filedname&=
#39;);=C2=A0<br></div><div><br></div><div>it can save an operator and slove=
s the readable issue.=C2=A0</div><div><br></div></div><span class=3D"gmail_=
signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature">=
<div dir=3D"ltr">Best Regards<div>Andy Fan</div></div></div></div>

--00000000000030deff06020015ae--