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>
 <CAKU4AWpDdFXAD+dMC1HeErXSKBUUBRGWkf=dAcX3wZgBNsWM=g@mail.gmail.com> <3198e7c138fc0b5f80512785a87b7bb2@anastigmatix.net>
In-Reply-To: <3198e7c138fc0b5f80512785a87b7bb2@anastigmatix.net>
From: Andy Fan <zhihui.fan1213@gmail.com>
Date: Thu, 3 Aug 2023 21:50:15 +0800
Message-ID: <CAKU4AWrMR8AS2XoedYB=X0dwmrHv23z2STtByazDVVJLkDt9UA@mail.gmail.com>
Subject: Re: Extract numeric filed in JSONB more effectively
To: Chapman Flack <chap@anastigmatix.net>
Cc: Pavel Stehule <pavel.stehule@gmail.com>, jian he <jian.universality@gmail.com>, 
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ff1b710602050fb4"
Archived-At: <https://www.postgresql.org/message-id/CAKU4AWrMR8AS2XoedYB%3DX0dwmrHv23z2STtByazDVVJLkDt9UA%40mail.gmail.com>
Precedence: bulk

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

Hi:

On Thu, Aug 3, 2023 at 8:34=E2=80=AFPM Chapman Flack <chap@anastigmatix.net=
> wrote:

> On 2023-08-03 03:53, Andy Fan wrote:
> > 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).
>
> Not to veer from the thread, but something about that paragraph
> has been hard for me to parse/follow.
>

I don't think this is a key conflict so far. but I'd explain this in more
detail. If timestamp -> timestamptz or timestamptz -> timestamp is
binary compatible,  we can only have 1 operator to return a timestamp.
then when we cast it to timestamptz, it will be a no-op during runtime.
however cast between timestamp and timestamptz is not binary
compatible. whose castmethod is 'f';


>
> >> 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?
>
> Now I am wondering about the 'planner support function' available
> in CREATE FUNCTION since PG 12. I've never played with that yet.
> Would that make it possible to have some, rather generic, extract
> from JSON operator that can look at the surrounding expression
> and replace itself sometimes with something  efficient?
>

I didn't realize this before,  'planner support function' looks
amazing and SupportRequestSimplify looks promising, I will check it
more.

--=20
Best Regards
Andy Fan

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

<div dir=3D"ltr"><div>Hi:=C2=A0</div><br><div class=3D"gmail_quote"><div di=
r=3D"ltr" class=3D"gmail_attr">On Thu, Aug 3, 2023 at 8:34=E2=80=AFPM Chapm=
an Flack &lt;<a href=3D"mailto:chap@anastigmatix.net" target=3D"_blank">cha=
p@anastigmatix.net</a>&gt; wrote:<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">On 2023-08-03 03:53, Andy Fan wrote:<br>
&gt; I didn&#39;t realize timetime types are binary compatible with SQL,<br=
>
&gt; so maybe we can have some similar optimization as well.<br>
&gt; (It is a pity that timestamp(tz) are not binary, or else we may<br>
&gt; just need one operator).<br>
<br>
Not to veer from the thread, but something about that paragraph<br>
has been hard for me to parse/follow.<br></blockquote><div><br></div><div>I=
 don&#39;t think this is a key conflict so far. but I&#39;d explain this in=
 more</div><div>detail. If timestamp -&gt; timestamptz or timestamptz -&gt;=
 timestamp is</div><div>binary compatible,=C2=A0 we can only have 1 operato=
r to return a timestamp.</div><div>then when we cast it to timestamptz, it =
will be a no-op during runtime.</div><div>however cast between timestamp an=
d timestamptz is not binary</div><div>compatible. whose=C2=A0castmethod=C2=
=A0is &#39;f&#39;;=C2=A0</div>


<div><br></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-lef=
t:1ex">
<br>
&gt;&gt; Maybe we can introduce some *internal operator* &quot;extract to t=
ype&quot;, and <br>
&gt;&gt; in<br>
&gt;&gt; rewrite stage we can the pattern (x-&gt;&#39;field&#39;)::type tra=
nsform to OP(x,<br>
&gt;&gt; &#39;field&#39;, typid)<br>
&gt; <br>
&gt; Not sure what the OP should be?=C2=A0 If it is a function, what is the=
<br>
&gt; return value?=C2=A0 It looks to me like it is hard to do in c language=
?<br>
<br>
Now I am wondering about the &#39;planner support function&#39; available<b=
r>
in CREATE FUNCTION since PG 12. I&#39;ve never played with that yet.<br>
Would that make it possible to have some, rather generic, extract<br>
from JSON operator that can look at the surrounding expression<br>
and replace itself sometimes with something=C2=A0 efficient?<br></blockquot=
e><div><br></div><div>I didn&#39;t realize this before,=C2=A0 &#39;planner =
support function&#39; looks</div><div>amazing and SupportRequestSimplify lo=
oks promising, I will check it=C2=A0</div><div>more.=C2=A0</div></div><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>Andy Fan</=
div></div></div></div>

--000000000000ff1b710602050fb4--