Re: Extract numeric filed in JSONB more effectively

Hi Matthias:

On Wed, Aug 2, 2023 at 7:33 AM Andy Fan <zhihui.fan1213@gmail.com> wrote:

>
>
> On Tue, Aug 1, 2023 at 7:03 PM Matthias van de Meent <
> boekewurm+postgres@gmail.com> wrote:
>
>> On Tue, 1 Aug 2023 at 06:39, Andy Fan <zhihui.fan1213@gmail.com> wrote:
>> >
>> > Hi:
>> >
>> > Currently if we want to extract a numeric field in jsonb, we need to use
>> > the following expression:  cast (a->>'a' as numeric). It will turn a
>> numeric
>> > to text first and then turn the text to numeric again.
>>
>> Why wouldn't you use cast(a->'a' as numeric), or ((a->'a')::numeric)?
>>
>
> Thanks for this information! I didn't realize we have this function
> already at [1].
>
> https://www.postgresql.org/docs/15/functions-json.html
>

Hi:

I just found ((a->'a')::numeric) is not as effective as I expected.

First in the above expression we used jsonb_object_field which
returns a jsonb (see JsonbValueToJsonb), and then we convert jsonb
to jsonbValue in jsonb_numeric (see JsonbExtractScalar). This
looks like a wastage.

Secondly, because of the same reason above, we use PG_GETARG_JSONB_P(0),
which may detoast a value so we need to free it with PG_FREE_IF_COPY.
then this looks like another potential wastage.

Thirdly, I am not sure we need to do the NumericCopy automatically
in jsonb_numeric. an option in my mind is maybe we can leave this
to the caller?  At least in the normal case (a->'a')::numeric, we don't
need this copy IIUC.

/*
 * v.val.numeric points into jsonb body, so we need to make a copy to
 * return
 */
retValue = DatumGetNumericCopy(NumericGetDatum(v.val.numeric));

At last this method needs 1 extra FuncExpr than my method, this would
cost some expression execution effort. I'm not saying we need to avoid
expression execution generally, but extracting numeric fields from jsonb
looks a reasonable case. As a comparison, cast to other data types like
int2/int4 may be not needed since they are not binary compatible.


Here is the performance comparison (with -O3, my previous post is -O0).

select 1 from tb where (a->'a')::numeric = 2;  31ms.
select 1 from tb where (a@->'a') = 2;  15ms


-- 
Best Regards
Andy Fan