Hi Matthias: On Wed, Aug 2, 2023 at 7:33 AM Andy Fan <zhihui.fan1...@gmail.com> wrote:
> > > On Tue, Aug 1, 2023 at 7:03 PM Matthias van de Meent < > boekewurm+postg...@gmail.com> wrote: > >> On Tue, 1 Aug 2023 at 06:39, Andy Fan <zhihui.fan1...@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
