MIME-Version: 1.0
References: <87r0hmvuvr.fsf@163.com>
 <8102ff5b-b156-409e-a48f-e53e63a39b36@eisentraut.org>
 <8734t6c5rh.fsf@163.com> <87o7bn7z56.fsf@163.com> <875xx197bp.fsf@163.com>
 <87ttk0lgcx.fsf@163.com>
 <CAApHDvqnUONCN54dAXdH678ELK7aH2gyVhh0GjTRtrexPd9YMw@mail.gmail.com>
 <8734m5fua1.fsf@163.com>
 <mmilfltxeqgajpi4zok2v2moudot6bhdmwsqgjgnymz3nvkvng@k3caignaldcp>
 <87a5dx4cfb.fsf@163.com>
 <cudr6wao4hz2otlycx2z2equkigbjxslxvzqb3ejfc4amroheo@frqbvffcgqnu>
 <7487A577-7BDC-4B94-9FFA-741E95439BFC@gmail.com>
In-Reply-To: <7487A577-7BDC-4B94-9FFA-741E95439BFC@gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Fri, 3 Apr 2026 06:50:27 +0200
Message-ID: 
 <CAFj8pRBpV9e+709D-qf=g5hgtXVN_wzKipRC0c_ZmdHws8rWtw@mail.gmail.com>
Subject: Re: Extract numeric filed in JSONB more effectively
To: Haibo Yan <tristan.yim@gmail.com>
Cc: Dmitry Dolgov <9erthalion6@gmail.com>, Andy Fan <zhihuifan1213@163.com>,
	David Rowley <dgrowleyml@gmail.com>, Peter Eisentraut <peter@eisentraut.org>,
	Amit Langote <amitlangote09@gmail.com>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>,
	jian he <jian.universality@gmail.com>, Chapman Flack <chap@anastigmatix.net>,
	pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000072571c064e871074"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFj8pRBpV9e%2B709D-qf%3Dg5hgtXVN_wzKipRC0c_ZmdHws8rWtw%40mail.gmail.com>
Precedence: bulk

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

p=C3=A1 3. 4. 2026 v 6:46 odes=C3=ADlatel Haibo Yan <tristan.yim@gmail.com>=
 napsal:

> On Nov 22, 2024, at 10:14=E2=80=AFAM, Dmitry Dolgov <9erthalion6@gmail.co=
m> wrote:
> >
> >> On Mon, Nov 18, 2024 at 08:23:52AM GMT, Andy Fan wrote:
> >>
> >>>>> I imagined you'd the patch should create a SupportRequestSimplify
> >>>>> support function for jsonb_numeric() that checks if the input
> >>>>> expression is an OpExpr with funcid of jsonb_object_field().  All y=
ou
> >>>>> do then is ditch the cast and change the OpExpr to call a new
> function
> >>>>> named jsonb_object_field_numeric() which returns the val.numeric
> >>>>> directly.  Likely the same support function could handle jsonb cast=
s
> >>>>> to other types too, in which case you'd just call some other
> function,
> >>>>> e.g jsonb_object_field_timestamp() or jsonb_object_field_boolean().
> >>>>
> >>>> Basically yes. The reason complexity comes when we many operators we
> >>>> want to optimize AND my patch I want to reduce the number of functio=
n
> >>>> created.
> >>>>
> >>>> [...]
> >>>>
> >>>> Within the start / finish function, we need to create *7* functions.
> >>>
> >>> Any particular reason you want to keep number of functions minimal? I=
s
> >>> it just to make the patch smaller? I might be missing something witho=
ut
> >>> looking at the implementation in details, but the difference between =
10
> >>> and 7 functions doesn't seem to be significant.
> >>
> >> Another reason is for reducing code duplication, writting too many
> >> similar function looks not good to me. Chapman expressed this idea
> >> first at [1]. Search "it would make me happy to further reduce some
> >> of the code" in the message.
> >>
> >> Acutally this doesn't make the patch complexer too much.
> >>
> >> [1]
> >>
> https://www.postgresql.org/message-id/5138c6b5fd239e7ce4e1a4e63826ac27%40=
anastigmatix.net
> >
> > It might not make everything too much complex, but e.g. relabeling of
> > the first argument for a "finish" function into an internal one sounds
> > strange to me. Maybe there is a way to avoid duplication of the code,
> > but keep all needed functions in pg_proc?
> >
> > Btw, sorry to complain about small details, but I find start / finish
> > naming pattern not quite fitting here. Their main purpose is to extract
> > / convert a value, the order in which they are happening is less
> > relevant.
> >
> >
> >
> >
>
> Hi all,
> I=E2=80=99d like to continue pushing this patch forward.
> Based on the earlier discussion, I reworked the patch into a smaller
> stage-1 version with a narrower scope and a simpler rewrite strategy. The
> current patch keeps the normal SQL syntax unchanged and uses
> support-function simplification to rewrite only the following patterns:
>         (jsonb_object_field(...))::numeric
>         jsonb_object_field(...))::bool
> into explicit typed extractor calls.
> So at this stage it intentionally covers only:
>         jsonb_object_field(...) / ->
>         casts to numeric
>         casts to bool
> and does not yet try to cover array/path extraction or integer/float type=
d
> extractors.
> I also ran a small microbenchmark to isolate the cast-over-object-field
> path. On my setup, the current patch shows the following gains:
> Query                                        Before      After
>  Speedup
>
> -------------------------------------------------------------------------=
-------
> SELECT sum((j->'n')::numeric) FROM t         118.028 ms  56.082 ms   2.10=
x
> SELECT count(*) FROM t WHERE (j->'b')::bool  115.665 ms  51.945 ms   2.23=
x
>
> -------------------------------------------------------------------------=
-------
> These are microbenchmark numbers rather than end-to-end workload results,
> but they suggest that the simplified rewrite path is worth pursuing.
> My goal with this version is not to solve the full matrix at once, but to
> first land a reviewer-friendly subset that:
> 1. does not introduce new user-visible operators,
> 2. keeps ordinary cast syntax unchanged,
> 3. avoids the more abstract internal/start-finish style machinery,
> 4. and uses explicit rewrite targets that are easier to review.
> If this direction looks reasonable, I=E2=80=99d appreciate another round =
of review
> on the updated patch. If people think the stage-1 scope is acceptable, I
> can continue with follow-up patches for additional extractor families and
> target types.
> Thanks,
> Haibo
>

