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> <873417fgc0.fsf@163.com>
In-Reply-To: <873417fgc0.fsf@163.com>
From: Haibo Yan <tristan.yim@gmail.com>
Date: Tue, 7 Apr 2026 01:31:16 -0700
Message-ID: 
 <CABXr29GhnWxrGXD2YijYSDxwObGLqOsf8gvfzrFu=UcYU71XYA@mail.gmail.com>
Subject: Re: Extract numeric filed in JSONB more effectively
To: Andy Fan <zhihuifan1213@163.com>
Cc: Dmitry Dolgov <9erthalion6@gmail.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="0000000000000671e7064eda9cdc"
Archived-At: 
 <https://www.postgresql.org/message-id/CABXr29GhnWxrGXD2YijYSDxwObGLqOsf8gvfzrFu%3DUcYU71XYA%40mail.gmail.com>
Precedence: bulk

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

On Mon, Apr 6, 2026 at 11:14=E2=80=AFPM Andy Fan <zhihuifan1213@163.com> wr=
ote:

>
> Hi Haibo,
>
> > 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. ..
> > and does not yet try to cover array/path extraction or integer/float
> > typed extractors.
>
> Thanks for working on this. I did a quick comparison between this
> version and my last patch v18 [1],  here is the difference.
>
> My previous v18[1]: 542 insertions(+), 59 deletions(-)
> Your patch: 543 insertions(+), 5 deletions(-)
>
> However as what you have realized, your current patch still lack of many
> optimizations, Not only the integer/float stuff, but also many
> functions, e.g. jsonb_array_element, jsonb_extract_path,
> jsonb_path_query and jsonb_path_query_first. After considering this,
> what patch will look like in your approach? I guess you can see many
> duplications. Less code doesn't always mean better, but I think this
> still be a key consideration to address/check.
>

Yes, I agree the current patch is still missing a large part of the full
matrix from v18 =E2=80=94 not only int4/int8/float8, but also other extract=
or
families such as jsonb_array_element, jsonb_extract_path, jsonb_path_query,
and jsonb_path_query_first.

My intent with this version was not to cover the whole space at once, but
to first reduce it to a smaller stage-1 subset that is easier to review and
easier to reason about. In particular, I wanted to validate the simpler
rewrite shape first: keep the support-function-based approach, but rewrite
directly to explicit typed extractor functions, without the previous
start/finish/internal pipeline.

I agree that if this approach is extended to the full matrix naively,
duplication will become a real issue. So I am not claiming that the current
patch shape should simply be copied mechanically across every
extractor/type combination. My thought was to first see whether this
narrower subset is acceptable in principle, and then decide how best to
extend it without reintroducing too much abstraction or too much
duplication.

So for now I would prefer to keep the current scope intentionally narrow:


   -

   jsonb_object_field / -> / equivalent subscripting form
   -

   casts to numeric and bool

and treat the rest as follow-up work, rather than trying to solve the
entire matrix in one patch.