+1

Pavel

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">p=C3=A1 3. 4. 2=
026 v=C2=A06:46 odes=C3=ADlatel Haibo Yan &lt;<a href=3D"mailto:tristan.yim=
@gmail.com">tristan.yim@gmail.com</a>&gt; napsal:<br></div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex">On Nov 22, 2024, at 10:14=E2=80=AFAM, Dmi=
try Dolgov &lt;<a href=3D"mailto:9erthalion6@gmail.com" target=3D"_blank">9=
erthalion6@gmail.com</a>&gt; wrote:<br>
&gt; <br>
&gt;&gt; On Mon, Nov 18, 2024 at 08:23:52AM GMT, Andy Fan wrote:<br>
&gt;&gt; <br>
&gt;&gt;&gt;&gt;&gt; I imagined you&#39;d the patch should create a Support=
RequestSimplify<br>
&gt;&gt;&gt;&gt;&gt; support function for jsonb_numeric() that checks if th=
e input<br>
&gt;&gt;&gt;&gt;&gt; expression is an OpExpr with funcid of jsonb_object_fi=
eld().=C2=A0 All you<br>
&gt;&gt;&gt;&gt;&gt; do then is ditch the cast and change the OpExpr to cal=
l a new function<br>
&gt;&gt;&gt;&gt;&gt; named jsonb_object_field_numeric() which returns the v=
al.numeric<br>
&gt;&gt;&gt;&gt;&gt; directly.=C2=A0 Likely the same support function could=
 handle jsonb casts<br>
&gt;&gt;&gt;&gt;&gt; to other types too, in which case you&#39;d just call =
some other function,<br>
&gt;&gt;&gt;&gt;&gt; e.g jsonb_object_field_timestamp() or jsonb_object_fie=
ld_boolean().<br>
&gt;&gt;&gt;&gt; <br>
&gt;&gt;&gt;&gt; Basically yes. The reason complexity comes when we many op=
erators we<br>
&gt;&gt;&gt;&gt; want to optimize AND my patch I want to reduce the number =
of function<br>
&gt;&gt;&gt;&gt; created.<br>
&gt;&gt;&gt;&gt; <br>
&gt;&gt;&gt;&gt; [...]<br>
&gt;&gt;&gt;&gt; <br>
&gt;&gt;&gt;&gt; Within the start / finish function, we need to create *7* =
functions.<br>
&gt;&gt;&gt; <br>
&gt;&gt;&gt; Any particular reason you want to keep number of functions min=
imal? Is<br>
&gt;&gt;&gt; it just to make the patch smaller? I might be missing somethin=
g without<br>
&gt;&gt;&gt; looking at the implementation in details, but the difference b=
etween 10<br>
&gt;&gt;&gt; and 7 functions doesn&#39;t seem to be significant.<br>
&gt;&gt; <br>
&gt;&gt; Another reason is for reducing code duplication, writting too many=
<br>
&gt;&gt; similar function looks not good to me. Chapman expressed this idea=
<br>
&gt;&gt; first at [1]. Search &quot;it would make me happy to further reduc=
e some<br>
&gt;&gt; of the code&quot; in the message.<br>
&gt;&gt; <br>
&gt;&gt; Acutally this doesn&#39;t make the patch complexer too much.<br>
&gt;&gt; <br>
&gt;&gt; [1]<br>
&gt;&gt; <a href=3D"https://www.postgresql.org/message-id/5138c6b5fd239e7ce=
4e1a4e63826ac27%40anastigmatix.net" rel=3D"noreferrer" target=3D"_blank">ht=
tps://www.postgresql.org/message-id/5138c6b5fd239e7ce4e1a4e63826ac27%40anas=
tigmatix.net</a><br>
&gt; <br>
&gt; It might not make everything too much complex, but e.g. relabeling of<=
br>
&gt; the first argument for a &quot;finish&quot; function into an internal =
one sounds<br>
&gt; strange to me. Maybe there is a way to avoid duplication of the code,<=
br>
&gt; but keep all needed functions in pg_proc?<br>
&gt; <br>
&gt; Btw, sorry to complain about small details, but I find start / finish<=
br>
&gt; naming pattern not quite fitting here. Their main purpose is to extrac=
t<br>
&gt; / convert a value, the order in which they are happening is less<br>
&gt; relevant.<br>
&gt; <br>
&gt; <br>
&gt; <br>
&gt; <br>
<br>
Hi all,<br>
I=E2=80=99d like to continue pushing this patch forward.<br>
Based on the earlier discussion, I reworked the patch into a smaller stage-=
1 version with a narrower scope and a simpler rewrite strategy. The current=
 patch keeps the normal SQL syntax unchanged and uses support-function simp=
lification to rewrite only the following patterns:<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 (jsonb_object_field(...))::numeric<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 jsonb_object_field(...))::bool<br>
into explicit typed extractor calls.<br>
So at this stage it intentionally covers only:<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 jsonb_object_field(...) / -&gt;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 casts to numeric<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 casts to bool<br>
and does not yet try to cover array/path extraction or integer/float typed =
extractors.<br>
I also ran a small microbenchmark to isolate the cast-over-object-field pat=
h. On my setup, the current patch shows the following gains:<br>
Query=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 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Befor=
e=C2=A0 =C2=A0 =C2=A0 After=C2=A0 =C2=A0 =C2=A0 =C2=A0Speedup<br>
---------------------------------------------------------------------------=
-----<br>
SELECT sum((j-&gt;&#39;n&#39;)::numeric) FROM t=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0118.028 ms=C2=A0 56.082 ms=C2=A0 =C2=A02.10x<br>
SELECT count(*) FROM t WHERE (j-&gt;&#39;b&#39;)::bool=C2=A0 115.665 ms=C2=
=A0 51.945 ms=C2=A0 =C2=A02.23x<br>
---------------------------------------------------------------------------=
-----<br>
These are microbenchmark numbers rather than end-to-end workload results, b=
ut they suggest that the simplified rewrite path is worth pursuing.<br>
My goal with this version is not to solve the full matrix at once, but to f=
irst land a reviewer-friendly subset that:<br>
1. does not introduce new user-visible operators,<br>
2. keeps ordinary cast syntax unchanged,<br>
3. avoids the more abstract internal/start-finish style machinery,<br>
4. and uses explicit rewrite targets that are easier to review.<br>
If this direction looks reasonable, I=E2=80=99d appreciate another round of=
 review on the updated patch. If people think the stage-1 scope is acceptab=
le, I can continue with follow-up patches for additional extractor families=
 and target types.<br>
Thanks,<br>
Haibo<br></blockquote><div><br></div><div>+1</div><div><br></div><div>Pavel=
</div><div>=C2=A0</div></div></div>

--00000000000072571c064e871074--