>
> > 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.10x
> > SELECT count(*) FROM t WHERE (j->'b')::bool  115.665 ms  51.945 ms
>  2.23x
> >
> -------------------------------------------------------------------------=
-------
>
> Thanks for running the test.
>
> [1] https://www.postgresql.org/message-id/87ttk0lgcx.fsf%40163.com
>
> --
> Best Regards
> Andy Fan
>
Thanks,
Haibo

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

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Apr 6, 2026 at 11:14=E2=80=AFPM A=
ndy Fan &lt;<a href=3D"mailto:zhihuifan1213@163.com">zhihuifan1213@163.com<=
/a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex"><br>
Hi Haibo,<br>
<br>
&gt; I=E2=80=99d like to continue pushing this patch forward.<br>
&gt; Based on the earlier discussion, I reworked the patch into a smaller<b=
r>
&gt; stage-1 version with a narrower scope and a simpler rewrite<br>
&gt; strategy. ..<br>
&gt; and does not yet try to cover array/path extraction or integer/float<b=
r>
&gt; typed extractors.<br>
<br>
Thanks for working on this. I did a quick comparison between this<br>
version and my last patch v18 [1],=C2=A0 here is the difference.<br>
<br>
My previous v18[1]: 542 insertions(+), 59 deletions(-)<br>
Your patch: 543 insertions(+), 5 deletions(-)<br>
<br>
However as what you have realized, your current patch still lack of many <b=
r>
optimizations, Not only the integer/float stuff, but also many<br>
functions, e.g. jsonb_array_element, jsonb_extract_path,<br>
jsonb_path_query and jsonb_path_query_first. After considering this,<br>
what patch will look like in your approach? I guess you can see many<br>
duplications. Less code doesn&#39;t always mean better, but I think this<br=
>
still be a key consideration to address/check.=C2=A0<br></blockquote><div><=
br></div><div>
<p class=3D"gmail-p1">Yes, I agree the current patch is still missing a lar=
ge part of the full matrix from v18 =E2=80=94 not only <span class=3D"gmail=
-s1">int4</span>/<span class=3D"gmail-s1">int8</span>/<span class=3D"gmail-=
s1">float8</span>, but also other extractor families such as <span class=3D=
"gmail-s1">jsonb_array_element</span>, <span class=3D"gmail-s1">jsonb_extra=
ct_path</span>, <span class=3D"gmail-s1">jsonb_path_query</span>, and <span=
 class=3D"gmail-s1">jsonb_path_query_first</span>.</p>
<p class=3D"gmail-p1">My intent with this version was not to cover the whol=
e space at once, but to first reduce it to a smaller stage-1 subset that is=
 easier to review and easier to reason about. In particular, I wanted to va=
lidate the simpler rewrite shape first: keep the support-function-based app=
roach, but rewrite directly to explicit typed extractor functions, without =
the previous start/finish/internal pipeline.</p>
<p class=3D"gmail-p1">I agree that if this approach is extended to the full=
 matrix naively, duplication will become a real issue. So I am not claiming=
 that the current patch shape should simply be copied mechanically across e=
very extractor/type combination. My thought was to first see whether this n=
arrower subset is acceptable in principle, and then decide how best to exte=
nd it without reintroducing too much abstraction or too much duplication.</=
p>
<p class=3D"gmail-p1">So for now I would prefer to keep the current scope i=
ntentionally narrow:</p>
<p class=3D"gmail-p3"><span class=3D"gmail-s2"></span></p><ul><li>
<p class=3D"gmail-p1"><span class=3D"gmail-s1">jsonb_object_field</span> / =
<span class=3D"gmail-s1">-&gt;</span> / equivalent subscripting form</p>
</li><li>
<p class=3D"gmail-p1">casts to <span class=3D"gmail-s1">numeric</span> and =
<span class=3D"gmail-s1">bool</span></p></li></ul>
<p class=3D"gmail-p1">and treat the rest as follow-up work, rather than try=
ing to solve the entire matrix in one patch.=C2=A0</p></div><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">
<br>
&gt; I also ran a small microbenchmark to isolate the cast-over-object-fiel=
d path. On my setup, the current patch shows the following gains:<br>
&gt; 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 Before=C2=A0 =C2=A0 =C2=A0 After=C2=A0 =C2=A0 =C2=A0 =C2=A0Speedup<br>
&gt; ----------------------------------------------------------------------=
----------<br>
&gt; 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>
&gt; SELECT count(*) FROM t WHERE (j-&gt;&#39;b&#39;)::bool=C2=A0 115.665 m=
s=C2=A0 51.945 ms=C2=A0 =C2=A02.23x<br>
&gt; ----------------------------------------------------------------------=
----------<br>
<br>
Thanks for running the test. <br>
<br>
[1] <a href=3D"https://www.postgresql.org/message-id/87ttk0lgcx.fsf%40163.c=
om" rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/message=
-id/87ttk0lgcx.fsf%40163.com</a><br>
<br>
-- <br>
Best Regards<br>
Andy Fan<br></blockquote><p class=3D"gmail-p1">Thanks,</p><div>Haibo=C2=A0<=
/div></div></div>

--0000000000000671e7064eda9cdc--